SQL查询指定日期范围内对应ID编号的最优实现方法

核心匹配逻辑 #

首先明确规则的底层判断逻辑：只要ID的生效时间段和查询的24小时窗口存在重叠，就返回对应ID，拆解如下：

• 每日凌晨4点执行时，查询窗口固定为 [前一天凌晨4点, 当日凌晨4点)
• ID的生效区间为 [Start Time, EndTime)，EndTime为NULL代表ID当前仍在生效
• 区间重叠判断条件：ID生效起始时间 < 窗口结束时间，且（ID失效时间 > 窗口起始时间 或 ID失效时间为NULL）

各数据库实现写法 #

MySQL版本 #

-- 预定义查询窗口
SET @window_start = DATE_SUB(CURDATE(), INTERVAL 1 DAY) + INTERVAL 4 HOUR;
SET @window_end = CURDATE() + INTERVAL 4 HOUR;

-- 执行查询
SELECT ID 
FROM 你的表名
WHERE `Start Time` < @window_end
AND (EndTime > @window_start OR EndTime IS NULL);

PostgreSQL版本 #

SELECT ID
FROM 你的表名
WHERE "Start Time" < CURRENT_DATE + INTERVAL '4 hours'
AND (EndTime > CURRENT_DATE - INTERVAL '1 day' + INTERVAL '4 hours' OR EndTime IS NULL);

结果验证 #

• 当查询窗口为2021-08-10 04:00 ~ 2021-08-11 04:00时：
  ID2111的失效时间晚于窗口起始时间，ID2112的生效时间早于窗口结束时间且仍在生效，返回2111、2112，符合要求
• 当查询窗口为2021-08-11 04:00 ~ 2021-08-12 04:00时：
  ID2111的失效时间早于窗口起始时间被过滤，仅ID2112符合条件，返回2112，符合要求

性能优化建议 #

给Start Time和EndTime字段添加联合索引，可避免全表扫描，大数据量场景下查询效率提升明显。

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