如何在SQL中实现plug/balance平衡代理科目的分类与金额计算

业务需求 #

• 现有业务数据表中，不同核算类型下有多类零散的小核算值科目：实际类核算包含X、Y，计划类核算包含AA、BB、CC、DD，这类零散科目的范围不固定，随时可以新增。
• 要把所有这类零散科目统一归为balance代理科目，这个科目的值固定按公式计算：final - (abc + def + ghi)。
• 目前尝试用sum (case when account not in ('abc','def','ghi')的逻辑编写查询语句，一直无法正常实现需求。

现有尝试代码的核心问题 #

你当前编写的SQL跑不通，主要是三个硬伤：

• case分支语法不对：多个分支之间缺少逻辑衔接，整个case块最后没有加end闭合，会直接触发数据库语法报错。
• 聚合嵌套逻辑错误：分组查询场景下，不能把sum()函数直接嵌套在另一层sum()的判断分支里，执行时会直接抛出逻辑错误。
• 分组维度不对：没有按照新的科目分类规则做聚合，算出来的数值结构和预期输出完全不匹配。

正确实现方案 #

这个场景根本不需要维护零散科目的枚举列表（毕竟零散科目随时可能新增），分两步计算即可：

1. 按年、月、核算类型三个维度分组，提前把abc、def、ghi、final四个固定科目的汇总值算出来作为统一计算基准。
2. 直接套用给定公式计算balance的取值，再把四个固定科目的汇总值和balance的计算值按行拼接，得到最终输出结果。
   参考可直接调整使用的SQL：

WITH base_metric AS (
  SELECT
    year,
    month,
    type,
    SUM(CASE WHEN accounts = 'abc' THEN amount ELSE 0 END) AS abc_sum,
    SUM(CASE WHEN accounts = 'def' THEN amount ELSE 0 END) AS def_sum,
    SUM(CASE WHEN accounts = 'ghi' THEN amount ELSE 0 END) AS ghi_sum,
    SUM(CASE WHEN accounts = 'final' THEN amount ELSE 0 END) AS final_sum
  FROM  你的业务表名
  GROUP BY year, month, type
)
SELECT year, month, type, 'abc' AS account_2, abc_sum AS amount FROM base_metric
UNION ALL
SELECT year, month, type, 'def' AS account_2, def_sum AS amount FROM base_metric
UNION ALL
SELECT year, month, type, 'ghi' AS account_2, ghi_sum AS amount FROM base_metric
UNION ALL
SELECT year, month, type, 'final' AS account_2, final_sum AS amount FROM base_metric
UNION ALL
SELECT year, month, type, 'balance' AS account_2,
  final_sum - (abc_sum + def_sum + ghi_sum) AS amount
FROM base_metric

方案适配性说明 #

• 完全不需要跟踪零散科目的变动，不管后续新增多少零散小值科目，只要四个固定科目的取数规则不变，SQL逻辑一行都不用调整。
• 没有复杂的嵌套聚合写法，数据库执行稳定性更高，哪一步数值计算偏差也可以单独校验排查。

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