如何用无循环精简SQL按自定义年度日期区间GROUP BY汇总数据

架构老林

2026-8-26

自定义年度区间的SQL分组汇总方案

场景

需要按自定义日期区间进行数据分组汇总，常规GROUP BY年份无法满足需求。自定义年度区间定义为：上年12月20日00:00:00至当年12月19日23:59:59。

现有History表数据

id | date                | income  | spent  
--------------------------------------------
1  | 2019-12-21 17:15:00 | 600,00  | NULL
2  | 2019-12-23 12:55:00 | 183,00  | NULL
3  | 2019-12-30 20:05:00 | NULL    | 25,00
4  | 2020-01-01 15:35:00 | NULL    | 13,00
5  | 2020-01-01 20:25:00 | NULL    | 500,50
6  | 2020-12-10 10:25:00 | NULL    | 5,50
7  | 2021-05-22 12:45:00 | 1098,00 | NULL
8  | 2021-05-23 10:18:00 | NULL    | 186,00
9  | 2021-11-25 12:32:00 | NULL    | 10,00
10 | 2021-12-23 10:35:00 | NULL    | 10,00

预期汇总结果

Year | Summary Income | Summary Spent | Difference
--------------------------------------------------
2020 |         783,00 |        544,00 |     239,50
2021 |        1098,00 |        196,00 |     902,00
2022 |           0,00 |         10,00 |     -10,00

当前实现（存储过程循环）

...
SET @Aa = (SELECT MIN(date) FROM History);
CREATE TEMPORARY TABLE Yr (Year VARCHAR(4), Income FLOAT(8,2), Spent FLOAT(8,2), differ FLOAT(8,2));
Yearly: LOOP
SET @Aa = @Aa + 1;
SET @From = CONCAT((@Aa - 1), '-12-20 00:00:00');
SET @To = CONCAT(@Aa, '-12-19 23:59:59');
SET @Count = (SELECT SUM(income) FROM History WHERE date >= @From AND date <= @To);
SET @diff = (SELECT SUM(spent)  FROM History WHERE date >= @From AND date <= @To);
INSERT INTO Yr (Year, Income, Spent, differ) VALUES (@Aa, @Count, @diff, (@Count - @diff));
IF (@Aa = (SELECT MAX(YEAR(date)) FROM History)) THEN LEAVE Yearly; END IF;
END LOOP;
SELECT * FROM Yr;
...

问题

是否可以不使用循环，通过精简的SQL语句实现该自定义年度区间的汇总需求？

解决方案：无循环的SQL实现

可以通过日期偏移计算分组键的方式替代循环，核心思路是先判断每条记录所属的自定义年度区间，再直接分组汇总，同时生成完整年度序列确保无数据年度也能显示。

完整SQL语句

WITH year_range AS (
    -- 生成覆盖所有涉及自定义年度的序列
    SELECT MIN(custom_year) AS year_num
    FROM (
        SELECT CASE 
            WHEN MONTH(date) = 12 AND DAY(date) >= 20 THEN YEAR(date) + 1
            ELSE YEAR(date)
        END AS custom_year
        FROM History
    ) t
    UNION ALL
    SELECT year_num + 1
    FROM year_range
    WHERE year_num < (SELECT MAX(custom_year) FROM (
        SELECT CASE 
            WHEN MONTH(date) = 12 AND DAY(date) >= 20 THEN YEAR(date) + 1
            ELSE YEAR(date)
        END AS custom_year
        FROM History
    ) t)
),
history_summary AS (
    -- 按自定义年度分组汇总收支
    SELECT 
        CASE 
            WHEN MONTH(date) = 12 AND DAY(date) >= 20 THEN YEAR(date) + 1
            ELSE YEAR(date)
        END AS custom_year,
        COALESCE(SUM(REPLACE(income, ',', '.')), 0) AS summary_income,
        COALESCE(SUM(REPLACE(spent, ',', '.')), 0) AS summary_spent
    FROM History
    GROUP BY custom_year
)
-- 关联年度序列与汇总结果，格式化输出
SELECT 
    yr.year_num AS `Year`,
    FORMAT(hs.summary_income, 2, 'de_DE') AS `Summary Income`,
    FORMAT(hs.summary_spent, 2, 'de_DE') AS `Summary Spent`,
    FORMAT(hs.summary_income - hs.summary_spent, 2, 'de_DE') AS `Difference`
FROM year_range yr
LEFT JOIN history_summary hs ON yr.year_num = hs.custom_year
ORDER BY yr.year_num;

关键逻辑说明

自定义年度判断：用CASE语句识别每条记录所属的自定义年度——若日期在12月20日及以后，则归为下一年的区间，否则归为当年区间。
年度序列生成：通过递归CTEyear_range生成所有需要统计的年度，确保像2022这种只有单条数据的年度不会被遗漏。
数据格式处理：
- REPLACE(income, ',', '.')：将原始数据的逗号小数点转换为标准格式，保证求和计算正确。
- COALESCE(..., 0)：把无数据的收支值替换为0，避免显示NULL。
- FORMAT(..., 2, 'de_DE')：将结果格式化为带逗号小数点的样式，匹配预期输出格式。

内容的提问来源于stack exchange，提问作者colper89