问题引入
下面是某游戏公司记录的用户每日登录数据, 计算每个用户最大的连续登录天数，定义连续登录时可以间隔一天。举例：如果一个用户在 1,3,5,6,9 登录了游戏，则视为连续 6 天登录。
id dt1001 2021-12-121002 2021-12-121001 2021-12-131001 2021-12-141001 2021-12-161002 2021-12-161001 2021-12-191002 2021-12-171001 2021-12-20
这是个连续问题的升级版，当满足某种要求时我们也是算作连续的，所以不能使用传统的连续编号，然后做差值的解法了。核心思路解析如下：
这种解法是比较常见的，很多场景都可以这样使用。还有比如计算用户的会话数，当两次会话时间超过1分钟时就算做不同的会话，也可以这样做。
#这里假设样例数据存在tmp表里面
select
user_id
,max(diff) as max_login_days
from
(
select
user_id
,user_group
,datediff(date(max(dt)),date(min(dt)),'dd')+1 as diff --拿到每个用户下，连续时间里面最大日期与最小日期的差值加1就得到来连续天数
from
(
select
user_id
,dt
-- 如果当前日期与上一个日期的差值在2之内，那么就给0，否则给1
,sum(if(datediff(date(dt),date(last_dt),'dd')<=2,0,1)) over(partition by user_id order by dt) as user_group
from
(
select
user_id
,dt
,lag(dt,1,dt) over(partition by user_id order by dt) as last_dt --根据user_id分组，拿到当前行的上一个日期，没有上一个就给自己本身的值
from tmp
)t1
)t1
group by user_id ,user_group
)t1
group by user_id
;
思考： 为什么user_group这个字段可以作为分组条件？
条件求和：
if(datediff(date(dt),date(last_dt),'dd')<=2,0,1)这个条件判断当前行日期与上一行日期的差异。如果差异小于或等于2天，则返回0，否则返回1。累加生成
user_group：
窗口函数
SUM(...) OVER(...)的工作方式是在指定的窗口内累加值。在这种情况下，窗口是由PARTITION BY user_id ORDER BY dt定义的，这意味着：累加是在每个user_id分区内独立进行的，所以不同用户的累加是隔离的。在每个分区内，累加是按照日期dt的顺序进行的。- 对于连续登录的日期（差异小于等于2天），由于返回的是0，
sum函数累加值不变，表示这些日期属于同一个登录周期。
相似问题
问题：如下为某电商公司用户访问网站的数据，包括用户id和访问时间两个字段。现有如下规则：如果某个用户的连续的访问记录时间间隔小于60秒，则属于同一个会话，现在需要计算每个用户有多少个会话。比如A用户在第1秒，60秒，200秒，230秒有三次访问记录，则该用户有2个会话，其中第一个会话是第1秒和第60秒的记录，第二个会话是第200秒和230秒的记录。
user_id ts1001 169200000001001 169200000501002 169200000651002 169200000801001 169200001501002 16920000160
#sql可以简化，这里只是为了拆分每一步是如何做的而没有做合并。
with tmp as (
select 1001 as user_id,16920000000 as ts
union all
select 1001 as user_id,16920000050 as ts
union all
select 1002 as user_id,16920000065 as ts
union all
select 1002 as user_id,16920000080 as ts
union all
select 1001 as user_id,16920000150 as ts
union all
select 1002 as user_id,16920000160 as ts
)
select
user_id
,count(distinct user_group) as user_group_cnt
from
(
select
user_id
,ts
-- 开窗做累加
,sum(flag) over(partition by user_id order by ts) as user_group
from
(
select
user_id
,ts
-- 判断当前行的时间与上一行的差值
,if(ts-last_ts<60,0,1) as flag
from
(
select
user_id
,ts
-- 取当前行的上一个时间，没有上一行就给自身的时间
,lag(ts,1,ts) over(partition by user_id order by ts) as last_ts
from tmp
)t1
)t1
)t1
group by user_id
;
