hive高频面试题1.6统计两个人的通话总时长

有如下通话记录：
呼叫		被叫	  时长
goudan haoge 01:01:01
goudan mazi 00:11:21
goudan laowang 00:19:01
goudan Jingba 00:21:01
goudan weige 01:31:17
haoge mazi 00:51:01
haoge mazi 01:11:19
haoge laowang 00:00:21
haoge laowang 00:23:01
laowang mazi 01:18:01
laowang weige 00:18:00
Jingba haoge 00:01:01
Jingba haoge 00:00:06
Jingba haoge 00:02:04
Jingba haoge 00:02:54
haoge laowang 01:00:13
haoge laowang 00:01:01
haoge goudan 00:01:01

使用hql统计两个人的通话总时长（用户之间互相通话的时长，时长单位使用分钟）?

1.需要把时间进行切割，切割完后的形式是分钟

int(split(times,':')[0] * 60+split(times,':')[1]+if(split(times,':')[2]=0,0,1))

2.需要让 A B和B A都转换成B A的形式进行统计

case when hj>=bj then hj else bj end hj
case when hj>=bj then bj else hj end bj

3.求解

select 
hj,
bj,
sum(time_1)
from
(
select
case when hj>=bj then hj else bj end hj,
case when hj>=bj then bj else hj end bj,
int(split(times,':')[0] * 60+split(times,':')[1]+if(split(times,':')[2]=0,0,1)) time_1
from callphone) call
group by hj,bj