SQL连接交易与促销表后聚合计算结果异常问题求助

安全阿岳

2026-8-26

问题根因

核心问题是关联粒度不匹配触发笛卡尔积行膨胀，和JOIN类型选择无关：
你默认amazon-order-id + date是两表的唯一匹配键，但两张表的实际存储粒度都不是订单级：
- 交易流水表中，同一个订单+日期下会存在多条记录：单个订单包含多个SKU、同时产生佣金、配送费、退款、调整金等不同类型流水时，都会拆分为独立行存储
- 促销报表中，同一个订单+日期下也会存在多条记录：单个订单叠加优惠券、订阅折扣、配送促销、活动费等多类优惠时，每类优惠会单独存为一行
  两表明细直接JOIN时，左表的N条匹配行会和右表的M条匹配行组合为N*M行，后续SUM聚合时所有交易类、促销类数值都会被重复累加，无论换哪种JOIN类型都无法修正该偏差。
代码存在额外逻辑错误：
- 存储费关联子查询中，将仓储表别名设置为T2，和外层促销表的别名T2重名，会导致关联逻辑执行异常
- 参与聚合的sku、selling-fees、fba-fees、total、product-sales、item-promotion-discount等字段未明确指定所属表，两表存在同名字段时会触发取值错误
- 存储费计算未匹配SKU维度，会将同品牌同月份的总存储费重复叠加到每个SKU下，导致存储费整体虚高。

修复方案

核心逻辑是先按最终输出的聚合维度（品牌、月份、SKU）分别计算各表的指标，再将聚合后的结果做关联，从根源避免多对多关联导致的行重复计算。
修复后的参考代码如下：

WITH trans_agg AS (
    -- 先聚合交易表的所有交易类指标
    SELECT
        "brand"
        ,DATE_FROM_PARTS(YEAR("date"),MONTH("date"),1) AS stat_month
        ,"sku"
        ,ABS(SUM("selling-fees")) AS "Referral fee"
        ,ABS(SUM("fba-fees")) AS "FBA fee"
        ,SUM(CASE WHEN "type"= 'FBA Inventory Fee' AND "description" NOT LIKE 'FBA Inventory Storage Fee' THEN ABS("total") ELSE 0 END) AS "Inbound shipping fee"
        ,SUM(CASE WHEN "type" = 'Refund' THEN ABS("product-sales") ELSE 0 END) AS "Refund"
        ,SUM(CASE WHEN "type" = 'Deal Fee' THEN ABS("total") ELSE 0 END) AS "SC Promotion Deal Fee"
        ,-(SUM(CASE WHEN "type" = 'Adjustment' THEN "total" ELSE 0 END)) AS "Adjustment"
        ,SUM(CASE WHEN "type" IS NULL THEN ABS("total") ELSE 0 END) AS "SC Coupon Redemption Fee"
    FROM "NEW_DATA_WH"."PUBLIC"."TRANSACTION_REPORT"
    WHERE "type" IN ('Order','FBA Inventory Fee','Refund','Deal Fee','Adjustment') OR "type" IS NULL
    GROUP BY 1,2,3
),
promo_agg AS (
    -- 先聚合促销表的所有促销类指标
    SELECT
        "brand"
        ,DATE_FROM_PARTS(YEAR("date"),MONTH("date"),1) AS stat_month
        ,"sku"
        ,SUM(CASE WHEN "description" LIKE '%... VPC%' THEN "item-promotion-discount" ELSE 0 END) AS "SC Coupon Spend"
        ,SUM(CASE WHEN "description" LIKE ANY ('%S & S%','%Subscribe and Save%') THEN "item-promotion-discount" ELSE 0 END) AS "SC Subscribe and Save"
        ,SUM("item-promotion-discount") AS "SC Cumulative Promo Spend"
        ,SUM(CASE WHEN "description" LIKE ANY ('%Free Sub SameDay%','%SameDay US Promotion%','%US Core Free Shipping Promotion%') THEN "item-promotion-discount" ELSE 0 END) AS "SC Shipping Promo"
    FROM "NEW_DATA_WH"."PUBLIC"."PROMOTION_REPORT"
    GROUP BY 1,2,3
),
storage_agg AS (
    -- 先聚合仓储表的存储费指标
    SELECT
        "brand"
        ,DATE_FROM_PARTS("year","month",1) AS stat_month
        ,"sku"
        ,IFNULL(SUM("estimated_monthly_storage_fee"),0) AS "Storage fee"
    FROM "NEW_DATA_WH"."PUBLIC"."STORAGE_REPORT"
    GROUP BY 1,2,3
)
-- 三个聚合后的结果按维度左连，不会产生重复行
SELECT
    t1."brand" AS "Brand"
    ,t1.stat_month AS "Date"
    ,t1."sku"
    ,t1."Referral fee"
    ,t1."FBA fee"
    ,t1."Inbound shipping fee"
    ,t1."Refund"
    ,t1."SC Promotion Deal Fee"
    ,t1."Adjustment"
    ,t1."SC Coupon Redemption Fee"
    ,COALESCE(t3."Storage fee",0) AS "Storage fee"
    ,COALESCE(t2."SC Coupon Spend",0) AS "SC Coupon Spend"
    ,COALESCE(t2."SC Subscribe and Save",0) AS "SC Subscribe and Save"
    ,COALESCE(t2."SC Cumulative Promo Spend",0) AS "SC Cumulative Promo Spend"
    ,COALESCE(t2."SC Shipping Promo",0) AS "SC Shipping Promo"
FROM trans_agg t1
LEFT JOIN promo_agg t2
    ON t1."brand" = t2."brand"
    AND t1.stat_month = t2.stat_month
    AND t1."sku" = t2."sku"
LEFT JOIN storage_agg t3
    ON t1."brand" = t3."brand"
    AND t1.stat_month = t3.stat_month
    AND t1."sku" = t3."sku"
ORDER BY 1,2,3;

注意：如果你的促销表、仓储表中没有sku字段，需要先在对应子查询中按订单维度关联拿到SKU信息后再做聚合，避免维度不匹配。

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