Hive查询优化：找出单日同时存在事件1和2的唯一ID

现有一张Hive表，包含ID、event_number（事件编号）和Date（日期）字段，同一ID可对应多条事件记录，表数据如下：

ID    event_number    Date
ABC      1           2022-08-01
ABC      2           2022-08-01
ABC      3           2022-08-01
DEF      1           2022-08-01
GHI      2           2022-08-02
DEF      3           2022-08-01

需求是找出单日同时存在事件1和事件2的唯一ID，示例中仅ABC符合要求（DEF只存在事件1和3，GHI仅在另一日期有事件2）。

我当前的实现语句如下：

select distinct ID from table where event_number=1 and date='2022-08-01' 
and ID in( Select ID from table where event_number=2 and date='2022-08-01');

请问有没有更简洁高效的实现方式？

方案1：分组聚合（更推荐） #

这种方式只需要扫描一次表，比子查询多次扫描的性能更优，逻辑也清晰：

SELECT ID
FROM your_table
WHERE Date = '2022-08-01'
  AND event_number IN (1, 2) -- 先过滤无关事件，减少后续聚合的数据量
GROUP BY ID, Date
HAVING COUNT(DISTINCT event_number) = 2; -- 确保该ID当日同时有1和2两种事件

如果能保证同一ID+Date+event_number不会出现重复记录，还可以把COUNT(DISTINCT)换成普通COUNT，性能会进一步提升：

SELECT ID
FROM your_table
WHERE Date = '2022-08-01'
  AND event_number IN (1, 2)
GROUP BY ID, Date
HAVING COUNT(*) = 2;

方案2：使用EXISTS子查询 #

相比IN子查询，EXISTS在Hive中通常更高效——因为它找到匹配的记录就会停止扫描，不需要全量返回子查询的结果集：

SELECT DISTINCT t1.ID
FROM your_table t1
WHERE t1.Date = '2022-08-01'
  AND t1.event_number = 1
  AND EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.ID = t1.ID
      AND t2.Date = t1.Date
      AND t2.event_number = 2
  );

方案3：窗口函数（适合扩展场景） #

如果后续需要检查更多事件类型（比如同时存在1、2、3），窗口函数的写法扩展性更强：

SELECT DISTINCT ID
FROM (
  SELECT ID,
         MAX(CASE WHEN event_number = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Date) has_event1,
         MAX(CASE WHEN event_number = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Date) has_event2
  FROM your_table
  WHERE Date = '2022-08-01'
) t
WHERE has_event1 = 1 AND has_event2 = 1;

方案优势说明 #

• 分组聚合避免了多次表扫描，在大数据量场景下性能提升明显；
• EXISTS减少了不必要的数据传输，Hive优化器对其处理更友好；
• 所有方案的逻辑都比原查询更清晰，后续调整需求（比如增加事件类型）也更方便。

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