SQL解决连续问题

原创百分 2023-08-07

503

sql面试中经常会遇到连续问题，例如连续登陆，连续购买等等，今天总结了一些连续性或者间隔的问题。以登陆问题为例：构造一些数据，user_id：用户id，一共有1到5；date：登陆日期，从2020-01-01到2020-03-30，一共随机生成了300组数据并去重。在学习之前可以看看我之前写的窗口函数Hive窗口函数和日期处理Hive日期时间函数总结的文章。在实际工作中，需要考虑数据重复的问题，可以先distinct user_id，date或者row_number改成dense_rank，再去重。

1、连续登陆N天的用户数

这里面有两个技巧，row_number()进行分组排序，然后date_sub(date,rn)会使得连续日期产生同一个日期，最后再筛选过滤一下即可。后面的过滤条件是大于等于10天，可改成所需要的条件。

select user_id
from(
select user_id,b.dt,count(1) as co
from(
select user_id,date_sub(date,rn) as dt
from(
select user_id,date,row_number() over(partition by user_id order by date) as rn
from demo
)a
)b
group by user_id,b.dt
having co>=10
)c
order by user_id

结果：

2、每个用户最大连续登陆天数

和问题1比较类似，只是这里求最大值而已。

select user_id,max(co) as mco
from(
select user_id,b.dt,count(1) as co
from(
select user_id,date_sub(date,rn) as dt
from(
select user_id,date,row_number() over(partition by user_id order by date) as rn
from demo)a
)b
group by user_id,b.dt
)c
group by user_id
order by user_id

结果：

3、每个用户一周内连续登陆的最大天数

这里面加分组的时候就需要加入周，可以使用weekofyear，这个是标准的星期，如果需要自定义的周，可以使用datediff，标定某天为初始日期，例如某个周四为第一周第一天，然后处以7取整，既可获取自定义的周。这里的一周也常常为月，求每个用户一个月内连续登陆的最大天数，都是一样的，还有关注日期里面是否跨年，如果跨年，则需要把年份带上，我这里构造的数据都是2020年，所以不需要。

select user_id,max(co) as mco
from(
select user_id,week,b.dt,count(1) as co
from(
select user_id,week,date_sub(date,rn) as dt
from(
select user_id,date,weekofyear(date) asweek,row_number() over(partition by user_id,weekofyear(date) order by date) as rn
from demo)a
)b
group by user_id,week,b.dt
)c
group by user_id
order by user_id

结果：

4、每个用户登陆间隔小于N天的天

我这里使用的是lead函数，还有一种方法是两个row_number，再left join，条件为a.user_id=b.user_id and a.rn=b.rn-1，因为最后一个日期自然导致间隔错误，所以加了一天，下面是小于 3天为例。

select user_id,count(date)+1 as co
from(
select user_id,date,datediff(rn,date) as dt
from(
select user_id,date,lead(date,1) over(partition by user_id order by date) as rn
from demo)a
)b
where b.dt<3
group by user_id
order by user_id

结果：

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

SQL解决连续问题

评论