【sql】输出连续活跃n天/n次的3种方法

1.先看问题

现在有一张表，存储着时间（dt），用户id（user_id），点赞次数（zan_num)。
问题：输出在这段时间内有连续活跃5天记录的用户
答案：aa，bb
先看看数据大概长什么样子：

2.方法

方法1

解决步骤：

• 如果连续活跃5天，那么某1天：2022-02-01，和它后边的第4天的日期：2020-02-05的间隔天数就是4天。
• 如果没有连续5天，那么这个间隔天数肯定是>4天的。

用到一个lead() over()的函数
作用：输出当前列的下一行值
用法：lead(列名，1，null) over(partition by xx order by xx)
注意点：1表示填充下一个数值，null表示如果在当前分组内如果下一个没有了，就以null填充

用到一个datediff函数
作用：输出两个日期的差值
用法：datediff(dt1,dt2)
注意点：日期格式为yyyy-mm-dd，大日期在前结果为正，反之则为负

完整代码：

--第三步，按照=4的条件输出结果
select distinct user_id from
--第二步，计算日期差值
(select user_id,dt,dt2,datediff(dt2,dt)  as cha
from
--第一步：取到后边第4个日期
(select user_id,dt,
        lead(dt,4,null) over (partition by user_id order by dt) as dt2
from  tmp.tmp_0308_test) a
    ) b
where cha =4

过程示例：

方法2

解决步骤：

• 把每个人的日期分组排序编号；
• 会发现一个规律：如果是连续活跃，日期与编号的天数差值得到的新日期是相等的，比如：

2022-02-15 - 10天=2022-02-05
2022-02-16 - 10天=2022-02-05
2022-02-17 - 10天=2022-02-05
2022-02-18 - 10天=2022-02-05

2022-02-20 - 14天=2022-02-06
2022-02-21 - 15天=2022-02-06

2022-02-23 - 16天=2022-02-07
……

• 按照差值计算日期的天数，>=5天即可。

用到一个row_number() over()的函数
作用：分组排序
用法：row_number() over(partition by xx order by xx)

用到一个date_sub函数
作用：输出某一日期减去n天的日期
用法：date_sub(dt,n)
注意点：dt日期格式为yyyy-mm-dd，n为整数，正数及为加，负数即为减

用到一个to_date函数
作用：输出某一时间戳的yyyy-mm-dd日期格式
用法：date_sub(xx)

完整代码：

--第四步：按照>=5的条件输出结果
select distinct user_id from
--第三步：按照同一差值计算天数
(select user_id,dt2,count(distinct dt) as num
from
--第二步：计算差值
(select user_id,dt,rn,to_date(date_sub(dt,rn)) as dt2 
from
--第一步：分组排序给编号
(select user_id,dt,
        row_number() over (partition by user_id order by dt) as rn
from  tmp.tmp_0308_test) a
    ) b
group by 1,2) c
where num >=5

过程示例：

方法3

解决步骤

当问题变为不是日期，而是别的形式的不规律的带时分秒毫秒的时间，不是天数，而是次数，上述两种方法可能就不适用了。

所以第三种方法是完全使用排序的概念来解决的。

• 把每个人的时间分组排序
• 把所有时间去重分组排序
• 时间自己的序号，与每个人的时间序号相减，得到差值
• 差值相同的时间即为连续的时间

完整代码：

select distinct user_id from
--第四步：按照>=5的条件输出结果
(select user_id,cha,count(distinct dt) as num
from
--第三步：计算两个序号的差值
(   select user_id,a.dt,a.rn,b.rn2,b.rn2-a.rn as cha
    from
    --第一步：对每个人的时间分组排序
(select user_id,dt,
        row_number() over (partition by user_id order by dt) as rn
from  tmp.tmp_0308_test)
a
join
--第二步：对所有时间排序
(select dt,row_number() over (order by dt) as rn2
from
 (select distinct dt
from  tmp.tmp_0308_test
   )  a
    ) b on a.dt=b.dt
    ) c
   group by 1,2
    ) d
where num >=5

过程示例：

如果有问题，欢迎指出～
如果大家有更简单的办法，欢迎评论分享～