Hive SQL实现每小时登录时长占比指标计算

需求描述

用Hive SQL实现每小时登录时长指标，登陆时间和登出时间跨整小时也需要计算

登录时间	登出时间	登陆时长(s)
2022-04-04 08:00:10	2022-04-04 11:20:20	12010

如上面表格所示，原始数据需转换为下面表格情况，每个整小时也需要计算

登陆时间段	登陆时间	登出时间
08	2022-04-04 08:00:10	2022-04-04 09:00:00
09	2022-04-04 09:00:00	2022-04-04 10:00:00
10	2022-04-04 10:00:10	2022-04-04 11:00:00
11	2022-04-04 11:00:10	2022-04-04 11:20:20

数据准备

--建表
CREATE TABLE `test`(`id` string, `checkin` timestamp, `checkout` timestamp)

--插入数据
insert into table test select '1',cast('2022-04-04 08:00:10' as timestamp),cast('2022-04-04 11:20:20' as timestamp);
insert into table test select '1',cast('2022-04-04 02:00:03' as timestamp),cast('2022-04-04 03:40:30' as timestamp);
insert into table test select '1',cast('2022-04-04 04:17:11' as timestamp),cast('2022-04-04 07:44:46' as timestamp);
insert into table test select '1',cast('2022-04-04 07:50:11' as timestamp),cast('2022-04-04 07:59:59' as timestamp);

id	checkin	checkout
1	2022-04-04 08:00:10	2022-04-04 11:20:20
1	2022-04-04 04:17:11	2022-04-04 07:44:46
1	2022-04-04 07:50:11	2022-04-04 07:59:59
1	2022-04-04 02:00:03	2022-04-04 03:40:30

逻辑实现

select 
id,
to_date(checkin2) check_date,
hour(checkin2) check_hour,
checkin2 checkin,
if(unix_timestamp(checkout2)>unix_timestamp(checkout),checkout,checkout2) checkout
from (
select
    id,
    sign,
    checkout,
    case when sign<>0 then concat(date_format(checkin,'YYYY-MM-dd'),' ',
        if(length((hour(checkin)+sign))=1,concat('0',cast((hour(checkin)+sign) as string)),cast((hour(checkin)+sign) as string))
        ,':00:00') else checkin end checkin2,
    case when hour(checkin)<>hour(checkout) then concat(date_format(checkin,'YYYY-MM-dd'),' ',
        if(length((hour(checkin)+sign+1))=1,concat('0',cast((hour(checkin)+sign+1) as string)),cast((hour(checkin)+sign+1) as string))
        ,':00:00' )
    else checkout end checkout2
from(
        select * from lx_time
    ) tmp lateral view posexplode(
        split(space(hour(checkout) - hour(checkin) + (datediff(checkout,checkin)) * 24),'')
    ) t as sign,sign2
) f ;

id	check_date	check_hour	checkin	checkout
1	2022-04-04	4	2022-04-04 04:17:11	2022-04-04 05:00:00
1	2022-04-04	5	2022-04-04 05:00:00	2022-04-04 06:00:00
1	2022-04-04	6	2022-04-04 06:00:00	2022-04-04 07:00:00
1	2022-04-04	7	2022-04-04 07:00:00	2022-04-04 07:44:46
1	2022-04-04	7	2022-04-04 07:50:11	2022-04-04 07:59:59
1	2022-04-04	2	2022-04-04 02:00:03	2022-04-04 03:00:00
1	2022-04-04	3	2022-04-04 03:00:00	2022-04-04 03:40:30
1	2022-04-04	8	2022-04-04 08:10:20	2022-04-04 08:20:20

至此主体逻辑已经完成，接下来就是根据主键，登录日期，登陆时间段分组 sum(登出时间-登陆时间)即可