PostgreSQL如何创建带层级子标题的汇总表且无需生成中间表

可行性说明 #

• 需求可完全通过原生SQL实现，无需额外Python处理步骤
• 无需创建中间summary表，直接基于final_output表嵌套计算即可生成目标汇总表，核心依赖PostgreSQL的GROUPING SETS（多维度层级聚合）和条件聚合语法实现。

具体实现代码 #

WITH base_agg AS (
    -- 基础聚合逻辑，替代中间summary表，直接从原始表计算
    SELECT 
        marking,
        area,
        division,
        status,
        SUM(CAST(amount AS INT)) AS amount,
        COUNT(*) AS cnt
    FROM final_output
    WHERE marking = 'A1'
    GROUP BY marking, area, division, status
)
SELECT 
    -- 区分行类型，生成展示用的区域/分部/总计标识
    CASE
        WHEN GROUPING(area) = 1 THEN '**GRAND TOTAL**'
        WHEN GROUPING(division) = 1 THEN CONCAT('**', area, '**')
        ELSE division
    END AS display_name,
    -- 拆分不同Status的金额、计数
    SUM(CASE WHEN status = 'Status1' THEN amount ELSE 0 END) AS status1_amount,
    SUM(CASE WHEN status = 'Status1' THEN cnt ELSE 0 END) AS status1_count,
    SUM(CASE WHEN status = 'Status2' THEN amount ELSE 0 END) AS status2_amount,
    SUM(CASE WHEN status = 'Status2' THEN cnt ELSE 0 END) AS status2_count,
    SUM(CASE WHEN status = 'Status3' THEN amount ELSE 0 END) AS status3_amount,
    SUM(CASE WHEN status = 'Status3' THEN cnt ELSE 0 END) AS status3_count,
    -- 总计数、总金额
    SUM(cnt) AS total_count,
    SUM(amount) AS total_amount
FROM base_agg
-- 多维度聚合：同时生成分部明细、区域合计、全局总计三个层级的数据
GROUP BY GROUPING SETS (
    (area, division, status), -- 分部明细层级
    (area, status), -- 区域合计层级
    (status) -- 全局总计层级
)
-- 排序保证顺序符合要求：区域合计在前，对应分部在后，最后输出全局总计
ORDER BY
    GROUPING(area) ASC,
    area ASC,
    GROUPING(division) DESC,
    division ASC

代码说明 #

• 内层base_agg CTE直接替换你原来的中间表summary，无需持久化存储，计算结果直接供外层调用
• GROUPING SETS语法可以一次性生成多维度聚合结果，避免写多个UNION ALL拼接不同层级的汇总数据
• GROUPING()函数用来判断当前聚合行的维度，用来区分是全局总计行、区域合计行还是分部明细行，同时用来控制排序逻辑
• 如果后续新增Status类型，只需要在条件聚合部分新增对应CASE WHEN分支即可

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