SQL Server 按固定時間間隔分組統計：CTE + spt_values 實現動態時間桶

需求：將記錄按 3 分鐘間隔分組，統計每組中 Status=1 的出現次數，並輸出區間起止時間。本文提供基於 CTE 和系統表 master.dbo.spt_values 生成時間序列的完整方案。

核心思路

1. 用 CTE (WITH tb AS (...)) 模擬源數據。
2. 利用 spt_values (type='P') 生成 0,1,2... 序列，對應時間桶編號。
3. 計算每桶的起始時間：DATEADD(minute, number*3, min_time)。
4. LEFT JOIN 源數據，條件為記錄時間落在桶區間內。
5. 聚合統計 Status=1 的計數。

完整 SQL

WITH tb(ID, [time], [Status]) AS (
    SELECT '215CSZ00','2015/11/30 23:53:41','2' UNION ALL
    SELECT '21CUG0HB','2015/11/30 23:54:17','1' UNION ALL
    SELECT '21CUG0HB','2015/11/30 23:54:22','1' UNION ALL
    SELECT '21UA75CS','2015/11/30 23:54:54','1' UNION ALL
    SELECT '215CSZ00','2015/11/30 23:56:17','2' UNION ALL
    SELECT '21ZCYPUY','2015/11/30 23:57:06','2' UNION ALL
    SELECT '2102200A','2015/11/30 23:57:45','1' UNION ALL
    SELECT '21NNACHH','2015/11/30 23:58:20','1' UNION ALL
    SELECT '21UA75CS','2015/11/30 23:58:27','2' UNION ALL
    SELECT '21UA75CS','2015/12/01 01:58:27','1'  -- 跨天測試
),
params AS (
    SELECT MIN([time]) AS min_time, MAX([time]) AS max_time
    FROM tb WHERE [Status] = '1'
),
buckets AS (
    SELECT number,
           DATEADD(minute, number*3, min_time) AS bucket_start,
           DATEADD(minute, (number+1)*3, min_time) AS bucket_end
    FROM master.dbo.spt_values, params
    WHERE type = 'P'
      AND DATEADD(minute, number*3, min_time) <= max_time
)
SELECT
    b.number AS bucket_no,
    b.bucket_start,
    b.bucket_end,
    COUNT(t.ID) AS status1_count
FROM buckets b
LEFT JOIN tb t
  ON t.[Status] = '1'
 AND t.[time] >= b.bucket_start
 AND t.[time] < b.bucket_end
GROUP BY b.number, b.bucket_start, b.bucket_end
ORDER BY b.number;

結果示例

bucket_no  bucket_start           bucket_end             status1_count
0          2015-11-30 23:54:17    2015-11-30 23:57:17    3
1          2015-11-30 23:57:17    2015-11-30 23:60:17    2
...        ...                    ...                    ...

關鍵點解析

• spt_values 是系統內置表，type='P' 返回 0~2047 連續整數，適合生成序列。
• 用 params CTE 獲取全局最小/最大時間，避免重複計算。
• LEFT JOIN 保證無數據的桶也輸出（計數為 0）。
• 時間區間採用 [start, end) 左閉右開，避免邊界重複計數。

生產環境優化建議

1. 建立日曆表 預存所有時間桶，查詢直接 JOIN，性能更優。
2. 大數據量時，在源表 [time] 列建立索引。
3. 參數化間隔分鐘數，做成存儲過程或函數復用。

掌握「生成序列 + 左連接 + 分組聚合」模式，可靈活應對各類固定間隔統計需求。