如何从数据库多事件日期范围中高效筛选当年已使用日期

针对你需要从包含StartDate和EndDate的事件表中提取当年所有已使用日期的需求，这里提供几种适配主流数据库的高性能查询方案，兼顾大量数据、日期重叠场景的效率，也支持通过“排除未使用日期”的反向思路实现：

核心思路 #

先生成当年的完整日期序列，再通过半连接（EXISTS）判断每个日期是否被任意事件覆盖——这种方式比直接展开每个事件的日期区间（比如递归拆分每个事件的日期）性能更高，尤其是当事件数量多、重叠频繁时，半连接会在找到第一个匹配后立即停止查询，避免多余计算。

如果当年未使用的日期占比极低，也可以反向查询未使用日期再排除，但通常正向匹配的效率已经足够优秀。

1. PostgreSQL 实现 #

利用PostgreSQL原生的generate_series生成日期序列，搭配EXISTS做高效匹配：

SELECT date_trunc('day', dd)::date AS used_date
FROM generate_series(
    -- 当年第一天
    date_trunc('year', CURRENT_DATE)::date,
    -- 当年最后一天
    date_trunc('year', CURRENT_DATE)::date + INTERVAL '1 year' - INTERVAL '1 day',
    INTERVAL '1 day'
) AS dd
WHERE EXISTS (
    SELECT 1
    FROM events
    WHERE dd::date BETWEEN events.StartDate AND events.EndDate
)
ORDER BY used_date;

2. MySQL 8.0+ 实现 #

通过递归CTE生成日期序列，同样用EXISTS做半连接：

WITH RECURSIVE year_dates AS (
    -- 当年第一天
    SELECT DATE_FORMAT(CURRENT_DATE, '%Y-01-01') AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM year_dates
    -- 递归到当年最后一天
    WHERE dt < DATE_FORMAT(CURRENT_DATE, '%Y-12-31')
)
SELECT dt AS used_date
FROM year_dates
WHERE EXISTS (
    SELECT 1
    FROM events
    WHERE year_dates.dt BETWEEN events.StartDate AND events.EndDate
)
ORDER BY used_date;

3. SQL Server 实现 #

用递归CTE生成日期序列，注意设置递归上限：

WITH year_dates AS (
    -- 当年第一天
    SELECT CAST(DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM year_dates
    -- 递归到当年最后一天
    WHERE dt < CAST(DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS DATE)
)
SELECT dt AS used_date
FROM year_dates
WHERE EXISTS (
    SELECT 1
    FROM events
    WHERE year_dates.dt BETWEEN events.StartDate AND events.EndDate
)
ORDER BY used_date
OPTION (MAXRECURSION 366); -- 当年最多366天，设置递归上限避免报错

性能优化建议 #

• 给events表的日期字段建立复合索引：

  -- 适配所有数据库的索引创建语句
  CREATE INDEX idx_events_date_range ON events (StartDate, EndDate);
  

  这个索引会极大加速BETWEEN区间查询的效率。
• 避免使用JOIN后去重或IN子查询的方式，这类操作会产生大量中间数据，性能远不如EXISTS半连接。
• 如果已使用日期占比极低（比如不足10%），可以反向查询未使用日期再排除，但实际场景中正向匹配的效率已经足够稳定。

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