编写SQL查询实现基于SAUCE列的STATE条件筛选需求

原始数据表 t1 #

STATE   SAUCE     type    VALUE
Normal  Yum Yum   bowl    4.5
Normal  Yum Yum   cup     4.0
Normal  Indomie   cup     3.5
Redo1   Indomie   pack1   15.0
Redo1   Indomie   pack2   5.0
Redo2   Mayo      pack3   5.0

筛选规则 #

• 若SAUCE值同时存在STATE=Normal和STATE=Redo类的行，仅保留该SAUCE下所有STATE=Redo类的行，删除STATE=Normal的行；
• 若SAUCE值仅存在STATE=Normal的行，则保留所有该行数据。

预期输出 #

STATE   SAUCE     type    VALUE
Normal  Yum Yum   bowl    4.5
Normal  Yum Yum   cup     4.0
Redo1   Indomie   pack1   15.0
Redo1   Indomie   pack2   5.0
Redo2   Mayo      pack3   5.0

原查询问题 #

你尝试的查询使用ROW_NUMBER()窗口函数，仅保留了每个SAUCE分组的第一行，导致丢失了多行符合要求的数据，无法满足需求。

正确SQL查询 #

WITH t1 AS (
  SELECT 'Normal' AS STATE, 'Yum Yum' AS SAUCE, 'bowl'  AS type, 4.5 AS VALUE UNION ALL
  SELECT 'Normal', 'Yum Yum', 'cup', 4.0 UNION ALL
  SELECT 'Normal', 'Indomie', 'cup', 3.5 UNION ALL
  SELECT 'Redo1', 'Indomie', 'pack1', 15.0 UNION ALL
  SELECT 'Redo1', 'Indomie', 'pack2', 5.0 UNION ALL
  SELECT 'Redo2', 'Mayo', 'pack3', 5.0
),
sauce_state_flag AS (
  SELECT 
    *,
    -- 标记当前SAUCE是否存在Redo类状态的记录
    MAX(CASE WHEN STATE LIKE 'Redo%' THEN 1 ELSE 0 END) OVER(PARTITION BY SAUCE) AS has_redo
  FROM t1
)
SELECT STATE, SAUCE, type, VALUE
FROM sauce_state_flag
WHERE 
  -- 存在Redo记录时，仅保留非Normal的行
  (has_redo = 1 AND STATE != 'Normal')
  -- 无Redo记录时，保留所有Normal行
  OR (has_redo = 0 AND STATE = 'Normal');

逻辑说明 #

1. 首先通过窗口函数MAX(CASE...) OVER(PARTITION BY SAUCE)，给每个SAUCE分组标记是否存在Redo开头的状态记录；
2. 根据标记has_redo进行筛选：
   • 当has_redo=1时，只保留STATE不为Normal的行；
   • 当has_redo=0时，保留所有STATE=Normal的行。

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