如何用SQL筛选无时间重叠记录及重叠区间中position更小的记录

核心逻辑 #

同key维度下判断记录时间是否重叠，仅过滤掉与更小position记录存在时间重叠的条目，其余记录全部保留。
时间重叠判定规则：两条记录满足 A.valid_from < B.valid_to AND B.valid_from < A.valid_to 即判定为重叠。

通用实现SQL（兼容所有支持子查询的主流数据库） #

SELECT `key`, valid_from, valid_to, position
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM your_table t2 
    WHERE t2.key = t1.key 
      AND t2.position < t1.position 
      AND t1.valid_from < t2.valid_to 
      AND t2.valid_from < t1.valid_to
);

逻辑说明 #

1. 用NOT EXISTS子句判断当前记录是否存在同key下、position更小且时间重叠的记录
2. 若存在则当前记录被过滤，若不存在则保留
3. 临界时间相邻的记录（如上例中b的2017-2021和2021-9999）不会被判定为重叠，会全部保留，完全匹配预期输出。

低版本数据库适配 #

如果数据库不支持CTE语法，以上写法已经可以直接运行，无需额外调整。

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