SQL如何统计连续登录天数

在用户运营、游戏运营等业务场景中，统计用户的连续登录天数是评估用户活跃度、制定激励策略的核心指标之一。实现这个统计的核心思路是先对用户的登录日期去重，再通过日期差值和分组逻辑识别连续的日期序列，最终统计每个连续序列的长度。

基础数据准备

首先我们需要准备一张用户登录记录表，表结构如下，包含用户ID和登录日期两个核心字段，同一个用户同一天可能有多条登录记录，需要先做去重处理。

-- 创建登录记录表
CREATE TABLE user_login_log (
    user_id INT,
    login_date DATE
);

-- 插入测试数据，包含3个用户的登录记录
INSERT INTO user_login_log (user_id, login_date) VALUES
(1, '2024-01-01'),
(1, '2024-01-02'),
(1, '2024-01-02'), -- 同一天重复登录
(1, '2024-01-03'),
(1, '2024-01-05'),
(1, '2024-01-06'),
(2, '2024-01-01'),
(2, '2024-01-03'),
(2, '2024-01-04'),
(3, '2024-01-02'),
(3, '2024-01-03'),
(3, '2024-01-04'),
(3, '2024-01-05'),
(3, '2024-01-06');

方案一：基于窗口函数实现

这是目前最主流的实现方式，核心逻辑是利用ROW_NUMBER()窗口函数为每个用户的登录日期生成排序序号，再用登录日期减去序号对应的天数，连续日期的差值会相同，通过这个差值分组就能识别连续登录的区间。

实现步骤

• 第一步：对每个用户的登录日期去重，避免同一天多次登录影响统计结果
• 第二步：对每个用户去重后的登录日期按升序生成行号
• 第三步：用登录日期减去行号对应的天数，得到连续分组的标识
• 第四步：按用户ID和分组标识分组，统计每个组的天数，即为连续登录天数

完整SQL代码

WITH distinct_login AS (
    -- 第一步：去重用户登录日期
    SELECT DISTINCT user_id, login_date
    FROM user_login_log
),
login_with_rn AS (
    -- 第二步：给每个用户的登录日期生成行号
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM distinct_login
),
login_group AS (
    -- 第三步：计算连续分组标识，连续日期的差值相同
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag
    FROM login_with_rn
)
-- 第四步：分组统计连续登录天数
SELECT 
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS continuous_days
FROM login_group
GROUP BY user_id, group_flag
ORDER BY user_id, start_date;

结果说明

上述代码执行后，会得到每个用户的所有连续登录区间及对应的天数，比如用户1会有两个连续区间：2024-01-01到2024-01-03共3天，2024-01-05到2024-01-06共2天。

方案二：基于自连接实现

如果数据库不支持窗口函数，可以使用自连接的方式实现，核心逻辑是找到每个登录日期的前一个登录日期，判断两个日期是否连续，再递归或者分组统计连续序列的长度。

实现步骤

• 第一步：同样先对登录日期去重
• 第二步：自连接找到每个登录日期的前一个登录日期
• 第三步：标记每个连续序列的起始日期
• 第四步：按起始日期分组统计连续天数

完整SQL代码

WITH distinct_login AS (
    -- 去重登录日期
    SELECT DISTINCT user_id, login_date
    FROM user_login_log
),
login_prev AS (
    -- 找到每个登录日期的前一个登录日期
    SELECT 
        a.user_id,
        a.login_date,
        MAX(b.login_date) AS prev_login_date
    FROM distinct_login a
    LEFT JOIN distinct_login b 
        ON a.user_id = b.user_id 
        AND b.login_date < a.login_date
    GROUP BY a.user_id, a.login_date
),
login_start AS (
    -- 标记连续序列的起始日期，前一个日期不存在或者间隔超过1天就是新序列开始
    SELECT 
        user_id,
        login_date,
        CASE 
            WHEN prev_login_date IS NULL 
                OR DATEDIFF(login_date, prev_login_date) > 1 
            THEN login_date 
            ELSE NULL 
        END AS start_flag
    FROM login_prev
),
group_flag AS (
    -- 给每个登录日期分配所属连续序列的起始日期
    SELECT 
        user_id,
        login_date,
        MAX(start_flag) OVER (PARTITION BY user_id ORDER BY login_date) AS group_start
    FROM login_start
)
-- 分组统计连续登录天数
SELECT 
    user_id,
    group_start AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS continuous_days
FROM group_flag
GROUP BY user_id, group_start
ORDER BY user_id, start_date;

两种方案对比

注意事项

• 统计前一定要对登录日期去重，否则同一天多次登录会被重复计算，导致连续天数统计错误
• 日期差值计算时要注意数据库的函数差异，比如MySQL用DATE_SUB，PostgreSQL用login_date - rn * INTERVAL '1 day'，SQL Server用DATEADD(DAY, -rn, login_date)
• 如果需要统计用户的最大连续登录天数，可以在上述结果的基础上再按用户ID分组取MAX(continuous_days)即可

连续登录天数的统计核心是识别连续的日期序列，窗口函数方案是更优的选择，在支持窗口函数的数据库场景下优先使用。

SQL连续登录天数窗口函数分组统计日期处理修改时间：2026-07-10 09:54:32