如何用SQL检测15分钟间隔时间序列中的缺失数据点？

要找出时间序列中缺失的15分钟数据点，核心思路是生成该范围内完整的15分钟间隔时间序列，再与现有数据对比，筛选出未匹配的时间点。以下是不同场景下的实现方案：

方法一：递归CTE生成连续时间（适用于PostgreSQL、MySQL 8.0+、SQL Server等支持递归的数据库） #

通用逻辑示例（PostgreSQL） #

WITH time_ranges AS (
    -- 先获取每个计数点的时间起止范围
    SELECT 
        counting_point,
        MIN(time) AS min_time,
        MAX(time) AS max_time
    FROM your_table
    GROUP BY counting_point
),
continuous_times AS (
    -- 递归生成每个计数点的所有15分钟间隔时间点
    SELECT 
        counting_point,
        min_time AS current_time
    FROM time_ranges
    UNION ALL
    SELECT 
        ct.counting_point,
        ct.current_time + INTERVAL '15 minutes' AS current_time
    FROM continuous_times ct
    JOIN time_ranges tr ON ct.counting_point = tr.counting_point
    WHERE ct.current_time + INTERVAL '15 minutes' <= tr.max_time
)
-- 左连接原表，找出无匹配的缺失时间点
SELECT 
    ct.counting_point,
    ct.current_time AS missing_time
FROM continuous_times ct
LEFT JOIN your_table t 
    ON ct.counting_point = t.counting_point 
    AND ct.current_time = t.time
WHERE t.time IS NULL
ORDER BY ct.counting_point, ct.current_time;

MySQL 适配版本 #

将时间间隔的写法改为DATE_ADD函数：

WITH time_ranges AS (
    SELECT 
        counting_point,
        MIN(time) AS min_time,
        MAX(time) AS max_time
    FROM your_table
    GROUP BY counting_point
),
continuous_times AS (
    SELECT 
        counting_point,
        min_time AS current_time
    FROM time_ranges
    UNION ALL
    SELECT 
        ct.counting_point,
        DATE_ADD(ct.current_time, INTERVAL 15 MINUTE) AS current_time
    FROM continuous_times ct
    JOIN time_ranges tr ON ct.counting_point = tr.counting_point
    WHERE DATE_ADD(ct.current_time, INTERVAL 15 MINUTE) <= tr.max_time
)
SELECT 
    ct.counting_point,
    ct.current_time AS missing_time
FROM continuous_times ct
LEFT JOIN your_table t 
    ON ct.counting_point = t.counting_point 
    AND ct.current_time = t.time
WHERE t.time IS NULL
ORDER BY ct.counting_point, ct.current_time;

方法二：数字表生成连续时间（适用于不支持递归CTE的老版本数据库） #

如果数据库不支持递归，可以通过数字表生成连续的时间偏移量，再拼接成完整时间序列：

临时生成数字表示例（MySQL） #

WITH numbers AS (
    -- 生成足够多的连续数字，覆盖最大时间范围需要的15分钟间隔数量
    SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 -- 按需增加
),
time_ranges AS (
    SELECT 
        counting_point,
        MIN(time) AS min_time,
        MAX(time) AS max_time
    FROM your_table
    GROUP BY counting_point
),
continuous_times AS (
    SELECT 
        tr.counting_point,
        DATE_ADD(tr.min_time, INTERVAL n.num * 15 MINUTE) AS current_time
    FROM time_ranges tr
    JOIN numbers n 
        ON DATE_ADD(tr.min_time, INTERVAL n.num * 15 MINUTE) <= tr.max_time
)
SELECT 
    ct.counting_point,
    ct.current_time AS missing_time
FROM continuous_times ct
LEFT JOIN your_table t 
    ON ct.counting_point = t.counting_point 
    AND ct.current_time = t.time
WHERE t.time IS NULL
ORDER BY ct.counting_point, ct.current_time;

说明 #

• 替换your_table为你的实际表名，time为时间字段名
• 数字表的数字数量需要足够覆盖从min_time到max_time的15分钟间隔数（比如24小时需要96个数字）

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