Hive中抽取连续多天登录用户

转：http://www.it165.net/database/html/201408/7854.html

昨天群上有人发个阿里的面试题，题目描述大概如下：

数据源：用户登录表，只有俩个字段，uid和dt

试用HQL抽取出连续登录了K天的用户uid

第一个想法就是直接用一个UDF解决，按uid分组，把dt收集起来然后在UDF里面判断是否满足条件

view source print ?

01. SELECT

02. uid,

03. isExist(collect_set(dt), k) flag

04. FROM

05. table_name

06. GROUP BY

07. uid

08. HAVING

09. flag = 1;

其中isExist的逻辑是判断collect_set中是否存在k个连续的值

这种方法简单明了，但是需要额外的写一个UDF，对于不懂JAVA的来说确实比较麻烦

今天群里有个神人给出了一种新的解决思路，十分完美的解决了，下面是具体代码

view source print ?

01. SELECT

02. uid, MAX(dt) - MIN(dt) diff, COLLECT_set (dt)

03. FROM

04. (SELECT

05. a.uid, a.dt, dt - rn num

06. FROM

07. (SELECT

08. uid, dt, row_number () over (PARTITION BY uid

09. ORDER BY dt) rn

10. FROM

11. table_name

12. GROUP BY uid, dt) a) a

13. GROUP BY uid, num

该思路首先利用窗口函数以uid分组然后按照dt排序给出每个dt在排序中的位置，然后用求出dt与位置的差（记为num)

最后按照uid和num做一个聚合，容易发现同一个num组内的dt是连续的值

然后直接计数(count(*))就可以得出结果了

上面的代码只是为了更加方便看到输出的结果正确性，输出结果如下：

view source print ?

01. UID        DIFF    DT_ARRAY

02. 1043736    3.0    ｛20140815    20140814    20140813    20140812｝

03. 1043736    0.0    ｛20140818｝

04. 1043736    1.0    ｛20140821    20140820｝

05. 1043844    0.0    ｛20140814｝

06. 1044090    1.0    ｛20140812    20140811｝

07. 1044090    2.0    ｛20140816    20140815    20140817｝

08. 1044090    0.0    ｛20140821｝

09. 1044264    0.0    ｛20140810｝

10. 1044264    3.0    ｛20140815    20140814    20140813    20140812｝

11. 1044264    5.0    ｛20140821    20140820    20140822    20140819    20140817    20140818｝

结果中uid = 1043736 的一共登录了7天，其中可以拆分成三个连续的登录模块，分别是连续登录1天、2天和4天