DB2 大表環境下的循環查詢實作：以 5 分鐘間隔統計同時在線用戶數

業務需求

有一張大表 zhangmo_online，欄位：sid（飛信號）、logintime、logouttime。需求：依天統計每日最大同時在線用戶數。做法：將一天切分為 5 分鐘間隔的時間點（共 288 個），統計各時間點「已上線且未下線」的用戶數，取每日最大值作為該日同時在線峰值。

解決方案架構

1. 建立取樣表： 儲存每個時間點的在線人數
2. 撰寫預存程序： 迴圈產生時間點、查詢計數、寫入取樣表
3. 彙總分析： 按日期分組取最大值

完整 SQL 實作

1. 原始資料表與測試資料

CREATE TABLE zhangmo_online (
    sid INTEGER,
    logintime TIMESTAMP,
    logouttime TIMESTAMP
);

INSERT INTO zhangmo_online
SELECT 51982112, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 20 MINUTES
FROM (VALUES (1)) a;

2. 取樣結果表

CREATE TABLE zhangmo_onlinenum (
    onlinetime VARCHAR(20),
    onlinenum INTEGER
);

3. 迴圈計算預存程序

CREATE PROCEDURE zhangmo_onlinenum()
LANGUAGE SQL
BEGIN
    DECLARE i TIMESTAMP;
    SET i = TIMESTAMP('2008-02-01 00:00:00');
    
    WHILE i < TIMESTAMP('2008-03-01 00:00:00') DO
        INSERT INTO zhangmo_onlinenum (onlinetime, onlinenum)
        SELECT 
            TRIM(CHAR(YEAR(i))) ||
            RIGHT('00' || TRIM(CHAR(MONTH(i))), 2) ||
            RIGHT('00' || TRIM(CHAR(DAY(i))), 2) ||
            RIGHT('00' || TRIM(CHAR(HOUR(i))), 2) ||
            RIGHT('00' || TRIM(CHAR(MINUTE(i))), 2),
            COUNT(DISTINCT sid)
        FROM zhangmo_online
        WHERE logintime < i AND logouttime > i;
        
        SET i = i + 5 MINUTES;
    END WHILE;
    
    COMMIT;
END

4. 執行與彙總

CALL zhangmo_onlinenum();

-- 取每日最大同時在線數
SELECT LEFT(onlinetime, 10) AS the_date, MAX(onlinenum) AS peak_online
FROM zhangmo_onlinenum
GROUP BY LEFT(onlinetime, 10);

效能優化建議

• 索引支援： 在 (logintime, logouttime) 建立複合索引，加速範圍過濾
• 分區表： 若資料跨月/年，考慮按月分區
• 向量化改寫： 可嘗試用 GENERATE_SERIES (DB2 11.5+) 或遞迴 CTE 產生時間序列，避免程序迴圈的逐筆 INSERT
• 批次提交： 迴圈中每 N 次提交一次，避免日誌暴漲

適用場景

此模式適用於「事件區間重疊計數」類問題：通話並發、設備在線、會話重疊等。核心邏輯：開始時間 < 觀測點 AND 結束時間 > 觀測點。