PostgreSQL中如何检测pressure表是否存在连续4天无记录时段

核心思路 #

由于表数据量大，但记录中断通常仅1-2天，我们可以先提取所有有记录的日期（按天去重），再通过计算相邻日期的间隔，快速定位出间隔≥4天的时段——这些间隔对应的就是连续无记录的空白期。

基础查询SQL #

WITH daily_records AS (
    -- 提取所有存在记录的日期（去重）
    SELECT DISTINCT DATE(created_at) AS record_date
    FROM pressure
    ORDER BY record_date
),
date_gaps AS (
    -- 计算相邻有记录日期的间隔天数
    SELECT
        record_date AS current_date,
        LAG(record_date) OVER (ORDER BY record_date) AS previous_date,
        (record_date - LAG(record_date) OVER (ORDER BY record_date)) AS days_between
    FROM daily_records
)
-- 筛选出间隔≥4天的空白期
SELECT
    previous_date + INTERVAL '1 day' AS gap_start,
    current_date - INTERVAL '1 day' AS gap_end,
    days_between AS gap_duration_days
FROM date_gaps
WHERE days_between >= 4;

覆盖首尾空白期的完整查询 #

如果需要检查最早记录之前或最晚记录到当前日期之间的空白期，可以用以下扩展查询：

-- 检查表初始化前的空白期
SELECT
    NULL AS gap_start,
    MIN(record_date) - INTERVAL '1 day' AS gap_end,
    (MIN(record_date) - (SELECT MIN(created_at) FROM pressure)::DATE) + 1 AS gap_duration_days
FROM (SELECT DISTINCT DATE(created_at) AS record_date FROM pressure) AS daily_records
WHERE (MIN(record_date) - (SELECT MIN(created_at) FROM pressure)::DATE) >= 4

UNION ALL

-- 中间时段的空白期查询
WITH daily_records AS (
    SELECT DISTINCT DATE(created_at) AS record_date
    FROM pressure
    ORDER BY record_date
),
date_gaps AS (
    SELECT
        record_date AS current_date,
        LAG(record_date) OVER (ORDER BY record_date) AS previous_date,
        (record_date - LAG(record_date) OVER (ORDER BY record_date)) AS days_between
    FROM daily_records
)
SELECT
    previous_date + INTERVAL '1 day' AS gap_start,
    current_date - INTERVAL '1 day' AS gap_end,
    days_between AS gap_duration_days
FROM date_gaps
WHERE days_between >= 4

UNION ALL

-- 检查最新记录到当前的空白期
SELECT
    MAX(record_date) + INTERVAL '1 day' AS gap_start,
    CURRENT_DATE AS gap_end,
    (CURRENT_DATE - MAX(record_date)) AS gap_duration_days
FROM (SELECT DISTINCT DATE(created_at) AS record_date FROM pressure) AS daily_records
WHERE (CURRENT_DATE - MAX(record_date)) >= 4;

性能优化建议 #

为大幅提升查询速度，给created_at字段创建索引：

CREATE INDEX idx_pressure_created_at ON pressure(created_at);

该索引会加速DATE(created_at)的去重操作，避免全表扫描。

内容的提问来源于stack exchange，提问作者Вячеслав Предеин