Snowflake SQL一对多关联查询时订单金额重复问题求助

我需要关联两张表：订单物料表SALES_ORDERS和物料账单日期表BILLING。使用LEFT JOIN关联时，若同一物料存在多笔账单日期记录，订单金额会随账单记录数重复出现。

以订单0306183779为例：物料503921订购数量为84，总ORDERED_SALES应为5330.64，但因有多条账单日期记录，该金额重复显示；物料503995也存在相同问题。

原查询语句：

SELECT DISTINCT
O.SALES_ORDER_ITEM_CREATE_DATE,
O.SALES_ORDER_ID,
LTRIM(O.MATERIAL_ID,'0') MATERIAL,
O.GSAR ORDERED_SALES,
O.SALES_ORDER_ITEM_QUANTITY,
B.BILLING_DOC_DATE,
B.GSAR BILLED_SALES
FROM SALES_ORDERS O
LEFT JOIN BILLING B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID
AND O.SALES_ORDER_ID = '0306183779'

期望结果：每个物料的ORDERED_SALES仅显示一次，BILLED_SALES的总和与ORDERED_SALES一致。

解决方案1：先聚合账单表再关联（仅保留账单金额总和） #

如果不需要保留每条账单日期，仅需账单金额总和，可先对BILLING表按订单、物料、行项聚合求和，再与订单表关联：

SELECT
O.SALES_ORDER_ITEM_CREATE_DATE,
O.SALES_ORDER_ID,
LTRIM(O.MATERIAL_ID,'0') MATERIAL,
O.GSAR ORDERED_SALES,
O.SALES_ORDER_ITEM_QUANTITY,
B.TOTAL_BILLED_SALES
FROM SALES_ORDERS O
LEFT JOIN (
    SELECT 
        SALES_DOC_ID,
        MATERIAL_ID,
        SALES_DOC_ITEM_ID,
        SUM(GSAR) AS TOTAL_BILLED_SALES
    FROM BILLING
    GROUP BY SALES_DOC_ID, MATERIAL_ID, SALES_DOC_ITEM_ID
) B ON O.SALES_ORDER_ID = B.SALES_DOC_ID 
    AND O.MATERIAL_ID = B.MATERIAL_ID 
    AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID
WHERE O.SALES_ORDER_ID = '0306183779'

解决方案2：保留账单日期，避免订单金额重复显示 #

如果需要保留每条账单日期，但不想让订单金额重复，可通过窗口函数标记唯一行，仅在第一行显示订单金额：

SELECT
O.SALES_ORDER_ITEM_CREATE_DATE,
O.SALES_ORDER_ID,
LTRIM(O.MATERIAL_ID,'0') MATERIAL,
CASE WHEN ROW_NUMBER() OVER(PARTITION BY O.SALES_ORDER_ID, O.MATERIAL_ID, O.SALES_ORDER_ITEM_ID ORDER BY B.BILLING_DOC_DATE) = 1 THEN O.GSAR ELSE NULL END AS ORDERED_SALES,
O.SALES_ORDER_ITEM_QUANTITY,
B.BILLING_DOC_DATE,
B.GSAR BILLED_SALES
FROM SALES_ORDERS O
LEFT JOIN BILLING B ON O.SALES_ORDER_ID = B.SALES_DOC_ID 
    AND O.MATERIAL_ID = B.MATERIAL_ID 
    AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID
WHERE O.SALES_ORDER_ID = '0306183779'

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