MySQL 實現類 Oracle 窗口函數分組：連續時間段合併實戰

MySQL 實現類 Oracle 窗口函數分組：連續時間段合併實戰

MySQL 8.0 前不支援 LAG()、LEAD()、OVER() 等窗口函數，面對「同用戶、時間連續（結束時間與下一條開始時間相差 1 秒）的記錄合併」需求，需用變量模擬窗口邏輯。本文詳細拆解思路與完整 SQL 遞推過程。

業務場景

表 time_log(id, s, start_t, end_t) 記錄用戶操作時段。需合併同一 id 下、時間連續（end_t + 1秒 = next start_t）的片段，輸出：id, 總時長(s), 片段起始時間, 片段結束時間。

樣本資料：

id=1: (20, 08:10:20~08:10:40), (9, 08:10:41~08:10:50), (60, 08:10:51~08:11:51)  -- 連續
       (2, 08:12:51~08:12:53)                                                    -- 間斷
預期輸出:
1, 89, 08:10:20, 08:11:51
1, 2,  08:12:51, 08:12:53

解決思路：分層遞推

1. 統一時間格式：將 datetime 轉為 YYYYMMDDHHMMSS 純數字字符串，便於比較與運算。
2. 生成行號：用 @rownum 賦予每行唯一序號。
3. 取上一行結束時間：用 @preEndTime 變量實現 LAG(end_t) 效果。
4. 構造分組標識：dstartnum = id + '-' + 格式化開始時間，dendnum = id + '-' + (格式化結束時間 + 1秒)。若 preendnum = dstartnum 則屬同一連續組，否則新組。
5. 累加分組標識：@rowid := @rowid + di（di=0 同組，di=rownum 新組），形成遞增分組 ID。
6. 組內聚合：按分組 ID 聚合 SUM(s)、MIN(start_t)、MAX(end_t)。

關鍵 SQL 片段解析

步驟 1：基礎轉換與 LAG 模擬

SELECT
    @rownum:=@rownum+1 AS rownum,
    @preEndTime AS preendnum,
    @preEndTime:=dendnum,
    t.*,
    CONCAT(id,'-',DATE_FORMAT(STR_TO_DATE(start_t,'%Y-%m-%d %H:%i:%s'),'%Y%m%d%H%i%s')) AS dstartnum,
    CONCAT(id,'-',DATE_FORMAT(STR_TO_DATE(end_t,'%Y-%m-%d %H:%i:%s'),'%Y%m%d%H%i%s')+1) AS dendnum
FROM time_log t, (SELECT @preEndTime:='', @rownum:=0) r

步驟 2：生成分組標識 di

SELECT *,
    CASE WHEN preendnum = dstartnum THEN 0 ELSE rownum END AS di
FROM (上述子查詢) t

步驟 3：累加分組 ID 並聚合 (最終 SQL 精簡版)

SELECT
    id,
    s_nums AS `總時長(秒)`,
    STR_TO_DATE(istarttimes, '%Y-%m-%d %H:%i:%s') AS `片段起始`,
    end_t AS `片段結束`
FROM (
    SELECT
        CASE WHEN @knum = dirow THEN 0 ELSE dirow END AS flag,
        @knum:=dirow,
        t.*
    FROM (
        SELECT
            @rowid:=@rowid+di AS dirow,
            @sums:=CASE WHEN di=0 THEN @sums+s+1 ELSE s END AS totals,
            @sums2:=CASE WHEN di=0 THEN @sums2+s ELSE s END AS s_nums,
            DATE_SUB(end_t, INTERVAL totals SECOND) AS istarttimes,
            t.*
        FROM (
            SELECT
                CASE WHEN preendnum=dstartnum THEN 0 ELSE rownum END AS di,
                t.*
            FROM (
                SELECT
                    @rownum:=@rownum+1 AS rownum,
                    @preEndTime AS preendnum,
                    @preEndTime:=dendnum,
                    t.*,
                    CONCAT(id,'-',DATE_FORMAT(STR_TO_DATE(start_t,'%Y-%m-%d %H:%i:%s'),'%Y%m%d%H%i%s')) AS dstartnum,
                    CONCAT(id,'-',DATE_FORMAT(STR_TO_DATE(end_t,'%Y-%m-%d %H:%i:%s'),'%Y%m%d%H%i%s')+1) AS dendnum
                FROM time_log t, (SELECT @preEndTime:='', @rownum:=0) r
            ) t
        ) t, (SELECT @rowid:=0) r
    ) t ORDER BY rownum DESC
) t, (SELECT @knum:=-1) r
WHERE flag <> 0
ORDER BY rownum;

MySQL 8.0+ 現代寫法 (強烈推薦)

升級至 MySQL 8.0 後，可直接使用標準窗口函數，代碼清晰易維護：

WITH formatted AS (
    SELECT
        id, s, start_t, end_t,
        STR_TO_DATE(start_t, '%Y-%m-%d %H:%i:%s') AS dt_start,
        STR_TO_DATE(end_t, '%Y-%m-%d %H:%i:%s') AS dt_end
    FROM time_log
),
lagged AS (
    SELECT
        *,
        LAG(dt_end) OVER (PARTITION BY id ORDER BY dt_start) AS prev_end
    FROM formatted
),
grouped AS (
    SELECT
        *,
        SUM(CASE WHEN TIMESTAMPDIFF(SECOND, prev_end, dt_start) = 1 THEN 0 ELSE 1 END)
            OVER (PARTITION BY id ORDER BY dt_start ROWS UNBOUNDED PRECEDING) AS grp
    FROM lagged
)
SELECT
    id,
    SUM(s) AS total_seconds,
    MIN(dt_start) AS segment_start,
    MAX(dt_end) AS segment_end
FROM grouped
GROUP BY id, grp
ORDER BY id, segment_start;

工程建議

• MySQL 5.7 及以下：若邏輯複雜、資料量大，**首選存儲過程** 或應用層處理，避免超長 SQL 維護困難。
• 時間邊界條件：確認「相差 1 秒」定義包含邊界，必要時用 TIMESTAMPDIFF(SECOND, ...) 精確判斷。
• 索引優化：INDEX idx_id_start (id, start_t) 支援分組排序與範圍查詢。