hive练习

有如下通话记录：
Zhangsan Wangwu 01:01:01
Zhangsan Zhaoliu 00:11:21
Zhangsan Yuqi 00:19:01
Zhangsan Jingba 00:21:01
Zhangsan Wuxi 01:31:17
Wangwu Zhaoliu 00:51:01
Wangwu Zhaoliu 01:11:19
Wangwu Yuqi 00:00:21
Wangwu Yuqi 00:23:01
Yuqi Zhaoliu 01:18:01
Yuqi Wuxi 00:18:00
Jingba Wangwu 00:02:04
Jingba Wangwu 00:02:54
Wangwu Yuqi 01:00:13
Wangwu Yuqi 00:01:01
Wangwu Zhangsan 00:01:01
函数：unix_timestamp(time_string,time_format)前面是将string类型时间字符串转化为指定的时间格式
from_unixtime(unix_time,format)将时间戳转化为指定格式的时间
建表

create table phonetime(
fornaem string,
toname strng,
time string
)
row format delimited
fields terminated by ' '
;

查询

1. 聚合数据将相同的数据聚合相加

select fromName,
toName,
from_unixtime(sum(unix_timestamp(time,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”)
from
phonetime
group by
fromName,toName;

2. 当成两张表关联，表1的第一个字段与表2的第二个字段关 并且 联表1的第二个字段与表2的第一个字段关联（求两人之间的通话）

select t1.,t2.
from
(select
fromName,toName,
from_unixtime(sum(unix_timestamp(time,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”) times
from phonetime
group by
fromName,toName) t1
left join
(select
fromName,toName,
from_unixtime(sum(unix_timestamp(time,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”) times
from phonetime
group by
fromName,toName) t2
on
t1.fromName = t2.toName and t1.toName = t2.fromName;

3. 累加同一条记录的t1.time和t2.time（通过左连接可将相互通话的时间相加没有去重）

   select
   t1.fromName,t1.toName,
   from_unixtime((unix_timestamp(t1.times,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”)+
   unix_timestamp(nvl(t2.times,“00:00:00”),“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+
   unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”)
   from
   (select fromName,toName,
   from_unixtime(sum(unix_timestamp(time,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”) times
   from phonetime group by fromName,toName) t1
   left join
   (select fromName,toName,
   from_unixtime(sum(unix_timestamp(time,“HH:mm:ss”)-unix_timestamp(“00:00:00”,“HH:mm:ss”))+unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”) times
   from phonetime group by fromName,toName) t2
   on t1.fromName = t2.toName and t1.toName = t2.fromName;