Oracle中如何求解连续登录问题

Oracle中如何求解连续登录问题

连续登录问题是指统计用户连续多天登录的起始日期、结束日期以及连续天数，是用户留存分析中的常见需求。在Oracle数据库中，我们可以通过窗口函数配合日期运算高效实现该需求。

核心实现思路

解决连续登录问题的核心逻辑是：如果用户的登录日期是连续的，那么登录日期减去对应的排序序号得到的结果会是一个固定值。具体步骤如下：

• 第一步：对用户的登录记录去重，避免同一天多次登录干扰计算结果
• 第二步：给每个用户的登录日期按升序排序，得到连续的序号
• 第三步：用登录日期减去排序序号，得到分组标识，连续登录的日期会得到相同的分组标识
• 第四步：按用户和分组标识分组，统计连续登录的起始日期、结束日期和天数

基础数据准备

首先我们创建测试用的用户登录表，并插入测试数据：

-- 创建用户登录表
CREATE TABLE user_login (
    user_id VARCHAR2(20),  -- 用户ID
    login_date DATE        -- 登录日期
);

-- 插入测试数据，包含用户A和用户B的登录记录
INSERT INTO user_login VALUES ('user_001', TO_DATE('2024-01-01', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_001', TO_DATE('2024-01-02', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_001', TO_DATE('2024-01-03', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_001', TO_DATE('2024-01-05', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_001', TO_DATE('2024-01-06', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_002', TO_DATE('2024-01-01', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_002', TO_DATE('2024-01-03', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_002', TO_DATE('2024-01-04', 'YYYY-MM-DD'));
INSERT INTO user_login VALUES ('user_002', TO_DATE('2024-01-05', 'YYYY-MM-DD'));
COMMIT;

完整SQL实现

下面是求解连续登录问题的完整SQL语句，每一步都添加了注释说明：

WITH distinct_login AS (
    -- 第一步：去重，同一用户同一天只保留一条记录
    SELECT DISTINCT user_id, login_date
    FROM user_login
),
login_with_rank 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,
        -- 登录日期减去序号，得到固定分组值
        login_date - rn AS group_id
    FROM login_with_rank
)
-- 第四步：按用户和分组统计连续登录信息
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_id
ORDER BY user_id, start_date;

结果说明

执行上述SQL后，会得到如下结果：

可以看到，用户user_001有两次连续登录记录，分别是3天和2天；用户user_002有两次连续登录记录，分别是1天和3天，符合我们插入的测试数据的实际情况。

注意事项

• 如果登录日期包含时间部分，需要先截断时间只保留日期部分，避免时间差异导致分组错误，可以使用TRUNC(login_date)处理
• 如果需要统计连续登录天数大于等于N天的记录，可以在最后的结果上添加HAVING COUNT(*) >= N条件过滤
• 该方法同样适用于其他支持窗口函数的数据库，只需要调整日期运算的语法即可

OracleSQL连续登录窗口函数修改时间：2026-06-27 13:12:15