DB2 迴圈查詢實戰：計算每日峰值同時在線用戶數

業務場景：從原始日誌推導每日併發峰值

假設有一張用戶在線日誌表 zhangmo_online，包含三個欄位：sid (用戶ID)、logintime (登入時間)、logouttime (登出時間)。需求是統計每一天中，同時在線用戶數最多的那一刻（峰值併發數）。

核心邏輯：將一天切分為固定間隔（如每 5 分鐘）的時間切片點，計算每個切片點「已登入且未登出」的用戶數，取當天最大值作為該日峰值。

資料庫物件準備

-- 1. 原始日誌表
CREATE TABLE zhangmo_online (
    sid INTEGER,
    logintime TIMESTAMP,
    logouttime TIMESTAMP
);

-- 2. 測試資料 (模擬一筆 20 分鐘的在線記錄)
INSERT INTO zhangmo_online
SELECT 51982112, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 20 MINUTES
FROM (VALUES(1)) a;

-- 3. 結果統計表 (儲存每個 5 分鐘切片的在線數)
CREATE TABLE zhangmo_onlinenum (
    onlinetime VARCHAR(20), -- 格式: YYYYMMDDHHMMSS
    onlinenum INTEGER
);

核心邏輯：SQL 預存程序實現迴圈採樣

使用 DB2 SQL PL 撰寫預存程序，透過 WHILE 迴圈遍歷時間範圍，於每個 5 分鐘節點執行統計查詢並寫入結果表。

CREATE PROCEDURE zhangmo_onlinenum()
LANGUAGE SQL
BEGIN
    DECLARE v_current_ts TIMESTAMP;
    DECLARE v_end_ts TIMESTAMP;

    -- 設定統計時間範圍：2008-02-01 00:00:00 至 2008-03-01 00:00:00
    SET v_current_ts = TIMESTAMP('2008-02-01 00:00:00');
    SET v_end_ts = TIMESTAMP('2008-03-01 00:00:00');

    WHILE v_current_ts < v_end_ts DO
        -- 核心統計查詢：登入時間 < 切片點 且 登出時間 > 切片點
        INSERT INTO zhangmo_onlinenum (onlinetime, onlinenum)
        SELECT 
            -- 將 TIMESTAMP 格式化為 YYYYMMDDHHMMSS 字串
            TRIM(CHAR(YEAR(v_current_ts))) ||
            RIGHT('00' || TRIM(CHAR(MONTH(v_current_ts))), 2) ||
            RIGHT('00' || TRIM(CHAR(DAY(v_current_ts))), 2) ||
            RIGHT('00' || TRIM(CHAR(HOUR(v_current_ts))), 2) ||
            RIGHT('00' || TRIM(CHAR(MINUTE(v_current_ts))), 2),
            COUNT(DISTINCT sid)
        FROM zhangmo_online
        WHERE logintime < v_current_ts
          AND logouttime > v_current_ts;

        -- 遞增 5 分鐘
        SET v_current_ts = v_current_ts + 5 MINUTES;
    END WHILE;

    COMMIT;
END;

關鍵技術點解析

• 時間格式化：DB2 無內建 TO_CHAR，需組合 YEAR/MONTH/DAY/HOUR/MINUTE 函數與 RIGHT('00'||...,2) 補零。
• 併發判斷邏輯：logintime < slice_point AND logouttime > slice_point 精確界定「在線」狀態。
• 去重計數：COUNT(DISTINCT sid) 確保同一用戶多次登入不重複計算。
• 效能考量：此為行級迴圈寫法，適合資料量中等、邏輯複雜場景。大數據量建議改用集合式思維（如產生時間維度表 JOIN 聚合）以獲取更高效能。

執行與結果分析

-- 1. 執行預存程序進行採樣統計
CALL zhangmo_onlinenum();

-- 2. 查詢每日峰值同時在線用戶數
SELECT LEFT(onlinetime, 10) AS stat_date, MAX(onlinenum) AS peak_concurrent_users
FROM zhangmo_onlinenum
GROUP BY LEFT(onlinetime, 10);

最終結果將呈現每一天的日期與對應的峰值併發用戶數，滿足業務報表需求。

進階優化方向

1. 集合式改寫：建立一張時間維度表 (含所有 5 分鐘切片點)，再與日誌表 JOIN 並 GROUP BY 切片點，一次性完成統計，避免迴圈逐筆 INSERT 的開銷。
2. 索引優化：在 zhangmo_online(logintime, logouttime) 上建立複合索引，加速篩選。
3. 分區表：若日誌表極大，可按月份分區，配合分區裁剪提升查詢速度。