hsql 计算用户连续登录天/周/月数并折算到时间维度

• 问题描述

经常的业务需求会计算用户的连续登录天数，以及归结到每天去观察截止到当天连续登录n天的用户数分布

• 针对这样的问题做一个记录

比如业务需要这些时间当天的连续登录天数的分布

•  解决思路

1.先找到用户每天登录的日志（如果每天有多条，合并成一天）

2.按照用户的登录日志做分组排序

3.按照登录时间和排序rn做差

本案例中diff = 时间戳函数(login_date) - rn*86400；如果是周或者月需要替换对应的值

 

 4.在按照用户和diff按照时间做分组排序

5.分组完成后，其中的diff_rn就是对应天的连续登录天数分布

• 源码sql　

     ## 这是解决用户连续登录天数的分布，并折算到时间上
     SELECT user_id,
            login_date,
            rn,
            diff,
            row_number() over(partition BY user_id, diff ORDER BY login_date) AS diff_rn
     FROM
       (
     SELECT user_id,
            login_date,
            rn,
            (unix_timestamp(login_date)-rn*86400) AS diff
     FROM
     (
     SELECT user_id,
            login_date,
            row_number() over(partition BY user_id ORDER BY login_date) AS rn
     FROM
     (
     SELECT user_id,
            login_date
     FROM table
        ) tmp1
            ) tmp2
                   ) tmp3;