MySQL 單條 SQL 查詢用戶最大連續登錄天數：變量模擬窗口函數實戰

一、 業務需求

統計用戶在指定時間範圍內的最大連續登錄天數。例：1/3、1/4 登錄 → 連續 2 天；1/6~1/10 每日登錄 → 最大連續 5 天。用戶量大、跨度長（如 2 年），應用層循環不可行，需下推到數據庫單條 SQL 完成。

二、 核心思路：用戶變量模擬分組標記

MySQL 5.7/8.0 無原生 LAG/LEAD 窗口函數（8.0 有但舊版項目多），利用會話變量 @last_uid、@last_dt、@cont_day、@cont_ix 掃描有序數據，標記連續段分組編號。

三、 完整 SQL 拆解

SELECT uid, MAX(lianxu_days) AS max_consecutive_days,
       MIN(start_date) AS period_start, MAX(end_date) AS period_end
FROM (
    SELECT uid,
           MAX(days) AS lianxu_days,
           MIN(login_day) AS start_date,
           MAX(login_day) AS end_date
    FROM (
        SELECT
            uid,
            @cont_day := CASE
                WHEN @last_uid = uid AND DATEDIFF(created_ts, @last_dt) = 1 THEN @cont_day + 1
                WHEN @last_uid = uid AND DATEDIFF(created_ts, @last_dt) < 1 THEN @cont_day  -- 同天多次記錄不斷連續
                ELSE 1
            END AS days,
            @cont_ix := @cont_ix + IF(@cont_day = 1, 1, 0) AS cont_ix,  -- 新分組遞增
            @last_uid := uid,
            @last_dt := created_ts AS login_day
        FROM (
            SELECT uid, DATE(created_ts) AS created_ts
            FROM plan_stage
            WHERE uid != 0
            ORDER BY uid, created_ts
        ) t,
        (SELECT @last_uid := 0, @last_dt := NULL, @cont_ix := 0, @cont_day := 0) t1
    ) t2
    GROUP BY uid, cont_ix
    HAVING lianxu_days > 10  -- 可調整閾值
) tmp
GROUP BY uid
ORDER BY max_consecutive_days DESC;

四、 關鍵變量邏輯圖解

五、 執行計劃優化建議

• 索引：plan_stage(uid, created_ts) 覆蓋內層排序與分組
• 時間範圍裁剪：內層子查詢加 WHERE created_ts BETWEEN '2016-01-01' AND '2016-12-31' 減少掃描行
• 避免 SELECT *：僅取 uid, created_ts
• 大數據量分頁：若只需 Top N 用戶，外層加 LIMIT

六、 MySQL 8.0+ 窗口函數優雅寫法

WITH ranked AS (
    SELECT
        uid,
        DATE(created_ts) AS login_day,
        LAG(DATE(created_ts)) OVER (PARTITION BY uid ORDER BY DATE(created_ts)) AS prev_day
    FROM plan_stage
    WHERE uid != 0
),
grouped AS (
    SELECT
        uid,
        login_day,
        SUM(CASE WHEN DATEDIFF(login_day, prev_day) = 1 THEN 0 ELSE 1 END)
            OVER (PARTITION BY uid ORDER BY login_day) AS grp
    FROM ranked
)
SELECT uid, COUNT(*) AS consecutive_days
FROM grouped
GROUP BY uid, grp
ORDER BY consecutive_days DESC
LIMIT 10;

窗口函數可讀性強、無變量副作用，建議 8.0+ 專案優先採用。

七、 總結

利用會話變量單掃描完成連續段分組，是 MySQL 5.x 解決「最大連續登錄/簽到/活躍天數」類問題的經典模式。核心口訣：按用戶/時間排序 → 變量判斷連續 → 分組標記 → 聚合取最大。理解變量流轉邏輯，舉一反三解決連續庫存、連續交易等同構問題。