Snowflake SQL一对多关联求和后字段重复问题问询

需要编写Snowflake SQL查询，展示订单中物料的录入（keyed）时间及开票时间。但存在单个物料对应多个开票日期的场景，导致查询结果中SALES_ORDER_ITEM_QUANTITY和KEYED_SALES字段值重复显示。以下是原查询语句、当前结果及期望结果，修正方案如下：

原查询语句 #

with sales_orders as (
    select 
        O.SALES_ORDER_ITEM_CREATE_DATE,
        O.SALES_ORDER_ID,
        O.MATERIAL_ID,
        O.SALES_ORDER_ITEM_ID,
        sum(distinct o.gsar) as keyed_sales,
        sum(distinct o.sales_order_item_quantity) as SALES_ORDER_ITEM_QTY
    from EDP.RPT_SALES.SALES_ORDERS O
    where O.FISCAL_YR_ID IN (2022,2023)
        AND O.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
        and o.sales_order_id = '0306231843'
    GROUP BY 
        O.SALES_ORDER_ITEM_CREATE_DATE,
        O.SALES_ORDER_ID,
        O.MATERIAL_ID,
        O.SALES_ORDER_ITEM_ID
)

,billing as (
    select 
        B.BILLING_DOC_DATE,
        B.SALES_DOC_ID,
        B.MATERIAL_ID,
        B.SALES_DOC_ITEM_ID,
        sum(B.BILL_QTY) as BILL_QTY,
        sum(B.GSAR) as GSAR
    from EDP.RPT_SALES.BILLING B
    where b.fiscal_yr_id IN (2022,2023)
        AND b.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
    group by 
        B.BILLING_DOC_DATE,
        B.SALES_DOC_ID,
        B.MATERIAL_ID,
        B.SALES_DOC_ITEM_ID
)

SELECT
    O.SALES_ORDER_ITEM_CREATE_DATE,
    LTRIM(O.SALES_ORDER_ID,'0') SALES_ORDER_ID,
    LTRIM(O.MATERIAL_ID,'0') MATERIAL_ID,
    O.SALES_ORDER_ITEM_ID,
    O.SALES_ORDER_ITEM_QTY,
    KEYED_SALES,
    B.BILLING_DOC_DATE,
    B.BILL_QTY,
    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

当前查询结果 #

（订单0306231843的物料128470，40件，金额$7783.60，分4次开票）

期望结果 #

修正后的查询语句 #

核心思路是通过ROW_NUMBER()窗口函数，对同一订单物料的多条开票记录进行编号，仅在第一条记录保留原数量和金额，其余记录置为0。

with sales_orders as (
    select 
        O.SALES_ORDER_ITEM_CREATE_DATE,
        O.SALES_ORDER_ID,
        O.MATERIAL_ID,
        O.SALES_ORDER_ITEM_ID,
        sum(distinct o.gsar) as keyed_sales,
        sum(distinct o.sales_order_item_quantity) as SALES_ORDER_ITEM_QTY
    from EDP.RPT_SALES.SALES_ORDERS O
    where O.FISCAL_YR_ID IN (2022,2023)
        AND O.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
        and o.sales_order_id = '0306231843'
    GROUP BY 
        O.SALES_ORDER_ITEM_CREATE_DATE,
        O.SALES_ORDER_ID,
        O.MATERIAL_ID,
        O.SALES_ORDER_ITEM_ID
)

,billing as (
    select 
        B.BILLING_DOC_DATE,
        B.SALES_DOC_ID,
        B.MATERIAL_ID,
        B.SALES_DOC_ITEM_ID,
        sum(B.BILL_QTY) as BILL_QTY,
        sum(B.GSAR) as GSAR
    from EDP.RPT_SALES.BILLING B
    where b.fiscal_yr_id IN (2022,2023)
        AND b.WW_CHANNEL IN ('RENOVATION','INSTALLATION')
    group by 
        B.BILLING_DOC_DATE,
        B.SALES_DOC_ID,
        B.MATERIAL_ID,
        B.SALES_DOC_ITEM_ID
)

,joined_data as (
    SELECT
        O.SALES_ORDER_ITEM_CREATE_DATE,
        LTRIM(O.SALES_ORDER_ID,'0') SALES_ORDER_ID,
        LTRIM(O.MATERIAL_ID,'0') MATERIAL_ID,
        O.SALES_ORDER_ITEM_ID,
        O.SALES_ORDER_ITEM_QTY,
        O.KEYED_SALES,
        B.BILLING_DOC_DATE,
        B.BILL_QTY,
        B.GSAR BILLED_SALES,
        -- 按订单物料分组，给开票记录编号
        ROW_NUMBER() OVER(PARTITION BY O.SALES_ORDER_ID, O.MATERIAL_ID, O.SALES_ORDER_ITEM_ID ORDER BY B.BILLING_DOC_DATE) as rn
    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
)

SELECT
    SALES_ORDER_ITEM_CREATE_DATE,
    SALES_ORDER_ID,
    MATERIAL_ID,
    SALES_ORDER_ITEM_ID,
    -- 仅第一条记录保留原值，其余置0
    CASE WHEN rn = 1 THEN SALES_ORDER_ITEM_QTY ELSE 0 END as SALES_ORDER_ITEM_QTY,
    CASE WHEN rn = 1 THEN KEYED_SALES ELSE 0 END as KEYED_SALES,
    BILLING_DOC_DATE,
    BILL_QTY,
    BILLED_SALES
FROM joined_data
ORDER BY BILLING_DOC_DATE DESC; -- 可根据需要调整排序规则

说明 #

• 使用ROW_NUMBER()对同一SALES_ORDER_ID、MATERIAL_ID、SALES_ORDER_ITEM_ID组合下的开票记录进行编号，排序规则可根据实际需求调整（比如按开票日期或开票金额）。
• 通过CASE语句，仅在编号为1的记录中保留原SALES_ORDER_ITEM_QTY和KEYED_SALES值，其余记录设为0，避免重复显示。

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