PySpark查询需求：按PACK_IND(Y/N)计算IMPLODED/EXPLODED_ALLOC_QTY

修改思路 #

通过CASE WHEN条件分支分别处理两种场景：

• 保留PACK_IND='Y'时原有的IMPLODED_ALLOC_QTY和EXPLODED_ALLOC_QTY计算逻辑
• 当PACK_IND='N'时，两个字段统一取SUM(a.ORDER_QTY)的结果

修改后的SQL示例 #

SELECT
    a.ITEM_ID,
    a.PACK_IND,
    SUM(a.ORDER_QTY) AS ORDER_QTY_SUM,
    -- 计算IMPLODED_ALLOC_QTY
    CASE
        WHEN a.PACK_IND = 'Y' THEN
            -- 替换为你原有的PACK_IND='Y'时的计算逻辑
            SUM(b.ALLOC_QTY)
        ELSE
            SUM(a.ORDER_QTY)
    END AS IMPLODED_ALLOC_QTY,
    -- 计算EXPLODED_ALLOC_QTY
    CASE
        WHEN a.PACK_IND = 'Y' THEN
            -- 替换为你原有的PACK_IND='Y'时的计算逻辑
            SUM(b.ALLOC_QTY * b.PACK_QTY)
        ELSE
            SUM(a.ORDER_QTY)
    END AS EXPLODED_ALLOC_QTY
FROM Inventory_Table a
LEFT JOIN Item_Pack_Table b ON b.PACK_ITEM_ID = a.ITEM_ID
GROUP BY a.ITEM_ID, a.PACK_IND;

关键说明 #

• 若原代码中PACK_IND='Y'的计算逻辑和示例不同，直接替换CASE WHEN分支内的对应语句即可
• 确保GROUP BY子句包含所有非聚合字段，避免分组逻辑错误

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