现有各用户的登录记录表(login_events)如下,表中每行数据表达的信息是一个用户何时登录了平台。
现要求统计各用户最长的连续登录天数,间断一天也算作连续,例如:一个用户在1,3,5,6登录,则视为连续6天登录。期望结果如下:
查询sql
WITH tmp as( --没啥好说的,做一个数据格式化,同时去重
SELECT
user_id,
date_format(login_datetime,"yyyy-MM-dd") login_date
from login_events
group by user_id,date_format(login_datetime,"yyyy-MM-dd")
)
SELECT
user_id,
--4.这就是每一条子sql都保留login_date这个字段的意义,同一个会话内的最大日期减去最小日期得到的就是连续登录的天数
datediff(max(login_date),min(login_date)) + 1 max_day_count
from(
SELECT
user_id,
login_date,
--3.这里拼上一个user_id的目的是给每一个会话建立一个唯一标识,方便后面按照会话的唯一标识进行group by
concat(user_id,"-",sum(flag) over(PARTITION by user_id order by login_date )) flag
from(
SELECT
user_id,
login_date,
last_login,
--2.进行判断,今天登录的日期减去上一次登录的日期,如果大于2的话,就是一个会话的新起点,给这个新起点标记一个1,后面开窗聚合,范围为上无边界到当前行,同一个会话sum的值就会相同
IF(datediff(login_date,last_login)>2,1,0) flag
from(
SELECT
user_id,
login_date,
--1.注解从这里看起,取出用户上一次登录的日期
lag(login_date,1,'1970-01-01') over(PARTITION by user_id order by login_date) last_login
from tmp
)t1
)t2
)t3 group by user_id,flag