0%

Hive窗口函数实战

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

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

假设有一张登陆日志表:

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

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

计算距离上次登录天数

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

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

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

计算连续登录天数

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

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天。

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方案的初衷。

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