如何用Hive SQL从单表统计Stage_1=A且无Stage_2=H的唯一ID数

给定如下数据表：

需要统计满足存在Stage_1 = A但不存在Stage_2 = H的唯一ID数量，示例中符合条件的是ID=3和ID=4，预期结果为2。要求仅从单表取数，不能使用外连接，用Hive SQL实现。

方法一：分组聚合+条件过滤 #

通过分组聚合判断每个ID的特征，再过滤出符合条件的ID：

SELECT COUNT(DISTINCT ID) AS qualified_id_count
FROM (
    SELECT ID,
           -- 标记该ID是否有Stage_1=A的记录
           MAX(CASE WHEN Stage_1 = 'A' THEN 1 ELSE 0 END) AS has_stage1_A,
           -- 标记该ID是否有Stage_2=H的记录
           MAX(CASE WHEN Stage_2 = 'H' THEN 1 ELSE 0 END) AS has_stage2_H
    FROM your_table_name
    GROUP BY ID
) t
WHERE has_stage1_A = 1 AND has_stage2_H = 0;

逻辑说明： #

1. 内层子查询按ID分组，对每个ID生成两个标记值：只要该ID下有对应条件的记录，标记值就为1，否则为0；
2. 外层筛选出「有Stage_1=A」且「无Stage_2=H」的ID，统计其数量。

方法二：子查询差集筛选 #

先圈出所有符合「有Stage_1=A」的ID范围，再排除掉「有Stage_2=H」的ID：

SELECT COUNT(DISTINCT ID) AS qualified_id_count
FROM your_table_name
WHERE ID IN (SELECT DISTINCT ID FROM your_table_name WHERE Stage_1 = 'A')
  AND ID NOT IN (SELECT DISTINCT ID FROM your_table_name WHERE Stage_2 = 'H');

逻辑说明： #

1. 第一个子查询获取所有包含Stage_1=A的ID集合；
2. 第二个子查询获取所有包含Stage_2=H的ID集合；
3. 外层取两个集合的差集，统计剩余ID的数量。

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