Hive窗口函数实战

Posted on Edited on

本文将介绍使用Hive强大的窗口函数，解决实际问题的方法，仅供参考。

连续登陆/活跃/访问问题

假设有一张登陆日志表：

1
2
3
4
CREATE TABLE user_login (
    uid int comment '用户ID',
    dt int comment '日期yyyymmdd'
) STORED AS PARQUET

PS：如果用户在某天多次登陆，也只会有一条记录。

计算距离上次登录天数

先来个最简单的案例, 要算出用户每次登陆，距离上次有多少天。

1
2
SELECT *,dt - lag(dt,1) OVER (PARTITION BY uid ORDER BY dt)
from user_login

lag 函数是取上N行的指定列的数据，N默认是1，所以也可以写成lag(dt)。

计算连续登录天数

计算用户连续登陆了多少天，一段时间内可能会有多次连续登陆。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    uid,
    max(dt)-min(dt) diff,
    collect_set(dt)
from(
    SELECT
        a.uid,
        a.dt,
        dt - rn num
    from (
        SELECT uid, dt, row_number() over ( PARTITION BY uid ORDER BY dt ) rn
        FROM user_login
    ) a
) b
GROUP BY uid,num

这里需要理解的关键点是’dt - rn’得到num这个计算，也就是将日期减去行号，
这是个取巧的算法，看起来有点奇怪。

因为row_number()是按dt正序的，所以连续的dt减去行号，会得到同一个值，而非连续的则不会。
举个例子：

uid dt row_number dt - row_number (num)
u1 20190203 1 20190202
u1 20190204 2 20190202
u1 20190206 3 20190203

这里其实就是子查询b的结果，理解了这个，最外层的语句就很好理解了。

标记用户连续间隔不超过3天的登录

标记用户连续的活跃记录，只要不超过3天间隔，就算连续活跃。

假设有记录如下：

uid dt
u1 20190203
u1 20190204
u1 20190206
u1 20190210
u1 20190211

这里需求就是要把前3行和后2行区分出来，因为20190206和20190210之间隔了4天。

1
2
3
4
5
SELECT *,max(flag) OVER (PARTITION BY uid ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) session_id
from (
    select uid,dt,if(dt-lag(dt)>3,dt,0) OVER (PARTITION BY uid ORDER BY dt) flag
    from user_login
) t

这里子查询t就是先标记处间隔大于3的记录。

uid dt flag
u1 20190203 0
u1 20190204 0
u1 20190206 0
u1 20190210 20190210
u1 20190211 0

最外层的查询，通过窗口函数，从当前行往上找出最大的flag，因为按dt排序，得到的其实就是最近的flag，
将该flag设置为自己的session_id，表示所属同一批连续活跃的记录。
得到：

uid dt flag session_id
u1 20190203 0 0
u1 20190204 0 0
u1 20190206 0 0
u1 20190210 20190210 20190210
u1 20190211 0 20190210

现在前3行和后2行，就被标记为不同的组了。

如果我们有的是精确到分、秒的日志，这个解决方案可以用来给用户的访问记录打上会话标记，然后拿来计算单次访问时长，访问深度等等，只是这里的dt改成时间戳而已。

计算会话标记其实是数仓同事提的需求，也是我想出这个sql方案的初衷。

希望能帮到需要的人。^-^