SQL查询：检测areas表中同一区域的时间范围是否重叠

问题背景 #

我有一张名为areas的表，存储区域随时间变化的面积数据。面积生效的时间范围（以年份为单位）存储在VALID_FROM和VALID_UNTIL列中，同一区域的时间范围不允许重叠。需要编写一条SQL查询，检测是否存在重叠的时间范围。

示例数据表 #

注意：结束日期为包含式，例如area3的两个时间范围无间隙，不属于重叠。

期望查询结果 #

当前问题与已有代码 #

同一区域可能在表中出现n次，不知道如何对任意多行的VALID_FROM和VALID_UNTIL列进行对比。目前仅完成了筛选出出现多次的区域的SQL语句：

WITH duplicats AS (
    SELECT `NAME`
    FROM `areas`
    GROUP BY `NAME`
    HAVING COUNT(`NAME`) > 1
)
SELECT * 
FROM       `areas`
INNER JOIN `duplicats`
        ON `areas`.`NAME` = `duplicats`.`NAME`

解决方案 #

方法1：自连接对比 #

通过自连接同一表，针对相同区域，判断不同行的时间范围是否存在重叠（重叠条件：A的起始年份 < B的结束年份，且A的结束年份 > B的起始年份，同时排除同一行的无效对比）：

SELECT DISTINCT a.NAME, 'timeframes overlap!' AS REMARK
FROM areas a
JOIN areas b 
  ON a.NAME = b.NAME 
  AND a.VALID_FROM < b.VALID_UNTIL 
  AND a.VALID_UNTIL > b.VALID_FROM 
  AND a.ROWID != b.ROWID; -- 若表有主键（如ID），替换为a.ID != b.ID更稳妥

方法2：窗口函数（高效推荐） #

按区域分组、按起始时间排序后，用窗口函数获取上一行的结束时间，直接对比当前行的起始时间是否早于等于上一行的结束时间（符合包含式结束的重叠判断逻辑）：

WITH ordered_areas AS (
    SELECT 
        NAME,
        VALID_FROM,
        VALID_UNTIL,
        LAG(VALID_UNTIL) OVER (PARTITION BY NAME ORDER BY VALID_FROM) AS prev_until
    FROM areas
)
SELECT DISTINCT NAME, 'timeframes overlap!' AS REMARK
FROM ordered_areas
WHERE VALID_FROM <= prev_until;

该方法避免了全量自连接，数据量较大时性能更优。

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