使用窗口函数替代不等值连接统计包含其他事件的事件数量

没问题，我来帮你把这个统计逻辑改成窗口函数实现，同时兼容PostgreSQL和Redshift，并且性能比原有的不等值连接更优。

先理清原需求逻辑 #

原SQL是要统计每个事件event_id，有多少其他事件完全被它包含——也就是满足：

• 被包含事件的begin_date晚于当前事件的begin_date
• 被包含事件的end_date早于当前事件的end_date

原SQL用左连接+分组实现，但左连接在数据量大时性能较差，窗口函数可以通过排序+一次遍历完成统计，效率更高。

窗口函数实现方案 #

SELECT
  event_id,
  -- 当没有匹配的被包含事件时，返回1（和原SQL的COUNT(*)逻辑对齐）
  CASE
    WHEN filtered_count = 0 THEN 1
    ELSE filtered_count
  END AS count
FROM (
  SELECT
    event_id,
    -- 窗口函数统计符合条件的被包含事件数量
    COUNT(*) OVER (
      -- 先按begin_date升序，begin_date相同时按end_date降序排序
      ORDER BY begin_date, end_date DESC
      -- 只统计当前行之后的所有行（避免重复统计自身和之前的行）
      ROWS BETWEEN CURRENT ROW + 1 AND UNBOUNDED FOLLOWING
    ) FILTER (
      -- 过滤出完全被当前事件包含的行
      WHERE begin_date > e.begin_date AND end_date < e.end_date
    ) AS filtered_count
  FROM events e
) sub
ORDER BY event_id ASC;

方案说明 #

1. 排序逻辑：先按begin_date升序，确保我们只统计开始时间晚于当前事件的行；begin_date相同时按end_date降序，避免误统计同开始时间但结束时间更长的事件。
2. 窗口范围：ROWS BETWEEN CURRENT ROW + 1 AND UNBOUNDED FOLLOWING限定只统计当前行之后的行，排除自身和开始时间更早的事件，减少不必要的计算。
3. FILTER子句：精准筛选出完全被当前事件包含的行，PostgreSQL和Redshift都支持窗口函数中使用FILTER。
4. CASE处理：和原SQL的LEFT JOIN + COUNT(*)逻辑对齐——当没有匹配的被包含事件时，原SQL会返回1（左连接后仅保留当前事件的行），这里用CASE把0转为1。

验证结果 #

执行这个SQL后，会得到和原SQL完全一致的结果：

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