Amazon Athena SQL查询：获取满足特定状态条件的ID全量行

问题分析 #

你原SQL的核心问题在于第二个条件逻辑错误：id IN (SELECT DISTINCT id FROM order_table WHERE status <> 'order_shipped')会把只要有任意一条非order_shipped记录的ID都纳入，但我们需要的是该ID的所有记录里完全不存在order_shipped状态的ID，比如ID1包含order_shipped但也有其他状态，会被原逻辑错误选中。

方案一：NOT EXISTS子查询（直观易读） #

先筛选存在order_packed的ID，再排除存在order_shipped的ID：

SELECT
    id,
    status
FROM order_table ot
WHERE
    EXISTS (SELECT 1 FROM order_table ot2 WHERE ot2.id = ot.id AND ot2.status = 'order_packed')
    AND NOT EXISTS (SELECT 1 FROM order_table ot3 WHERE ot3.id = ot.id AND ot3.status = 'order_shipped')

方案二：分组过滤+关联（适合先锁定目标ID） #

通过分组计算每个ID的状态分布，筛选出符合条件的ID后，再关联原表获取全量行：

WITH valid_ids AS (
    SELECT id
    FROM order_table
    GROUP BY id
    HAVING 
        SUM(CASE WHEN status = 'order_packed' THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN status = 'order_shipped' THEN 1 ELSE 0 END) = 0
)
SELECT
    ot.id,
    ot.status
FROM order_table ot
JOIN valid_ids vi ON ot.id = vi.id

方案三：窗口函数（单表扫描，性能更优） #

用窗口函数给每个ID标记是否包含目标状态，再过滤出符合条件的行：

WITH order_with_flags AS (
    SELECT
        id,
        status,
        MAX(CASE WHEN status = 'order_packed' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS has_packed,
        MAX(CASE WHEN status = 'order_shipped' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS has_shipped
    FROM order_table
)
SELECT
    id,
    status
FROM order_with_flags
WHERE has_packed = 1 AND has_shipped = 0

验证结果 #

以上三种方案均能输出你期望的结果：

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