递归查询求和叶子节点余额并向上汇总至父账户问题

问题描述 #

数据库包含两张核心表：

• account：存储账户的树形结构，字段包括id（主键）、parent_id（父账户ID）、account_name（账户名称）
• monthly_balance：仅存储叶子账户的月度余额，字段包括id（主键）、account_id（关联账户ID）、balance（余额）、year_month（统计月份）

需求是计算所有账户（包括各级父账户）的余额，父账户余额为其所有子节点（含叶子节点）的余额总和。后续需支持按日期区间过滤，但当前核心是解决树形余额汇总问题。此前尝试Stack Overflow上的方案，结果要么为空，要么计算错误。

示例场景 #

账户树形结构（括号内为叶子账户余额）：

A1
+- A1.1 (6)
+- A1.2
   +- A1.2.1 (1)
   +- A1.2.2 (10)
   +- A1.2.3 (3)
A2
+- A2.1 
   +- A2.1.1 (10)
   +- A2.1.2 (5)

预期结果 #

A1 = 20
A1.1 = 6
A1.2 = 14
A1.2.1 = 1
A1.2.2 = 10
A1.2.3 = 3
A2 = 15
A2.1 = 15
A2.1.1 = 10
A2.1.2 = 5

数据表结构及测试数据 #

创建表 #

CREATE TABLE account(
    id INT PRIMARY KEY,
    parent_id INT,
    account_name TEXT
);

CREATE TABLE monthly_balance(
    id INT PRIMARY KEY,
    account_id INT FOREIGN KEY REFERENCES account(id),
    balance NUMERIC,
    year_month DATE
);

插入测试数据 #

注：修正原数据中插入monthly_balance时误写为account的错误

INSERT INTO account VALUES 
(1, NULL, 'A1'),
(2, 1, 'A1.1'),
(3, 1, 'A1.2'),
(4, 3, 'A1.2.1'),
(5, 3, 'A1.2.2'),
(6, 3, 'A1.2.3'),
(7, NULL, 'A2'),
(8, 7, 'A2.1'),
(9, 8, 'A2.1.1'),
(10, 8, 'A2.1.2');

INSERT INTO monthly_balance VALUES 
(1, 2, 6, '2022-08-01'),
(2, 4, 1, '2022-08-01'),
(3, 5, 10, '2022-08-01'),
(4, 6, 3, '2022-08-01'),
(5, 9, 10, '2022-08-01'),
(6, 10, 5, '2022-08-01');

解决方案：递归CTE汇总余额 #

使用递归公共表表达式（CTE）遍历账户的树形结构，找到每个账户对应的所有叶子节点，再汇总叶子节点的余额得到该账户的总余额：

WITH RECURSIVE account_hierarchy AS (
    -- 递归起点：所有账户（包括父账户和叶子账户）
    SELECT 
        id AS account_id,
        id AS leaf_account_id,
        account_name
    FROM account
    UNION ALL
    -- 递归遍历：将父账户关联到其所有子节点（最终关联到叶子节点）
    SELECT 
        ah.account_id,
        a.id AS leaf_account_id,
        ah.account_name
    FROM account_hierarchy ah
    JOIN account a ON ah.leaf_account_id = a.parent_id
)
-- 汇总每个账户对应的所有叶子节点余额
SELECT 
    ah.account_name,
    COALESCE(SUM(mb.balance), 0) AS total_balance
FROM account_hierarchy ah
LEFT JOIN monthly_balance mb ON ah.leaf_account_id = mb.account_id
-- 可选：添加日期过滤条件
-- WHERE mb.year_month = '2022-08-01'
GROUP BY ah.account_id, ah.account_name
ORDER BY ah.account_name;

逻辑说明 #

1. 递归CTE account_hierarchy：
   • 初始查询：将每个账户自身作为起始节点
   • 递归查询：不断将父账户与子节点关联，最终每个父账户会关联到其所有层级的叶子节点
2. 余额汇总：将递归得到的账户-叶子节点关联关系，与monthly_balance表关联，汇总每个账户对应的所有叶子节点余额
3. COALESCE处理空值：确保没有叶子节点的账户（如果存在）返回0而非NULL

执行上述SQL后，即可得到与预期一致的所有账户余额结果。

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