如何查询层级区域表中每一行的递归总和？

给定存储区域层级关系的area表，其中population为区域自身不含子区域的人口数（单位：百万），需计算每个区域及其所有子区域的人口总和。

测试数据 #

CREATE TABLE IF NOT EXISTS area (
    id integer,
    parent_id integer,
    name text,
    population integer
);

INSERT INTO area VALUES
    (1, NULL, 'North America', 0),
    (2, 1, 'United States', 0),
    (3, 1, 'Canada', 39),
    (4, 1, 'Mexico', 129),
    (5, 2, 'Contiguous States', 331),
    (6, 2, 'Non-contiguous States', 2);

解决方案 #

使用递归CTE（Common Table Expression）遍历区域层级，收集每个节点及其所有子节点的人口数据，最后分组求和：

WITH RECURSIVE area_hierarchy AS (
    -- 初始步骤：将每个节点标记为自身的根节点
    SELECT id, name, population, id AS root_id
    FROM area
    UNION ALL
    -- 递归步骤：将子节点关联到其所有祖先节点的根ID
    SELECT child.id, child.name, child.population, parent.root_id
    FROM area child
    JOIN area_hierarchy parent ON child.parent_id = parent.id
)
SELECT 
    a.name,
    SUM(ah.population) AS sum
FROM area a
JOIN area_hierarchy ah ON a.id = ah.root_id
GROUP BY a.id, a.name
ORDER BY a.id;

执行结果 #

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