業務常需按固定時間間隔 (如 10 分鐘) 聚合統計用戶登入、操作行為。本文對比多種 GROUP BY 寫法與存儲過程方案,提供動態間隔調整的最佳實踐,並附 Python 自動生成存儲過程代碼。
表 cmd_info 含 time_str (DATETIME) 與 date_str (DATE/INT),需統計 2016-11-20 每 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;
-- 127 rows
截取至分鐘十位,補 '0' 形成 YY-MM-DD HH:M0 格式。缺點:間隔硬編碼,難以動態調整。
SELECT ROUND(UNIX_TIMESTAMP(time_str) / (10 * 60)) AS timekey, COUNT(*)
FROM `security`.`cmd_info`
WHERE `date_str` = 20161120
GROUP BY timekey ORDER BY timekey;
-- 126 rows
將時間戳轉秒,除以間隔秒數 (600) 取整。優點:修改分母即可調整間隔 (5分、15分、30分、1小時等),效能相當。
-- ROUND 四捨五入 (145 rows,邊界偏差大)
SELECT CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', ROUND(MINUTE(time_str)/10,0)*10), COUNT(*)
FROM `security`.`cmd_info` WHERE `date_str`=20161120
GROUP BY DATE(time_str), HOUR(time_str), ROUND(MINUTE(time_str)/10,0)*10;
-- FLOOR 向下取整 (127 rows,與 DATE_FORMAT 等價) ★推薦
SELECT CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', FLOOR(MINUTE(time_str)/10)*10), COUNT(*)
FROM `security`.`cmd_info` WHERE `date_str`=20161120
GROUP BY DATE(time_str), HOUR(time_str), FLOOR(MINUTE(time_str)/10)*10;
-- CEIL 向上取整 (151 rows)
SELECT CONCAT(DATE(time_str), ' ', HOUR(time_str), ':', CEIL(MINUTE(time_str)/10)*10), COUNT(*)
FROM `security`.`cmd_info` WHERE `date_str`=20161120
GROUP BY DATE(time_str), HOUR(time_str), CEIL(MINUTE(time_str)/10)*10;
FLOOR 向下取整符合自然時間區間定義 (00:00-00:09 → 00:00),結果最直觀。
硬編碼 144 個 10 分鐘區間,每區間獨立 BETWEEN 查詢賦值給變數,最後 SELECT @count_0, @count_1, ...。代碼量巨大、維護困難、效能低下 (144 次掃描/索引查找)。
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)
sql_parts = []
var_parts = []
for x in range(slots):
start = min_today + datetime.timedelta(minutes=interval_min * x)
end = min_today + datetime.timedelta(minutes=interval_min * (x + 1))
sql_parts.append(
'SELECT COUNT(*) FROM `cmd_info` WHERE `time_str` BETWEEN "{0}" AND "{1}" INTO @count_{2};'
.format(start.strftime('%Y-%m-%d %H:%M:%S'), end.strftime('%Y-%m-%d %H:%M:%S'), x)
)
var_parts.append('@count_{0}'.format(x))
print('\n'.join(sql_parts))
print('SELECT {0};'.format(', '.join(var_parts)))
| 方案 | 靈活性 | 效能 | 適用場景 |
|---|---|---|---|
| DATE_FORMAT | 低 (固定間隔) | 中 | 固定 10分/1小時報表 |
| UNIX_TIMESTAMP 數學法 | 高 (動態間隔) | 中 | 通用推薦 |
| FLOOR 函數法 | 中 | 中 | 需顯式時間字串輸出 |
| 存儲過程 | 極低 | 差 (N 次掃描) | 極少數兼容性場景 |
date_str 分區,配合 time_str 索引核心原則:用單條 GROUP BY 掃描完成聚合,避免多次掃描或複雜存儲過程。動態間隔選 UNIX_TIMESTAMP / interval_seconds,固定間隔需字串輸出選 FLOOR(MINUTE()/interval)*interval。