SQL如何对聚合数据分组以筛选符合配送组及总销售额要求的订单

• 核心逻辑缺失：没有先按「订单+配送组」维度聚合计算每个配送组的销售额，直接按整单聚合，根本无法判断单配送组的金额是否低于35美元，这是最关键的逻辑错误。
• 写法可维护性极差：穷举所有配送类型组合写CASE判断，后续新增配送类型就需要修改所有CASE逻辑，同时大量冗余列会导致结果可读性极低，也无法直接输出符合条件的订单列表。
• 冗余语法：外层的GROUP BY完全无意义，内层子查询已经按order_no分组聚合，外层分组不会生成任何有效聚合结果，还会浪费计算资源。

正确的实现逻辑分三步：

1. 先按order_no + Shipping_Group分组，计算每个订单下每个配送组的销售额
2. 再按order_no聚合，计算整单总销售额，同时判断该订单下是否存在任何一个配送组的销售额≥35美元
3. 筛选出整单总销售额>35美元，且所有配送组销售额都低于35美元的订单，就是符合要求的结果

优化后的SQL代码如下：

WITH group_sales AS (
    -- 步骤1：计算每个订单每个配送组的销售额
    SELECT 
        order_no,
        Shipping_Group,
        SUM(pl_net_price) AS group_amount
    FROM eim-prod.EDW_VIEWS.ORDER_SUBMIT_PRODUCT_LINEITEM
    GROUP BY order_no, Shipping_Group
),
order_total AS (
    -- 步骤2：计算整单总金额，标记是否存在配送组金额≥35
    SELECT 
        order_no,
        SUM(group_amount) AS order_total_amount,
        MAX(CASE WHEN group_amount >=35 THEN 1 ELSE 0 END) AS has_group_over_35,
        -- 如果需要保留原逻辑的配送类型标识，可在这里加对应的MAX(CASE)逻辑
        MAX(CASE WHEN Shipping_Group IN ('DIGITAL_GIFT_CARD','DIGITAL','GAME_INFORMER_DIGITAL','LOYALTY') THEN 1 ELSE 0 END ) AS Digital,
        MAX(CASE WHEN Shipping_Group IN ('GAME_INFORMER_PHYSICAL','PHYSICAL') THEN 1 ELSE 0 END) AS S2H,
        MAX(CASE WHEN Shipping_Group IN ('PHYSICAL_PREORDER','DIGITAL_PREORDER','PICKUP_PREORDER') THEN 1 ELSE 0 END ) AS Preorder,
        MAX(CASE WHEN Shipping_Group = 'PICKUP' THEN 1 ELSE 0 END ) AS BOPS,
        MAX(CASE WHEN Shipping_Group = 'SDD' THEN 1 ELSE 0 END) AS SDD
    FROM group_sales
    GROUP BY order_no
)
-- 步骤3：筛选符合条件的订单
SELECT *
FROM order_total
WHERE order_total_amount > 35 
AND has_group_over_35 = 0;

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