如何在Athena中查询不含'b'/'d'类商品的异常订单？

需求说明 #

需找出不存在item_category为'b'或'd'记录的订单：

• 订单内只要有至少一条'b'/'d'品类的记录，即为正常订单
• 完全没有此类记录的订单属于异常订单，需排查

示例异常订单 #

测试数据集 #

正确结果应仅返回order_nbr=2，因为订单1包含'b'品类的记录。

原SQL的问题 #

原语句：

SELECT order_nbr FROM table
where item_category not in ('b', 'd')
group by order_nbr

该语句错误的原因是：它仅筛选出品类非'b'/'d'的记录再分组，但订单1中同时存在'b'和'a'的记录，其中'a'的记录会被筛选出来，导致订单1仍被返回，不符合需求。

正确SQL实现（适配Athena，支持CTE） #

方法1：基于CTE分离正常/异常订单 #

对应你提供的伪代码逻辑，先收集所有正常订单，再排除得到异常订单：

WITH good_orders AS (
    SELECT DISTINCT order_nbr
    FROM your_table_name
    WHERE item_category IN ('b', 'd')
)
SELECT DISTINCT order_nbr
FROM your_table_name
WHERE order_nbr NOT IN (SELECT order_nbr FROM good_orders)

方法2：聚合函数直接判断 #

更简洁的方式，通过统计每个订单中'b'/'d'品类的记录数，判断是否为异常订单：

SELECT order_nbr
FROM your_table_name
GROUP BY order_nbr
HAVING COUNT(CASE WHEN item_category IN ('b', 'd') THEN 1 END) = 0

COUNT(CASE...)会统计订单内符合条件的记录数，等于0则说明该订单无'b'/'d'品类记录，属于异常订单。

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