现要求统计各用户最长的连续登录天数,间断一天也算作连续,例如:一个用户在1,3,5,6登录,则视为连续6天登录。
分类:SQL
测试数据:
CREATE TABLE tempdb.dbo.login_logs (
user_id int NOT NULL,
login_date date NOT NULL
);
user_id int NOT NULL,
login_date date NOT NULL
);
-- 为用户A生成随机登录记录
INSERT INTO login_logs (user_id, login_date)
SELECT
1,
DATEADD(DAY, FLOOR(RAND(CHECKSUM(NEWID())) * 31), '2024-01-01') -- 用户A在1月份的任意一天登录
FROM
(SELECT TOP 31 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) numbers -- 生成31个数字作为登录日期的偏移量
-- 为用户B生成随机登录记录
INSERT INTO login_logs (user_id, login_date)
SELECT
2,
DATEADD(DAY, FLOOR(RAND(CHECKSUM(NEWID())) * 31), '2024-01-01') -- 用户B在1月份的任意一天登录
FROM
(SELECT TOP 31 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) numbers; -- 生成31个数字作为登录日期的偏移量
生成中间数据:
SELECT
*,
SUM(value) OVER (PARTITION BY user_id ORDER BY login_date) AS cumulative_value
FROM
(
select *,
case when user_id = previous_user_id and DATEDIFF(day,previous_login_date,login_date)<=1 then 0 else 1 end as value
from (
SELECT
user_id,
login_date,
LAG(user_id) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_user_id,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login_date
FROM
login_logs
) aa
) aaa
生成的中间数据
对这个数据再做聚合就可以得到结果了。