hive_面试题 【分组问题】

需求 : 某个用户连续的访问记录如果时间间隔小于 60 秒,则分为同一个组
      -- 同一个组内的数据,访问时间间隔小于60s

1. 数据准备

-- DDL
create table test3 (
`id` string comment '用户id',
`showtime` string comment '访问时间')
 comment '用户访问记录表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;

-- 插入数据
insert overwrite table test3
select '1001' as id,'17523641234' as showtime union all
select '1001' as id,'17523641256' as showtime union all
select '1002' as id,'17523641278' as showtime union all
select '1001' as id,'17523641334' as showtime union all
select '1002' as id,'17523641434' as showtime union all
select '1001' as id,'17523641534' as showtime union all
select '1001' as id,'17523641544' as showtime union all
select '1002' as id,'17523641634' as showtime union all
select '1001' as id,'17523641638' as showtime union all
select '1001' as id,'17523641654' as showtime ;

-- 数据说明
-- 电商公司用户访问时间数据,每访问一次则会产生一条记录

2. 思路 

with t1 as (
select
id
,showtime
,lag(showtime,1,showtime) over(partition by id order by showtime asc) as pre_showtime -- 当前行的前一行的showtime
,cast(showtime - lag(showtime,1,showtime) over(partition by id order by showtime asc) as int)  as time_diff
from test3)
select
id
,showtime
,time_diff
,if(time_diff>=60,1,0) as groupby_flag
,sum(if(time_diff>=60,1,0)) over(partition by id order by showtime asc) groupid
from t1
;

id      showtime        time_diff       groupby_flag    groupid
1001    17523641234     0               0               0
1001    17523641256     22              0               0
1001    17523641334     78              1               1
1001    17523641534     200             1               2
1001    17523641544     10              0               2
1001    17523641638     94              1               3
1001    17523641654     16              0               3
1002    17523641278     0               0               0
1002    17523641434     156             1               1
1002    17523641634     200             1               2