如何用SQL计算连续登录并统计SQL统计连续登录分布情况

在用户运营和数据分析场景中，统计用户的连续登录天数以及不同连续登录天数的用户分布，是评估用户活跃度、制定留存策略的核心指标之一。我们可以通过窗口函数和分组统计的组合，高效完成这个需求。

一、连续登录计算的核心思路

要计算连续登录天数，核心逻辑是先处理登录日期的重复问题，再通过日期排序和差值计算判断连续性：

• 第一步：对用户的所有登录记录去重，确保每个用户每天只有一条登录记录，避免同一天多次登录影响统计结果。
• 第二步：对每个用户的登录日期按时间升序排序，生成行号。
• 第三步：用登录日期减去行号对应的天数，得到连续登录的分组标识。如果登录是连续的，那么登录日期减去排序行号的结果会保持一致。
• 第四步：按用户和分组标识分组，统计每个组的天数，就是该用户的连续登录天数。

二、基础数据准备

我们先假设有一张用户登录记录表user_login_log，表结构如下：

插入测试数据的SQL如下：

-- 创建测试表
CREATE TABLE user_login_log (
    user_id INT,
    login_date DATE
);

-- 插入测试数据
INSERT INTO user_login_log (user_id, login_date) VALUES
(1, '2024-01-01'),
(1, '2024-01-02'),
(1, '2024-01-03'),
(1, '2024-01-05'),
(2, '2024-01-01'),
(2, '2024-01-03'),
(2, '2024-01-04'),
(2, '2024-01-05'),
(2, '2024-01-06'),
(3, '2024-01-02'),
(3, '2024-01-04'),
(3, '2024-01-05');

三、计算用户连续登录天数

按照之前的核心思路，我们可以写出如下SQL计算用户的每次连续登录天数：

WITH distinct_login AS (
    -- 第一步：去重，每个用户每天只保留一条登录记录
    SELECT DISTINCT user_id, login_date
    FROM user_login_log
),
login_with_row 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_id
    FROM login_with_row
)
-- 第四步：按用户和分组标识分组，统计连续天数
SELECT 
    user_id,
    MIN(login_date) AS start_login_date,
    MAX(login_date) AS end_login_date,
    COUNT(*) AS continuous_days
FROM login_group
GROUP BY user_id, group_id
ORDER BY user_id, start_login_date;

如果是PostgreSQL数据库，日期减法的语法需要调整，第三步的DATE_SUB(login_date, INTERVAL rn DAY)要替换为login_date - rn * INTERVAL '1 day'，其他逻辑保持一致。

上述SQL的执行结果如下：

四、统计连续登录分布情况

得到每个用户的连续登录天数之后，我们只需要对连续登录天数进行分组计数，就能得到分布情况。如果需要统计每个用户的最大连续登录天数对应的分布，可以先筛选出每个用户的最大连续登录天数，再做统计：

WITH distinct_login AS (
    SELECT DISTINCT user_id, login_date
    FROM user_login_log
),
login_with_row 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_id
    FROM login_with_row
),
user_continuous_days AS (
    SELECT 
        user_id,
        COUNT(*) AS continuous_days
    FROM login_group
    GROUP BY user_id, group_id
),
user_max_continuous AS (
    -- 取每个用户的最大连续登录天数
    SELECT 
        user_id,
        MAX(continuous_days) AS max_continuous_days
    FROM user_continuous_days
    GROUP BY user_id
)
-- 统计不同最大连续登录天数的用户数量
SELECT 
    max_continuous_days AS 连续登录天数,
    COUNT(*) AS 用户数量
FROM user_max_continuous
GROUP BY max_continuous_days
ORDER BY max_continuous_days;

上述SQL的执行结果如下：

五、注意事项

• 如果登录日期包含时间部分，需要先转换为日期类型再处理，避免时间差异导致连续判断错误。
• 如果数据库不支持窗口函数，可以通过自关联的方式实现排序和差值计算，不过性能会比窗口函数差一些。
• 统计分布时可以根据业务需求调整分组规则，比如将连续登录7天以上的用户归为同一组，更贴合运营分析的场景。

SQL连续登录登录分布窗口函数分组统计修改时间：2026-07-18 19:03:33