有如下通话记录:
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 ' '
;
查询
- 聚合数据将相同的数据聚合相加
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;
- 当成两张表关联,表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;
-
累加同一条记录的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;