PostgreSQL查询不连续rec_no对应的缺失read_time

核心逻辑 #

按id分组处理，通过窗口函数获取每条记录的下一条记录信息，判断rec_no是否连续。若存在间隙，根据read_time固定15分钟的间隔规律，计算出缺失时间段对应的read_time。

通用SQL实现（适用于PostgreSQL、SQL Server等） #

假设表名为your_table，代码如下：

WITH id_group AS (
    SELECT
        id,
        rec_no,
        read_time,
        LEAD(rec_no) OVER (PARTITION BY id ORDER BY rec_no) AS next_rec_no,
        LEAD(read_time) OVER (PARTITION BY id ORDER BY rec_no) AS next_read_time
    FROM your_table
),
missing_records AS (
    SELECT
        id,
        GENERATE_SERIES(rec_no + 1, next_rec_no - 1, 1) AS missing_rec_no,
        read_time + INTERVAL '15 minutes' * (GENERATE_SERIES(rec_no + 1, next_rec_no - 1, 1) - rec_no) AS missing_read_time
    FROM id_group
    WHERE next_rec_no IS NOT NULL AND next_rec_no > rec_no + 1
)
SELECT id, missing_rec_no, missing_read_time
FROM missing_records
ORDER BY id, missing_rec_no;

MySQL适配版本（无GENERATE_SERIES支持） #

用递归CTE生成缺失的rec_no序列：

WITH RECURSIVE id_group AS (
    SELECT
        id,
        rec_no,
        read_time,
        LEAD(rec_no) OVER (PARTITION BY id ORDER BY rec_no) AS next_rec_no,
        LEAD(read_time) OVER (PARTITION BY id ORDER BY rec_no) AS next_read_time
    FROM your_table
),
missing_recursive AS (
    SELECT
        id,
        rec_no + 1 AS missing_rec_no,
        read_time + INTERVAL 15 MINUTE AS missing_read_time,
        next_rec_no
    FROM id_group
    WHERE next_rec_no IS NOT NULL AND next_rec_no > rec_no + 1
    UNION ALL
    SELECT
        id,
        missing_rec_no + 1,
        missing_read_time + INTERVAL 15 MINUTE,
        next_rec_no
    FROM missing_recursive
    WHERE missing_rec_no + 1 < next_rec_no
)
SELECT id, missing_rec_no, missing_read_time
FROM missing_recursive
ORDER BY id, missing_rec_no;

覆盖三类数据场景的补充处理 #

1. 开头缺失（id的最小rec_no大于1）：
   可以额外生成从1到min_rec_no-1对应的缺失时间，以该id最早的read_time为基准倒推：

   WITH min_rec AS (
       SELECT id, MIN(rec_no) AS min_rn, MIN(read_time) AS min_rt
       FROM your_table
       GROUP BY id
   ),
   start_missing AS (
       SELECT
           id,
           GENERATE_SERIES(1, min_rn - 1, 1) AS missing_rec_no,
           min_rt - INTERVAL '15 minutes' * (min_rn - GENERATE_SERIES(1, min_rn - 1, 1)) AS missing_read_time
       FROM min_rec
       WHERE min_rn > 1
   )
   -- 合并中间缺失与开头缺失结果
   SELECT * FROM missing_records
   UNION ALL
   SELECT * FROM start_missing
   ORDER BY id, missing_rec_no;
   

2. 中间缺失：上述基础代码已完全覆盖。

3. 结尾缺失（需指定截止时间）：
   若要补充最大rec_no之后的缺失时间，先确定截止时间（比如'2024-06-01 23:59:59'），再生成对应的序列：

   WITH max_rec AS (
       SELECT id, MAX(rec_no) AS max_rn, MAX(read_time) AS max_rt
       FROM your_table
       GROUP BY id
   ),
   end_missing AS (
       SELECT
           id,
           max_rn + GENERATE_SERIES(1, FLOOR((TIMESTAMP '2024-06-01 23:59:59' - max_rt) / INTERVAL '15 minutes'), 1) AS missing_rec_no,
           max_rt + INTERVAL '15 minutes' * GENERATE_SERIES(1, FLOOR((TIMESTAMP '2024-06-01 23:59:59' - max_rt) / INTERVAL '15 minutes'), 1) AS missing_read_time
       FROM max_rec
       WHERE max_rt + INTERVAL '15 minutes' <= '2024-06-01 23:59:59'
   )
   -- 合并所有缺失结果
   SELECT * FROM missing_records
   UNION ALL
   SELECT * FROM start_missing
   UNION ALL
   SELECT * FROM end_missing
   ORDER BY id, missing_rec_no;
   

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