如何用递归SQL计算Gnucash中的账户余额

背景 #

使用Gnucash一年，近期制作自定义财务报表时，卡在了计算包含子账户的账户累计余额上。

数据库架构 #

涉及核心表：

• accounts表：字段含guid、name、account_type、commodity_guid、commodity_scu、non_std_scu、parent_guid、code、description、hidden、placeholder
• transactions表：字段含guid、currency_guid、num、post_date、enter_date、description
• splits表：字段含guid、tx_guid、account_guid、memo、action、reconcile_state、reconcile_date、value_num、value_denom、quantity_num、quantity_denom、lot_guid

目标 #

生成展示账户层级、并汇总当前账户及所有下级子账户余额的财务报表。

遇到的问题 #

尝试递归SQL查询时触发错误：ERROR: aggregate functions are not allowed in a recursive query's recursive term，原查询代码：

WITH RECURSIVE AccountHierarchy AS
    (SELECT a.guid,
            a.parent_guid,
            a.code,
            a.name,
            0 AS level,
            SUM(COALESCE(s.value_num, 0)) as value_num
     FROM accounts a
     LEFT JOIN splits s ON a.guid = s.account_guid
     WHERE parent_guid IS NULL
     GROUP BY a.guid,
              a.parent_guid,
              a.code,
              a.name
     UNION ALL SELECT a2.guid,
                      a2.parent_guid,
                      a2.code,
                      a2.name,
                      ah.level + 1 AS level,
                      SUM(COALESCE(s2.value_num, 0)) + SUM(ah.value_num) AS value_num
     FROM accounts AS a2
     JOIN AccountHierarchy AS ah ON a2.parent_guid = ah.guid
     LEFT JOIN splits s2 ON a2.guid = s2.account_guid
     GROUP BY a2.guid,
              a2.parent_guid,
              a2.code,
              a2.name,
              ah.level)
SELECT guid,
       parent_guid,
       level,
       code,
       name
FROM AccountHierarchy
WHERE parent_guid IS NOT NULL
ORDER BY code,
         level,
         name;

修正方案 #

错误根源是递归CTE的递归部分不允许使用聚合函数，需拆分计算步骤：

1. 先预计算每个账户自身的基础余额 #

单独统计所有账户的交易余额，避免在递归逻辑中做聚合：

WITH AccountBalances AS (
    SELECT 
        a.guid,
        a.parent_guid,
        a.code,
        a.name,
        COALESCE(SUM(s.value_num), 0) AS self_balance
    FROM accounts a
    LEFT JOIN splits s ON a.guid = s.account_guid
    GROUP BY a.guid, a.parent_guid, a.code, a.name
),

2. 递归构建账户层级并累加余额 #

递归部分仅做层级关联，直接累加当前账户的自身余额与父级的累计余额：

AccountHierarchy AS (
    SELECT 
        ab.guid,
        ab.parent_guid,
        ab.code,
        ab.name,
        0 AS level,
        ab.self_balance AS total_balance
    FROM AccountBalances ab
    WHERE ab.parent_guid IS NULL
    UNION ALL
    SELECT 
        ab.guid,
        ab.parent_guid,
        ab.code,
        ab.name,
        ah.level + 1 AS level,
        ab.self_balance + ah.total_balance AS total_balance
    FROM AccountBalances ab
    JOIN AccountHierarchy ah ON ab.parent_guid = ah.guid
)

3. 查询最终结果 #

SELECT 
    guid,
    parent_guid,
    level,
    code,
    name,
    total_balance
FROM AccountHierarchy
-- 若只需非顶级账户则保留此条件，否则删除
WHERE parent_guid IS NOT NULL
ORDER BY code, level, name;

补充说明 #

• 预计算账户自身余额的步骤是一次性聚合，避免递归过程中重复计算交易数据
• 若需处理多币种余额，需结合commodity_guid和value_denom字段添加汇率转换逻辑，当前代码默认同币种计算

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