使用Spark SQL识别含单条异常记录的分组订单

需求明确：找出订单分组中恰好只有1条qty为0的记录，并输出该订单的所有明细记录，而非仅异常那条。

正确实现方案 #

方法1：使用窗口函数（推荐，更高效）

窗口函数可直接在原数据上计算每个订单的0值记录数，无需额外关联：

SELECT order_number, item_number, qty
FROM (
    SELECT 
        order_number, 
        item_number, 
        qty,
        -- 统计当前订单中qty=0的记录总数
        COUNT(CASE WHEN qty = 0 THEN 1 END) OVER (PARTITION BY order_number) AS zero_qty_count
    FROM your_table_name
) t
WHERE zero_qty_count = 1;

• 内层子查询通过PARTITION BY order_number按订单分组，用CASE WHEN标记qty=0的记录，再用COUNT统计每个组内的0值数量
• 外层筛选出zero_qty_count=1的所有记录，即可得到符合要求的订单全部明细

方法2：子查询+关联

先统计符合条件的订单号，再关联原表获取明细：

SELECT t1.order_number, t1.item_number, t1.qty
FROM your_table_name t1
JOIN (
    SELECT order_number
    FROM your_table_name
    GROUP BY order_number
    -- 筛选出恰好有1条qty=0的订单
    HAVING SUM(CASE WHEN qty = 0 THEN 1 ELSE 0 END) = 1
) t2 ON t1.order_number = t2.order_number;

• 子查询通过GROUP BY order_number分组，用SUM(CASE WHEN...)统计每个订单的0值记录数，筛选出等于1的订单号
• 原表与子查询结果关联，得到这些订单的所有明细

你之前方法的问题 #

你用GROUP BY order_number搭配HAVING COUNT(DISTINCT order_number) >1完全无效：因为按订单号分组后，每个组的order_number唯一，COUNT(DISTINCT order_number)永远等于1，这个条件永远不成立，自然得不到结果。

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