I have a table like this, and there are three cases,
## case a
| rec_no | read_time | id
+--------+---------------------+----
| 45139 | 2023-02-07 17:00:00 | a
| 45140 | 2023-02-07 17:15:00 | a
| 45141 | 2023-02-07 17:30:00 | a
| 45142 | 2023-02-07 18:15:00 | a
| 45143 | 2023-02-07 18:30:00 | a
| 45144 | 2023-02-07 18:45:00 | a
## case b
| rec_no | read_time | id
+--------+---------------------+----
| 21735 | 2023-02-01 19:15:00 | b
| 21736 | 2023-02-01 19:30:00 | b
| 21742 | 2023-02-01 21:00:00 | b
| 21743 | 2023-02-01 21:15:00 | b
| 21744 | 2023-02-01 21:30:00 | b
| 21745 | 2023-02-01 21:45:00 | b
## case c
| rec_no | read_time | id
+--------+---------------------+----
| 12345 | 2023-02-02 12:15:00 | c
| 12346 | 2023-02-02 12:30:00 | c
| 12347 | 2023-02-02 12:45:00 | c
| 12348 | 2023-02-02 13:15:00 | c
| 12352 | 2023-02-02 14:00:00 | c
| 12353 | 2023-02-02 14:15:00 | c
I'd like to find out the missing readtime field when the rec is not continuous.
read_time is '15 min' interval
in different 'id', rec_no are independent
I'd like something like this,
## case a
## nothing because rec_no is continous
| read_time | id
+---------------------+----
## case b
## get six rows
| read_time | id
+--------+-----------------
| 2023-02-01 19:45:00 | b
| 2023-02-01 20:00:00 | b
| 2023-02-01 20:15:00 | b
| 2023-02-01 20:30:00 | b
| 2023-02-01 20:45:00 | b
| 2023-02-01 21:00:00 | b
## case c
## get two rows (13:00:00 is missing but rec_no is continous)
| read_time | id
+--------+-----------------
| 2023-02-02 13:30:00 | c
| 2023-02-02 13:45:00 | c
Is there a way to do this ? The output format is not too important as long as I can get the result correctly.
SELECT
rec_no,
id,
gs
FROM (
SELECT
*,
lead(rec_no) OVER (PARTITION BY id ORDER BY rec_no) - rec_no > 1 AS is_gap, -- 1
lead(read_time) OVER (PARTITION BY id ORDER BY rec_no) as next_read_time
FROM mytable
)s, generate_series( -- 3
read_time + interval '15 minutes', -- 4
next_read_time - interval '15 minutes',
interval '15 minutes'
) as gs
WHERE is_gap -- 2
lead() window function to move the next rec_no value and the next read_time value to the current row. With this you can check if the difference between the current and next rec_no values are greater than 1.