如何使用SQL窗口函数解连续登录_利用窗口函数计算连续登录

ROW_NUMBER()

user_logins

user_id

login_date

WITH DistinctUserLogins AS (
    -- 步骤1：首先，我们需要确保每个用户每天只有一条登录记录。
    -- 如果原始数据可能包含同一用户在同一天多次登录，去重是必要的。
    SELECT DISTINCT
        user_id,
        CAST(login_date AS DATE) AS login_day -- 确保我们只关注日期部分
    FROM
        user_logins
),
NumberedLogins AS (
    -- 步骤2：为每个用户的登录记录按日期顺序分配一个行号。
    -- 这是窗口函数发挥作用的关键一步。
    SELECT
        user_id,
        login_day,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) AS rn
    FROM
        DistinctUserLogins
),
ConsecutiveLoginGroups AS (
    -- 步骤3：创建“连续登录分组键”。
    -- 这里的巧妙之处在于：如果 login_day 是连续的（例如，2023-01-01, 2023-01-02, 2023-01-03），
    -- 那么 login_day 减去对应的 rn 值（1, 2, 3）后，结果将是一个恒定值。
    -- 例如：2023-01-01 - 1天 = 2022-12-31
    --       2023-01-02 - 2天 = 2022-12-31
    --       2023-01-03 - 3天 = 2022-12-31
    -- 但如果出现中断（例如，2023-01-05），那么 2023-01-05 - 4天 = 2023-01-01，
    -- 这就创建了一个新的分组键。
    SELECT
        user_id,
        login_day,
        rn,
        (login_day - rn * INTERVAL '1 DAY') AS login_group_key -- PostgreSQL语法
        -- MySQL: DATE_SUB(login_day, INTERVAL rn DAY)
        -- SQL Server: DATEADD(day, -rn, login_day)
    FROM
        NumberedLogins
)
-- 步骤4：根据用户ID和分组键聚合，计算每个连续登录区间的开始日期、结束日期和天数。
SELECT
    user_id,
    MIN(login_day) AS streak_start_date,
    MAX(login_day) AS streak_end_date,
    COUNT(*) AS consecutive_days_count
FROM
    ConsecutiveLoginGroups
GROUP BY
    user_id,
    login_group_key
HAVING
    COUNT(*) >= 1 -- 可以根据需要调整，例如只显示连续2天或以上的登录
ORDER BY
    user_id,
    streak_start_date;

-- 如果你只想知道每个用户的最长连续登录天数，可以在上述查询外层再加一层：
/*
SELECT
    user_id,
    MAX(consecutive_days_count) AS max_consecutive_days
FROM (
    -- 上述计算连续登录天数的完整查询
    -- ...
) AS user_streaks
GROUP BY
    user_id
ORDER BY
    max_consecutive_days DESC;
*/

login_day - rn * INTERVAL '1 DAY'

GROUP BY

GROUP BY user_id, login_day

COUNT(*)

COUNT()

SUM()

AVG()

GROUP BY

GROUP BY

ROW_NUMBER()

login_day - rn

LAG()

-- 考虑有1天宽限期的连续登录 (这会复杂很多，只是一个思路提示)
WITH UserLoginSequence AS (
    SELECT
        user_id,
        CAST(login_date AS DATE) AS login_day,
        LAG(CAST(login_date AS DATE), 1) OVER (PARTITION BY user_id ORDER BY CAST(login_date AS DATE)) AS prev_login_day
    FROM user_logins
),
LoginGroupsWithGrace AS (
    SELECT
        user_id,
        login_day,
        -- 如果当前登录日期与前一次登录日期相差超过宽限期，则视为新的一组
        CASE
            WHEN prev_login_day IS NULL OR (login_day - prev_login_day) <= INTERVAL '2 DAY' THEN 0 -- 允许1天间隔，即最多相差2天
            ELSE 1
        END AS is_new_group
    FROM UserLoginSequence
),
GroupMarkers AS (
    SELECT
        user_id,
        login_day,
        SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY login_day) AS group_id
    FROM LoginGroupsWithGrace
)
SELECT
    user_id,
    MIN(login_day) AS streak_start_date,
    MAX(login_day) AS streak_end_date,
    COUNT(*) AS consecutive_days_count
FROM GroupMarkers
GROUP BY
    user_id,
    group_id
ORDER BY
    user_id,
    streak_start_date;

login_date

CAST(login_date AS DATE)

DATE_TRUNC('day', login_date)