SQL视图预求和列二次聚合结果放大16倍异常排查

问题背景 #

业务关联规则如下：

• 1个订单对应2个consignment（发货单）
• 每个发货单可关联多个货品，每个货品对应独立重量值
• 最终需求为按发票维度单行输出，展示该发票关联的所有订单参考号、对应所有订单的总重量
  初始实现思路为先创建视图做订单维度的重量预聚合，方便后续扩展其他字段、降低维护难度。

初始视图实现 #

初始视图使用窗口函数按订单维度聚合货品总重量，创建SQL如下：

CREATE OR REPLACE VIEW test_view AS
SELECT orders.reference,
  sum(aposition.GEWICHTF) OVER (PARTITION BY consignment.ordernr) "Weight"
FROM orders
LEFT JOIN consignment ON orders.nr=consignment.ordernr
LEFT JOIN aposition ON aposition.consignmentNr=consignment.nr
WHERE orders.number>0
AND orders.inrecyclebin=0
AND orders.customernr=3450588;

直接查询该视图结果符合预期：返回2条记录，reference分别为1、2，对应Weight值均为24000，两个订单总重量预期为48000。

二次聚合异常 #

基于视图按发票维度做二次聚合查询时，结果出现异常放大，实际返回总重量为384000（为预期值的16倍），仅订单参考号拼接结果正常为"1, 2"。异常查询SQL如下：

SELECT distinct afaktura.fakturanr,
  (
    select listagg(t.n,', ') WITHIN GROUP (ORDER BY t.n)
    FROM (
      select (UKV.aexternauftragsnr) n
      FROM test_view UKV
      where ukv.fakturanr=afaktura.fakturanr
    ) t
  ) "Reference Number",
  sum(test_view."Weight") OVER (PARTITION BY afaktura.fakturanr) "Weight"
FROM afaktura
LEFT JOIN test_view ON test_view.fakturanr = afaktura.fakturanr
LEFT JOIN calculation ON calculation.FK_IDAFAKTURA=afaktura.idafaktura
LEFT JOIN consignment ON consignment.nr=calculation.consignmentNr
LEFT JOIN aposition ON aposition.consignmentNr=consignment.nr
LEFT JOIN orders ON akopf.nr=consignment.ordernr
WHERE orders.number>0
AND orders.inrecyclebin=0
AND orders.customernr=3450588;

异常根因 #

• 冗余关联触发笛卡尔积导致行数膨胀：外层查询已经关联了预聚合的test_view，又重复关联了calculation、consignment、aposition、orders这些视图内部已经关联过的明细表。按照业务关系，1个订单对应2个发货单，每个发货单关联多个货品行，多表关联后视图中原本的2条订单重量记录会被重复复制，最终每条重量记录被重复计数8次，总放大倍数16，和返回的384000（2400028）完全吻合。
• 重复值叠加求和导致结果放大：视图内使用窗口函数sum() over()不会折叠查询行数，同一订单的所有关联行都会携带相同的订单总重量值，外层再对这些重复携带的重量值做sum求和，会把所有重复行的重量全部累加，直接导致结果倍数级放大。
• 视图字段缺失导致关联逻辑不可靠：原视图定义中没有输出fakturanr（发票号）字段，外层直接用test_view.fakturanr做关联本身存在逻辑漏洞，容易出现字段取值错位的问题。

修正方案 #

第一步：优化视图定义，从根源避免重复行 #

将视图内的窗口函数替换为普通GROUP BY聚合，保证每个订单仅返回1条唯一的汇总记录，同时把后续关联需要用到的发票号、订单主键字段加入视图输出，修正后视图SQL如下：

CREATE OR REPLACE VIEW test_view AS
SELECT 
  orders.nr AS order_nr,
  orders.reference,
  orders.fakturanr,
  SUM(aposition.GEWICHTF) AS "Weight"
FROM orders
LEFT JOIN consignment ON orders.nr = consignment.ordernr
LEFT JOIN aposition ON aposition.consignmentNr = consignment.nr
WHERE orders.number > 0
  AND orders.inrecyclebin = 0
  AND orders.customernr = 3450588
GROUP BY orders.nr, orders.reference, orders.fakturanr;

优化点：视图层直接折叠到订单粒度，每个订单仅返回1行总重量值，后续关联不会再出现同一订单重量重复出现的问题。

第二步：简化外层查询，移除冗余关联 #

去掉外层和视图重复的明细表关联，使用普通GROUP BY聚合代替DISTINCT+窗口函数的写法，既保证逻辑清晰，也不会产生重复计算，修正后查询SQL如下：

SELECT 
  afaktura.fakturanr,
  LISTAGG(test_view.reference, ', ') WITHIN GROUP (ORDER BY test_view.reference) AS "Reference Number",
  SUM(test_view."Weight") AS "Weight"
FROM afaktura
LEFT JOIN test_view ON test_view.fakturanr = afaktura.fakturanr
-- 若需要从calculation表取发票相关的其他字段，先对calculation按发票维度去重后再关联，避免产生重复行
WHERE EXISTS (
  SELECT 1 
  FROM calculation
  JOIN consignment ON consignment.nr = calculation.consignmentNr
  JOIN orders ON orders.nr = consignment.ordernr
  WHERE calculation.FK_IDAFAKTURA = afaktura.idafaktura
    AND orders.number > 0
    AND orders.inrecyclebin = 0
    AND orders.customernr = 3450588
)
GROUP BY afaktura.fakturanr;

优化点：移除了重复的明细表关联，从根源避免笛卡尔积；直接按发票号分组聚合，不需要用窗口函数再做分区求和，配合视图层的唯一订单记录，最终返回的总重量为准确值。

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