PostgreSQL中计算指定日期范围内缺失时间段的数量

针对你需要统计指定日期范围内缺失日期区间数量的需求，可以通过CTE结合窗口函数实现，以下是具体方案：

解决SQL #

WITH date_range AS (
    -- 定义查询的起止日期，可按需修改
    SELECT '2024-12-05'::date AS start_date, '2024-12-25'::date AS end_date
),
combined_dates AS (
    -- 合并查询范围内的已有日期与起止日期
    SELECT date FROM event_dates
    WHERE date BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)
    UNION ALL
    SELECT start_date FROM date_range
    UNION ALL
    SELECT end_date FROM date_range
),
ordered_dates AS (
    -- 为每个日期获取下一个相邻日期
    SELECT 
        date,
        LEAD(date) OVER (ORDER BY date) AS next_date
    FROM combined_dates
)
-- 统计有效缺失区间的数量
SELECT COUNT(*) AS missing_interval_count
FROM ordered_dates
WHERE next_date IS NOT NULL 
  AND date < next_date;

逻辑说明 #

1. date_range：定义你要查询的起止日期，方便后续修改调整。
2. combined_dates：将数据表中在查询范围内的日期，与起止日期合并，确保首尾的区间不会被遗漏。
3. ordered_dates：使用LEAD()窗口函数，按日期排序后获取每个日期的下一个相邻日期。
4. 统计逻辑：筛选出当前日期小于下一个日期的记录（说明两个日期之间存在缺失区间），统计这些记录的数量即为缺失区间总数。

针对你给出的示例数据，执行该SQL后会返回4，与预期结果一致。

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