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

作者:JueFan_C  来源:IT165收集  发布日期:2014-08-22 21:45:51

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

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

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

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

SELECT
    uid,
    isExist(collect_set(dt), k) flag
FROM
    table_name
GROUP BY
    uid
HAVING
    flag = 1;

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

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

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

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
            table_name
        GROUP BY uid, dt) a) a 
GROUP BY uid, num 

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

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

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

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

UID        DIFF    DT_ARRAY
1043736    3.0    {20140815    20140814    20140813    20140812}
1043736    0.0    {20140818}
1043736    1.0    {20140821    20140820}
1043844    0.0    {20140814}
1044090    1.0    {20140812    20140811}
1044090    2.0    {20140816    20140815    20140817}
1044090    0.0    {20140821}
1044264    0.0    {20140810}
1044264    3.0    {20140815    20140814    20140813    20140812}
1044264    5.0    {20140821    20140820    20140822    20140819    20140817    20140818}

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

    
Tag标签: 用户  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规