SQL Server中统计存在连续7天登录记录的用户数

原始数据 #

需求说明 #

统计存在连续7天登录记录的用户数量：用户A因1月4日未登录，不符合连续7天条件；用户B连续7天登录，需计入统计。

解决方案 #

核心思路是通过日期减去行号的方式将连续登录日期归为同一分组，再统计每组的登录天数，判断是否满足连续7天要求。以下是完整SQL代码：

WITH LoginGroups AS (
    SELECT 
        UserId,
        -- 若CreatedOn是字符串类型，需转换为DATE，104对应dd.MM.yyyy格式
        CONVERT(DATE, CreatedOn, 104) AS LoginDate,
        -- 生成分组标识：连续日期的分组值相同，中断则值变化
        DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY CONVERT(DATE, CreatedOn, 104)), CONVERT(DATE, CreatedOn, 104)) AS GroupId
    FROM 
        YourLoginTable -- 替换为你的实际表名
    WHERE CreatedOn IS NOT NULL
),
GroupLoginStats AS (
    SELECT 
        UserId,
        COUNT(*) AS ContinuousLoginDays
    FROM 
        LoginGroups
    GROUP BY 
        UserId, GroupId
)
-- 统计有连续7天及以上登录的用户数
SELECT COUNT(DISTINCT UserId) AS QualifiedUserCount
FROM 
    GroupLoginStats
WHERE 
    ContinuousLoginDays >= 7;

代码解释 #

1. LoginGroups CTE：

   • 先把字符串格式的CreatedOn转换为DATE类型（如果字段本身是DATE类型，直接用CreatedOn即可）。
   • 对每个用户的登录日期按时间顺序生成行号，用DATEADD把日期减去行号得到分组标识GroupId——连续日期计算后会得到相同的GroupId，日期中断时GroupId改变，以此区分不同的连续登录段。

2. GroupLoginStats CTE：

   • 按用户和分组标识分组，统计每个连续登录段的天数。

3. 最终查询：

   • 筛选出存在连续登录天数≥7的用户，去重后统计总数，就是符合要求的用户数量。

内容的提问来源于stack exchange，提问作者C93H