PostgreSQL：将计算的过期日期作为新行置于原日期行下方而非单独列

现有vouchers表结构及数据如下：

CREATE TABLE vouchers (
    id SERIAL PRIMARY KEY,
    collected_date DATE,
    collected_volume INT);
INSERT INTO vouchers(collected_date, collected_volume)VALUES 
('2024-02-15', 1),
('2024-03-09', 900),
('2024-04-20', 300),
('2024-04-20', 800),
('2024-05-24', 400),
('2025-01-17', 200),
('2025-02-15', 800),
('2025-02-15', 150);

期望得到如下结果：

需要实现的逻辑：

• 计算expire_date：collected_date加上12个月
• 将expire_date作为新行放在对应collected_date行的下方，而非单独列
• 新行的collected_volume为对应collected_date总collected_volume的负数

注意：若生成的expire_date与原有collected_date重合，需保持两行独立，不合并求和（比如2024-02-15生成的2025-02-15行，要和原有的2025-02-15行分开）

当前使用的查询无法实现将expire_date转为行的效果：

select collected_date as collected_date
     , (collected_date + interval '12 months')::date as expire_date
     , sum(collected_volume) as collected_volume
from vouchers
group by 1,2
order by 1,2;

要实现将原数据行和对应的过期行作为独立行输出，需要先按日期聚合原数据，再用UNION ALL将聚合后的原数据行和生成的过期行合并，最后通过排序保证原行和对应过期行相邻。

具体SQL如下：

WITH aggregated_vouchers AS (
    SELECT 
        collected_date,
        SUM(collected_volume) AS total_volume
    FROM vouchers
    GROUP BY collected_date
)
SELECT 
    collected_date,
    total_volume AS collected_volume
FROM aggregated_vouchers
UNION ALL
SELECT 
    (collected_date + INTERVAL '12 months')::DATE AS collected_date,
    -total_volume AS collected_volume
FROM aggregated_vouchers
ORDER BY 
    -- 先按年、月、日排序，保证同一日期的原行和过期行排在一起
    DATE_TRUNC('year', collected_date),
    EXTRACT('month' FROM collected_date),
    EXTRACT('day' FROM collected_date),
    -- 让原行排在过期行前面
    CASE WHEN collected_date IN (SELECT collected_date FROM aggregated_vouchers) THEN 1 ELSE 2 END;

逻辑说明： #

1. CTE aggregated_vouchers：先按collected_date聚合，计算每个日期的总collected_volume，处理同一天多条数据的求和（比如2024-04-20的300+800=1100）。
2. UNION ALL合并行：第一部分输出聚合后的原数据行，第二部分输出过期行（日期加12个月，数值取反）。
3. 排序规则：
   • 先按年、月、日排序，保证同一日期的原行和过期行相邻；
   • 最后用CASE表达式让原行排在对应过期行的前面。

这样就能完全匹配期望的结果，同时保证重合日期的行独立不合并。

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