PostgreSQL实现带转置的求和及动态年份分类统计需求

我来帮你搞定这个问题！你的核心痛点是要自动适配未来新增的年份，同时确保每个目标分类在每一年都有统计值（无数据时显示0），还要把D1/D2/D3合并成分类D。咱们可以用「生成全量年份-分类组合 + 左连接聚合数据」的思路来实现，完全不用依赖固定视图，能自动应对未来的年份变化。

完整SQL语句 #

假设你的表名为event_counts，下面是可以直接复用的查询语句：

WITH target_categories AS (
    -- 定义我们需要的最终分类：A、B、C、D，未来新增分类直接修改这个数组即可
    SELECT unnest(ARRAY['A', 'B', 'C', 'D']) AS category
),
all_year_category_pairs AS (
    -- 生成所有年份和目标分类的笛卡尔积，确保每个年份每个分类都有记录
    SELECT DISTINCT ec.year, tc.category
    FROM event_counts ec
    CROSS JOIN target_categories tc
),
aggregated_events AS (
    -- 预处理原表：合并D1/D2/D3为D，再按年份+新分类求和
    SELECT
        year,
        CASE
            WHEN category IN ('D1', 'D2', 'D3') THEN 'D'
            ELSE category
        END AS new_category,
        SUM(events) AS event_sum
    FROM event_counts
    GROUP BY year, new_category
)
-- 组合数据并格式化输出，用COALESCE把空值转为0，同时计算年度总事件数
SELECT
    aycp.year,
    SUM(COALESCE(ae.event_sum, 0)) AS "events-total",
    MAX(CASE WHEN aycp.category = 'A' THEN COALESCE(ae.event_sum, 0) END) AS "category A",
    MAX(CASE WHEN aycp.category = 'B' THEN COALESCE(ae.event_sum, 0) END) AS "category B",
    MAX(CASE WHEN aycp.category = 'C' THEN COALESCE(ae.event_sum, 0) END) AS "category C",
    MAX(CASE WHEN aycp.category = 'D' THEN COALESCE(ae.event_sum, 0) END) AS "category D"
FROM all_year_category_pairs aycp
LEFT JOIN aggregated_events ae
    ON aycp.year = ae.year AND aycp.category = ae.new_category
GROUP BY aycp.year
ORDER BY aycp.year;

关键逻辑拆解 #

咱们一步步看这个SQL的核心设计：

1. target_categories CTE：明确列出最终需要的分类集合，后续如果要新增分类，只需要修改这个数组，非常灵活。
2. all_year_category_pairs CTE：通过CROSS JOIN生成所有年份和目标分类的组合——不管该年份有没有对应分类的数据，都会生成一条记录。这里用DISTINCT year自动抓取表中所有年份，未来新增年份后，这个CTE会自动包含新年份，完全不用修改代码。
3. aggregated_events CTE：先把原表中的D1/D2/D3统一映射为D，再按年份和新分类聚合求和，得到每个年份每个有效分类的事件数。
4. 最终查询：把全量年份-分类组合和聚合数据左连接，用COALESCE把无数据的分类值转为0；再用CASE WHEN做行转列，把分类转换成列，同时计算每个年份的总事件数。

结果验证 #

用你提供的测试数据运行这个SQL，会得到和你期望完全一致的结果：

• 2022年：C的事件数为3，D的总和是113+1+2=116，总事件数16+13+3+116=148
• 2023年：C和D无数据，显示0，总事件数5+8+0+0=13

未来如果新增2024年的数据（哪怕只有分类A的记录），这个查询也会自动生成2024年的行，B、C、D列显示0，完美适配你的需求。

为什么之前的视图方案不行？ #

之前用多个视图的问题在于：视图是基于现有数据的聚合，不会主动生成「没有数据的年份-分类组合」，左连接后这些缺失的组合就不会出现在结果里。而我们用CROSS JOIN生成全量组合的方式，能确保每个年份每个分类都有记录，从根源上解决了无数据分类不显示的问题。

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