Impala如何查询同时满足多状态条件的ID并返回指定状态日期

假设你的表名为impala_status_table，可替换为实际表名。

方案1：聚合预筛选+关联查询（推荐，性能更优） #

先通过分组聚合筛选出同时满足两个状态条件的ID，再关联原表取出对应X状态的日期：

WITH qualified_ids AS (
    SELECT ID
    FROM impala_status_table
    GROUP BY ID
    HAVING SUM(CASE WHEN STATUS IN ('a','b','c') THEN 1 ELSE 0 END) > 0
       AND SUM(CASE WHEN STATUS = 'x' THEN 1 ELSE 0 END) > 0
)
SELECT 
    t.ID,
    t.Date AS x_status_date
FROM impala_status_table t
JOIN qualified_ids q ON t.ID = q.ID
WHERE t.STATUS = 'x';

方案2：双条件子查询关联（你提到的实现方式） #

分别构造满足a/b/c条件的ID子查询、携带X状态日期的子查询，通过ID关联后得到结果：

SELECT 
    x_sub.ID,
    x_sub.Date AS x_status_date
FROM (
    SELECT DISTINCT ID FROM impala_status_table WHERE STATUS IN ('a','b','c')
) abc_sub
JOIN (
    SELECT ID, Date FROM impala_status_table WHERE STATUS = 'x'
) x_sub ON abc_sub.ID = x_sub.ID;

扩展说明 #

• 如果同一个ID存在多条X状态的记录，上述两种写法会返回所有X对应的日期，如需仅返回最新的X日期，可在查询中添加MAX(Date)聚合逻辑：

  WITH qualified_ids AS (
      SELECT ID
      FROM impala_status_table
      GROUP BY ID
      HAVING SUM(CASE WHEN STATUS IN ('a','b','c') THEN 1 ELSE 0 END) > 0
         AND SUM(CASE WHEN STATUS = 'x' THEN 1 ELSE 0 END) > 0
  )
  SELECT 
      t.ID,
      MAX(t.Date) AS latest_x_status_date
  FROM impala_status_table t
  JOIN qualified_ids q ON t.ID = q.ID
  WHERE t.STATUS = 'x'
  GROUP BY t.ID;
  

• 数据量较大时优先选择方案1，仅需两次表扫描即可完成查询，性能优于多次子查询扫描的方案2。
• 如果实际数据中STATUS存储为大写格式，将查询条件里的'a','b','c','x'替换为对应大写字符串即可。

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