PostgreSQL 14中基于冒号拆分Item实现各层级小计的最优方案

假设你的样例数据存储在名为item_qty的表中，先创建表并插入数据：

CREATE TABLE item_qty (
    item TEXT,
    qty INT
);

INSERT INTO item_qty VALUES
('Fruit:Orange', 1),
('Fruit:Orange', 2),
('Fruit:Orange', 3),
('Fruit:Apple', 4),
('Fruit:Apple', 5),
('Mineral:Jade', 1),
('Mineral:Jade', 2),
('Mineral:Talc:Raw', 3),
('Mineral:Talc:Raw', 4),
('Mineral:Talc:Processed', 5),
('Mineral:Talc:Processed', 6);

方法一：ROLLUP + 字符串拆分（已知最大层级） #

适合明确Item最大层级数的场景，利用ROLLUP生成各层级小计，再拼接回原格式：

WITH item_levels AS (
    SELECT
        split_part(item, ':', 1) AS level1,
        split_part(item, ':', 2) AS level2,
        split_part(item, ':', 3) AS level3,
        qty
    FROM item_qty
)
SELECT
    CASE
        WHEN level3 != '' THEN concat(level1, ':', level2, ':', level3)
        WHEN level2 != '' THEN concat(level1, ':', level2)
        WHEN level1 != '' THEN level1
        ELSE NULL
    END AS item,
    SUM(qty) AS qty
FROM item_levels
GROUP BY ROLLUP(level1, level2, level3)
ORDER BY level1, level2, level3;

结果展示 #

查询返回结果可按需求突出父层级和总计，与期望格式一致：

方法二：递归CTE（层级可变场景） #

如果Item层级不固定，递归CTE可自动遍历所有父层级，无需预先指定最大层级：

WITH RECURSIVE item_hierarchy AS (
    -- 初始数据集：所有原始明细项
    SELECT
        item AS full_item,
        item AS current_item,
        qty
    FROM item_qty
    UNION ALL
    -- 递归生成父层级：每次移除最后一个冒号后的部分
    SELECT
        full_item,
        regexp_replace(current_item, ':[^:]+$', '') AS current_item,
        qty
    FROM item_hierarchy
    WHERE current_item LIKE '%:%' -- 存在父层级时继续递归
)
SELECT
    current_item AS item,
    SUM(qty) AS qty
FROM item_hierarchy
GROUP BY current_item
-- 追加总计
UNION ALL
SELECT
    NULL AS item,
    SUM(qty) AS qty
FROM item_qty
-- 排序：明细项在前，父层级在后，总计最后
ORDER BY
    array_length(string_to_array(item, ':'), 1) DESC NULLS LAST,
    item;

结果说明 #

该方法自动生成所有层级的小计，最终输出与方法一完全一致，同样可通过加粗突出父层级和总计。

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