Oracle SQL技术求助：如何筛选过去一年中存在日期重叠且ID_FORMULAR唯一的VISITORS表记录

咱们先梳理下你原来的SQL存在的几个核心问题：

• 用了隐式笛卡尔积连接，会生成大量重复数据，不仅结果不准确，查询性能也很差
• 日期重叠的判断逻辑写得太冗余，其实有更简洁通用的写法
• 完全没处理最后要求的「保留ID_FORMULAR唯一记录」的条件
• 重复多次写CREATED_DATE的范围条件，维护起来很麻烦

针对你的三个需求，我给你优化后的两种方案，你可以根据实际情况选择：

方案一：用EXISTS判断重叠+DISTINCT去重（简单场景） #

这个方案适合你只需要每个ID_FORMULAR出现一次，且对取哪条记录没有特殊要求的情况：

SELECT DISTINCT T1.*
FROM VISITORS T1
WHERE 
    -- 条件1：CREATED_DATE在一年前到今日（包含两端）
    T1.CREATED_DATE >= ADD_MONTHS(TRUNC(CURRENT_DATE), -12)
    AND T1.CREATED_DATE <= TRUNC(CURRENT_DATE)
    -- 条件2：存在其他ID_FORMULAR不同的记录，日期区间重叠
    AND EXISTS (
        SELECT 1
        FROM VISITORS T2
        WHERE T2.ID_FORMULAR != T1.ID_FORMULAR
          -- 通用的区间重叠判断逻辑：两个闭区间[a1,a2]和[b1,b2]重叠的条件是 a1 <= b2 且 b1 <= a2
          AND T1.FROM_DATE <= T2.TO_DATE
          AND T2.FROM_DATE <= T1.TO_DATE
    )

方案二：用窗口函数确保ID_FORMULAR唯一（指定取某条记录） #

如果需要每个ID_FORMULAR只保留特定的一条记录（比如最新创建的那条），可以用ROW_NUMBER()窗口函数，灵活性更高：

SELECT *
FROM (
    SELECT 
        T1.*,
        -- 按ID_FORMULAR分组，按CREATED_DATE倒序排，每组第一条标记为rn=1
        ROW_NUMBER() OVER (PARTITION BY T1.ID_FORMULAR ORDER BY T1.CREATED_DATE DESC) AS rn
    FROM VISITORS T1
    WHERE 
        T1.CREATED_DATE >= ADD_MONTHS(TRUNC(CURRENT_DATE), -12)
        AND T1.CREATED_DATE <= TRUNC(CURRENT_DATE)
        AND EXISTS (
            SELECT 1
            FROM VISITORS T2
            WHERE T2.ID_FORMULAR != T1.ID_FORMULAR
              AND T1.FROM_DATE <= T2.TO_DATE
              AND T2.FROM_DATE <= T1.TO_DATE
        )
)
WHERE rn = 1

关键优化点说明 #

1. 替换笛卡尔积为EXISTS：EXISTS是半连接，只要找到符合条件的记录就会停止查询，比笛卡尔积的全量匹配性能提升很多，也不会生成冗余数据
2. 简化日期重叠判断：用T1.FROM_DATE <= T2.TO_DATE AND T2.FROM_DATE <= T1.TO_DATE可以涵盖所有重叠场景（部分重叠、完全包含、端点接触），比你原来分三个OR的写法简洁且不易出错
3. 处理ID_FORMULAR唯一：
   • DISTINCT会基于所有返回字段去重，确保每个ID_FORMULAR不会重复出现
   • 窗口函数可以更精准地控制每个ID_FORMULAR保留哪一条记录，适合有特定排序需求的场景

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