第 2 题 分组问题

1、题目要求

        如下为电商公司用户访问时间数据

1001;17523641256
1002;17523641278
1001;17523641334
1002;17523641434
1001;17523641534
1001;17523641544
1002;17523641634
1001;17523641638
1001;17523641654

        某个用户连续的访问记录如果时间间隔小于 60 秒，则分为同一个组，结果为：

id      ts(秒)   group
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3

2、建表和加载数据

create table if not exists test2(
id int ,
ts string
)row format delimited fields terminated by ";";


load data local inpath '/opt/test/t2.txt' overwrite into table test2;

3、分析

        1）首先肯定要排序后才能比较前后两个时间间隔，前后两个时间不在同一行怎么比较？

如果我们能达到如下效果那就和知道时间间隔了（组内排序后第三例数据是上一行的时间，组内第一个数据的第三列是本身）

1001 17523641256 17523641256
1001 17523641334 17523641256
1001 17523641534 17523641334
1001 17523641544 17523641534
1001 17523641638 17523641544
1001 17523641654 17523641638

1002 17523641278 17523641278
1002 17523641434 17523641278
1002 17523641634 17523641434

        2）我们开始使用lag窗口函数实现

lead(col,n,default):当前行往下n行，如果没有就取default，将找到的那个数据放到本行

lag(col,n,default): 当前行往上n行，如果没有就取default，将找到的那个数据放到本行

select
id,ts,
lag(ts,1,'0') over(partition by id order by ts) lag_ds
from test2;

         结果：

id           ts        lag_ts
1001	17523641256	0
1001	17523641334	17523641256
1001	17523641534	17523641334
1001	17523641544	17523641534
1001	17523641638	17523641544
1001	17523641654	17523641638
1002	17523641278	0
1002	17523641434	17523641278
1002	17523641634	17523641434

        3）将当前行的 ts 建减上一行 lag_ts

select
id,ts,(ts-lagts) as tsdiff
from (
    select
        id,ts,
        lag(ts,1,'0') over(partition by id order by ts) lagts
    from test2
)t1;

        结果：

id	         ts	      tsdiff
1001	17523641256	17523641256
1001	17523641334	78
1001	17523641534	200
1001	17523641544	10
1001	17523641638	94
1001	17523641654	16
1002	17523641278	17523641278
1002	17523641434	156
1002	17523641634	200

        4）判断相减后每个用户内从第一行到当前行有多少个大于60，有多少个就是第几组

select
id,ts,
sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupid
from (
    select
        id,ts,(ts-lagts) as tsdiff
    from (
        select
            id,ts,
            lag(ts,1,'0') over(partition by id order by ts) lagts
        from test2
    )t1
)t2;

        结果：

id	         ts	    groupid
1001	17523641256	1
1001	17523641334	2
1001	17523641534	3
1001	17523641544	3
1001	17523641638	4
1001	17523641654	4
1002	17523641278	1
1002	17523641434	2
1002	17523641634	3

这道题目的重点就是窗口函数的应用，如果不熟悉窗口函数的使用可以参考hive中的开窗函数