如何在不使用自连接的情况下重写该SQL查询

原查询核心逻辑 #

原查询的作用是从user表中筛选出满足以下要求的记录：

• 自身满足EventTypeId = 0且Date在'2022-10-16'至'2022-10-30'区间内
• 同时标记该Id是否存在另一条关联记录：EventTypeId > 0且Date > '2022-10-16'
  最后通过DISTINCT消除自连接产生的重复行

方案一：使用EXISTS子查询（推荐，性能更优） #

利用EXISTS直接判断存在性，完全替代自连接，且无需DISTINCT：

SELECT 
    Id,
    app,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM user 
        WHERE Id = R.Id 
          AND EventTypeId > 0 
          AND Date > '2022-10-16'
    ) THEN 1 END AS type
FROM user R
WHERE Date BETWEEN '2022-10-16' AND '2022-10-30' 
  AND EventTypeId = 0

优势：

• EXISTS仅做存在性判断，不会返回冗余数据，避免了自连接后的重复行问题
• 结合Date作为分区字段的特性，子查询可直接扫描'2022-10-16'之后的分区，大幅减少数据扫描范围
• 若给Id和EventTypeId建立联合索引，查询效率会进一步提升

方案二：使用窗口函数 #

通过窗口函数按Id分区聚合，判断是否存在符合条件的关联记录：

SELECT
    Id,
    app,
    type
FROM (
    SELECT 
        Id,
        app,
        -- 按Id分区，判断该Id是否存在符合条件的记录
        MAX(CASE WHEN EventTypeId > 0 AND Date > '2022-10-16' THEN 1 END) OVER (PARTITION BY Id) AS type,
        EventTypeId,
        Date
    FROM user
) t
-- 筛选出原查询要求的主记录
WHERE t.EventTypeId = 0 
  AND t.Date BETWEEN '2022-10-16' AND '2022-10-30'

优势：

• 无需自连接，通过一次扫描表完成计算
• 适合需要对同Id的其他字段做聚合计算的场景

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