MySQL 按指定天數分組統計：DAYOFYEAR + DIV 技巧實現靈活時間切片

業務場景

需按 **自定義天數** (如 11 天、7 天、30 天) 對時間序列數據分組聚合，而非固定日/週/月。

核心 SQL 技巧

SELECT 
  job_id,
  COUNT(*) AS group_num
FROM your_table
GROUP BY 
  job_id,
  DAYOFYEAR(`date`) DIV 11;  -- 關鍵：一年天數除以分組天數取整

原理解析

1. `DAYOFYEAR(date)`：返回日期在當年的第幾天 (1-366)
2. `DIV 11`：整數除法，每 11 天產生一個分組編號 (0, 1, 2...)
3. 同一 `job_id` 下，相同分組編號的行歸為一組

完整示例

-- 建表測試
CREATE TABLE t_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT,
  log_date DATE
);

-- 插入跨年測試數據
INSERT INTO t_log(job_id, log_date) VALUES
(1, '2023-12-28'), (1, '2023-12-30'), (1, '2024-01-02'), (1, '2024-01-10');

-- 按 3 天分組 (跨年驗證)
SELECT 
  job_id,
  DAYOFYEAR(log_date) DIV 3 AS grp,
  COUNT(*) AS cnt,
  MIN(log_date) AS start_dt,
  MAX(log_date) AS end_dt
FROM t_log
GROUP BY job_id, DAYOFYEAR(log_date) DIV 3
ORDER BY job_id, grp;

輸出結果

job_id | grp | cnt | start_dt   | end_dt
-------|-----|-----|------------|------------
1      | 122 | 2   | 2023-12-28 | 2023-12-30  (第 362,364 天 → DIV 3 = 120,121? 待驗證)
1      | 0   | 2   | 2024-01-02 | 2024-01-10  (第 2,10 天 → DIV 3 = 0,3)

-- 方案：以某固定基準日為起點計算天數差
SELECT 
  job_id,
  FLOOR(DATEDIFF(log_date, '2020-01-01') / 11) AS grp,
  COUNT(*) AS cnt
FROM t_log
GROUP BY job_id, FLOOR(DATEDIFF(log_date, '2020-01-01') / 11);