HiveSql经典面试题解析-统计两个人的通话时长

数据：数据有三列：呼叫人、接听人、通话时长，由一个空格分割。

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

需求：统计两个人通话的总时长。
说明：

• ①两个人可能多次通话
• ②呼叫人和接听人可互换，即第一次通话呼叫人->接听人，第二次通过接听人变成了呼叫人

思路其实很简单，将相同的呼叫人和接听人的通话时间相加，一个group by就解决了。但写出代码然后要得到正确的结果就没那么简单了。

看看下面的SQL和结果：

select fromName,toName,from_unixtime(sum(unix_timestamp(callDuration,"HH:mm:ss")),"HH:mm:ss") callDuration from call_record group by fromName,toName

红框对应的原始数据如下：

总时间应该为：2:02:20，Sql统计出来的时间却是18:02:20。另外，其他的结果又是正确的。

是不是很莫名其妙，如果单纯这样看，是很难定位问题的，考虑到from_unixtime是日期转换函数，并非仅仅是小时、分、秒的加减，所以将上面结果的年月日也显示出来。

sql做如下修改：

 select fromName,toName,from_unixtime(sum(unix_timestamp(callDuration,"HH:mm:ss")),"yyyy-MM-dd HH:mm:ss") from call_record group by fromName,toName;

将结果与原始数据对比：

发现如下问题：凡是group by后有多条数据，经过聚合的，日期都是1970-01-01以前的；单条数据的日期都是1970-01-01。

要讲清楚这个问题，首先要搞清楚from_unixtime和unix_timestamp这两个函数的特点。

首先，看看from_unixtime的特性。

从图上可以看出，尽管时间戳是0，但转换为时间后变成了8点，from_unixtime将时间戳0转换为0时区的时间，而后再将0时区的时间转换为系统时区的时间，于是时间戳代表的0点(0时区)被转换为8点(东八区)。

其次，看看unix_timestamp的特性。

unix_timestamp的逻辑：

• ①将当前时区时间转换为0时区时间
• ②将0时区时间转换为时间戳

结合上面两个function的特性，以下面这条数据为例，解释下为什么会出问题。

这条数据对应的原始数据由四条：

根据上面的sql语句，会分别对这四条数据的时间调用unix_timestamp。而我们已经知道，这个函数会将时间转换为0时区，即将当前时间减去8小时。由于是4条数据相加，每条数据都会被减去8小时，相当于一共被减去了32小时。

到这儿，你会发现还是对不上啊，如果是减去32个小时，结果不应该是1969-12-31 00:06:05吗？

看看SQL你会发现1969-12-31 00:06:05是from_unixtime的结果，我们提到过，from_unixtime会先将时间戳转换为0时区的时间，然后将时间转换为本地时区时间，对东八区来说，就是要加上8个小时。

unix_timestamp累计减去32小时，from_unixtime加上8小时，就变成减去24小时，恰好小时数不变，天数变成前一天。

按照上面的推理，下面这条数据的出现就不难理解了：

这条数据对应的原始数据只有2条。

相当于unix_timestamp减去16小时，from_unixtime加上8小时，效果减去8小时。

最后的结果就是1970-01-01 02-02-20 减去8小时，就变成了 1969-12-31 18:02:20，这里的18并非是总的通话时间，而是时间中的小时，表征18点而不是18个小时。

这也是为什么我们直接用from_unixtime和unix_timestamp得不出正确结果的原因，其实也不该这样用。

当然，非要这样用，其实也可以，但要做一些处理。

一，使用unix_timestamp和from_unixtime的解法

1，对unix_timestamp的处理

from_unixtime会根据当前时区进行时间转换，我们没办法告知它不转，但可以做一个逆操作去抵消转换。以东八区为例，from_unixtime减8小时后，我们再把8小时加回来，为了避免硬编码，使用unix_timestamp(“00:00:00”)得到减去8小时对应的秒数，要注意的是，得到的结果是负数，所以语义上虽然我们要加8个小时，但符号要用减号：

unix_timestamp(callDuration,"HH:mm:ss")-unix_timestamp("00:00:00",'HH:mm:ss')

2，对from_unixtime的处理

因为from_unixtime会对最后计算的结果加上8小时，但我们不需要这个处理，因为我们计算的是时间总长，而不是具体几点钟,所以，要在最终的结果上减去8小时，减去8小时的思路同上，注意语义上的减要用加号实现。

3，SQL

hive> select fromName,toName,from_unixtime(sum(unix_timestamp(callDuration,"HH:mm:ss")-unix_timestamp("00:00:00",'HH:mm:ss'))+unix_timestamp("00:00:00",'HH:mm:ss'),"HH:mm:ss") callDuration from call_record group by fromName,toName;

SQL写到这里，解决了一个难题，另一个难题是需求上的，要求统计两个人的通话时间，这里包含了两种情况：A打电话给B和B打电话给A，这两种场景下的通话时间应该相同，但在group by的时候无法将这两种情况识别出来。

所以，应该提前处理，将A打电话给B和B打电话给A这两种情况统一为A打电话给B或者B打电话给A。

实现手法也简单：

select 
case when fromName > toName then fromName  else toName end fromName ,
case when fromName <= toName then fromName  else toName end toName ,
callDuratin

from call_record;

4，最终版SQL

select fromName,toName,from_unixtime(sum(unix_timestamp(callDuration,"HH:mm:ss")-unix_timestamp("00:00:00",'HH:mm:ss'))+unix_timestamp("00:00:00",'HH:mm:ss'),"HH:mm:ss") callDuration 
from  (
  select 
	case when fromName > toName then fromName  else toName end fromName ,
	case when fromName <= toName then fromName  else toName end toName ,
	callDuration
  from call_record
)
group by fromName,toName;

二，使用时间函数hour + minute + second

其实最标准的方法是表中的时长转换为秒数，然后相加得出总时长，再将秒数转换为表中的格式。

将秒数转换为时间格式，有两种方法，一是调用from_unixtime方法，不过考虑到时差，需要使用unix_timestamp去调整。

select fromName,toName,
from_unixtime(sum(hour(callDuration)*3600 + minute(callDuration)*60 + second(callDuration))+ unix_timestamp(“00:00:00”,“HH:mm:ss”),“HH:mm:ss”)
from call_record
group by fromName,toName;