hive连续N天登录sql

hive连续N天登录sql

1、连续N天登录

select user_id
,ds
,ds_6
from(SELECt  user_id
        ,ds
        ,lead(ds,6,null) over(PARTITION BY user_id ORDER BY ds ASC) ds_6
FROM    (
            SELECt  DISTINCT ds
                    ,user_id
            FROM    dwd_user_log
            WHERe   ds >= '20210101'
            AND     ds <= '20211231'
        ) a 
) m 
where DATEDIFF(TO_DATE(ds_6,'yyyymmdd'),TO_DATE(ds,'yyyymmdd'),'dd') = 6
;

2、大于等于N天登录

SELECt  user_id
        ,MIN(DATE)
        ,MAX(DATE)
        ,count(*) num
FROM    (
            SELECt  user_id
                    ,date
                    ,dateadd(date,-rn,'dd') dis
            FROM    (
                        SELECt  user_id
                                ,date
                                ,row_number() over(PARTITION BY user_id ORDER BY DATE ASC) rn
                        FROM    (
                                    SELECt  DISTINCT to_date(ds,'yyyymmdd') date
                                            ,user_id
                                    FROM    dwd_user_log
                                    WHERe   ds >= '20210101'
                                    AND     ds <= '20211231'
                                    AND     user_id = '10032874'
                                ) a
                    ) c
        ) m
GROUP BY user_id
         ,dis
HAVINg  num >= 7