您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 按時間間隔分組統計實戰：10 分鐘粒度聚合方案

編輯：MySQL 資料庫相關知識

業務場景

用戶登錄/操作日誌按 10 分鐘粒度繪製 24 小時趨勢圖。需將 time_str (DATETIME) 按固定窗口聚合計數。

方案對比：三種 GROUP BY 寫法

方法一：字符串截取法（固定 10 分鐘/小時）

SELECT 
    CONCAT(LEFT(DATE_FORMAT(time_str, '%y-%m-%d %H:%i'), 15), '0') AS time_flag,
    COUNT(*) AS cnt
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY time_flag
ORDER BY time_flag;

優點： 可讀性強，結果直接可用於圖表 X 軸
缺點： 只能固定 10 分鐘/小時，難以動態調整間隔

方法二：Unix 時間戳除法（動態間隔）

SELECT 
    ROUND(UNIX_TIMESTAMP(time_str) / (10 * 60)) AS time_key,
    COUNT(*)
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY time_key
ORDER BY time_key;

優點： 間隔靈活，修改除數即可（5分鐘/30分鐘/1小時）
缺點： 結果需反向轉換為可讀時間

方法三：日期時間函數組合法（標準 SQL）

-- FLOOR 向下取整（等價方法一）
SELECT 
    CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', FLOOR(MINUTE(time_str)/10)*10) AS time_slot,
    COUNT(*)
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY DATE(time_str), HOUR(time_str), FLOOR(MINUTE(time_str)/10)*10
ORDER BY time_slot;

-- ROUND 四捨五入（邊界不同）
SELECT 
    CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', ROUND(MINUTE(time_str)/10,0)*10) AS time_slot,
    COUNT(*)
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY DATE(time_str), HOUR(time_str), ROUND(MINUTE(time_str)/10,0)*10
ORDER BY time_slot;

-- CEIL 向上取整
SELECT 
    CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', CEIL(MINUTE(time_str)/10)*10) AS time_slot,
    COUNT(*)
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY DATE(time_str), HOUR(time_str), CEIL(MINUTE(time_str)/10)*10
ORDER BY time_slot;

關鍵差異： FLOOR 將 00:00-00:09 歸為 00:00，ROUND 00:05 歸為 00:10，CEIL 00:01 歸為 00:10。按業務定義選擇。

存儲過程方案（歷史對比）

早期無動態 SQL 時，寫死 144 個 10 分鐘窗口：

DELIMITER //
CREATE PROCEDURE `usp_cmd_info`(IN p_date VARCHAR(12))
BEGIN
    -- 動態生成 144 個 SELECT ... INTO @count_0 ... @count_143
    -- 最後 SELECT @count_0, @count_1, ...
END //
DELIMITER ;

缺點： 代碼極長、維護困難、無法動態調整間隔、性能不如單條 GROUP BY

Python 自動生成存儲過程（參考）

import datetime

today = datetime.date.today()
min_today = datetime.datetime.combine(today, datetime.time.min)
max_today = datetime.datetime.combine(today, datetime.time.max)

interval_min = 5  # 可配置間隔
slots = int(24 * 60 / interval_min)

selects = []
vars = []
for i in range(slots):
    start = min_today + datetime.timedelta(minutes=interval_min * i)
    end = min_today + datetime.timedelta(minutes=interval_min * (i + 1))
    selects.append(
        f'SELECT COUNT(*) FROM `cmd_info` '
        f'WHERE `time_str` BETWEEN "{start:%Y-%m-%d %H:%M:%S}" AND "{end:%Y-%m-%d %H:%M:%S}" '
        f'INTO @count_{i};'
    )
    vars.append(f'@count_{i}')

proc_sql = f"""
DELIMITER //
DROP PROCEDURE IF EXISTS `usp_cmd_info`;
CREATE PROCEDURE `usp_cmd_info`(IN dates VARCHAR(12))
BEGIN
{chr(10).join(selects)}
    SELECT {', '.join(vars)};
END //
DELIMITER ;
"""
print(proc_sql)