您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 按時間段分組統計會員數：CASE WHEN vs FROM_UNIXTIME 效能對比

編輯：MySQL 綜合教學

MySQL 時間維度分組統計：兩種寫法效能實測與最佳選擇

需求：按小時統計會員上線數，create_time 為 Unix 時間戳 (INT)。

方案一：CASE WHEN 硬編碼區間 (不推薦)

SELECT 
    COUNT(DISTINCT user_id) AS user_count,
    CASE
        WHEN create_time > 1395046800 AND create_time < 1395050400 THEN '17:00-18:00'
        WHEN create_time > 1395050400 AND create_time < 1395054000 THEN '18:00-19:00'
        WHEN create_time > 1395054000 AND create_time < 1395057600 THEN '19:00-20:00'
        WHEN create_time > 1395057600 AND create_time < 1395061200 THEN '20:00-21:00'
        ELSE 'unknown'
    END AS `date`
FROM tb_user_online_log
WHERE create_time > 1395046800 AND create_time < 1395061200
GROUP BY `date`
ORDER BY create_time;

缺點：硬編碼時間戳難維護、新增區間需改 SQL、CASE 條件逐行評估效能差、無法利用索引範圍掃描。

方案二：FROM_UNIXTIME + 格式化分組 (推薦)

SELECT 
    COUNT(DISTINCT user_id) AS user_count,
    FROM_UNIXTIME(create_time, '%Y-%m-%d %H:00:00') AS hours,
    CONCAT(
        FROM_UNIXTIME(create_time, '%Y-%m-%d %H:00'),
        '-',
        FROM_UNIXTIME(create_time, '%H') + 1,
        ':00'
    ) AS `date`
FROM tb_user_online_log
GROUP BY hours
ORDER BY create_time;

優勢：

動態轉換，無需硬編碼時間戳
可直接對 create_time 建索引，WHERE create_time BETWEEN ... 走索引範圍掃描
GROUP BY hours 利用隱式分組排序
程式碼簡潔、可維護性強

效能對比 (百萬級測試)

進階：產生連續時間序列 (填補無資料小時)

-- 1. 建立數字輔助表 (0-23)
CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums VALUES (0),(1),(2)...(23);

-- 2. 左連接填補
SELECT 
    DATE_FORMAT(DATE_ADD('2024-01-15 00:00:00', INTERVAL n HOUR), '%Y-%m-%d %H:00') AS hour,
    COALESCE(t.cnt, 0) AS user_count
FROM nums
LEFT JOIN (
    SELECT FROM_UNIXTIME(create_time, '%Y-%m-%d %H:00') AS hour, COUNT(DISTINCT user_id) AS cnt
    FROM tb_user_online_log
    WHERE create_time BETWEEN UNIX_TIMESTAMP('2024-01-15 00:00:00') AND UNIX_TIMESTAMP('2024-01-15 23:59:59')
    GROUP BY hour
) t ON nums.hour = t.hour
ORDER BY hour;

分組統計常用技巧補充

分組後計總行數：SELECT COUNT(*) FROM (SELECT 1 FROM tbl GROUP BY id) a;
日期分組：SELECT DATE_FORMAT(FROM_UNIXTIME(`time`), '%Y-%m-%d') AS day, COUNT(*) FROM tbl GROUP BY day;
只取前 N 組：GROUP BY hour ORDER BY user_count DESC LIMIT 10;