萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 實現 10 分鐘間隔分組統計:GROUP BY 進階技巧與存儲過程對比

業務常需按固定時間間隔 (如 10 分鐘) 聚合統計用戶登入、操作行為。本文對比多種 GROUP BY 寫法與存儲過程方案,提供動態間隔調整的最佳實踐,並附 Python 自動生成存儲過程代碼。

需求場景

表 cmd_info 含 time_str (DATETIME) 與 date_str (DATE/INT),需統計 2016-11-20 每 10 分鐘的記錄數,繪製日間分佈圖。

方案一:DATE_FORMAT 字串截取 (固定 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 格式。缺點:間隔硬編碼,難以動態調整。

方案二:UNIX_TIMESTAMP 數學運算 (動態間隔)

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 次掃描/索引查找)。

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)

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 索引
  • 物化視圖/摘要表預聚合,定時刷新
  • ClickHouse/Apache Druid 等 OLAP 引擎承接高頻統計查詢

核心原則:用單條 GROUP BY 掃描完成聚合,避免多次掃描或複雜存儲過程。動態間隔選 UNIX_TIMESTAMP / interval_seconds,固定間隔需字串輸出選 FLOOR(MINUTE()/interval)*interval。

copyright © 萬盛學電腦網 all rights reserved