Sample Header Ad - 728x90

SELECT / DELETE rows where consecutive null value count is greater than N

1 vote
1 answer
353 views
I have a table of the following structure, where all columns except the last are non-null: | ID | receiver | send_time | recv_time | | -- | -------- | --------- | --------- | | 1 | A | 00:00:00 | 00:00:01 | | 2 | A | 00:00:01 | NULL | | 3 | A | 00:00:02 | NULL | | 4 | A | 00:00:03 | NULL | | 5 | A | 00:00:04 | 00:00:05 | | 6 | B | 00:00:00 | 00:00:01 | | 7 | B | 00:00:01 | NULL | | 8 | B | 00:00:02 | 00:00:03 | | 9 | A | 00:00:05 | NULL | | 10 | B | 00:00:03 | NULL | | 11 | A | 00:00:06 | 00:00:07 | I want to select (and ultimately delete, but I think selecting would be the first step) all rows, where the recv_time column is NULL for *n or more* consecutive rows, partitioned by the receiver column and ordered by the send_time column. So from the above example, if *n=2*, I want to select/delete the rows with ID 2, 3 and 4, but not rows 7, 9, or 10. I think a count with partition is needed, but I can't figure out how to "reset" the count of null values as soon as a non-null row is encountered. This is what I've got so far:
SELECT 
    id, receiver, send_time, recv_time, 
    COUNT(CASE WHEN recv_time IS NULL THEN 1 END) OVER (PARTITION BY receiver ORDER BY send_time)
FROM test 
ORDER BY id
which gives me: | ID | receiver | send_time | recv_time | count | | -- | -------- | --------- | --------- | ----- | | 1 | A | 00:00:00 | 00:00:01 | 0 | | 2 | A | 00:00:01 | NULL | 1 | | 3 | A | 00:00:02 | NULL | 2 | | 4 | A | 00:00:03 | NULL | 3 | | 5 | A | 00:00:04 | 00:00:05 | 3 | | 6 | B | 00:00:00 | 00:00:01 | 0 | | 7 | B | 00:00:01 | NULL | 1 | | 8 | B | 00:00:02 | 00:00:03 | 1 | | 9 | A | 00:00:05 | NULL | 4 | | 10 | B | 00:00:03 | NULL | 2 | | 11 | A | 00:00:06 | 00:00:07 | 4 | But I want the count to reset at rows 5, 8, 11. I'm also not sure how I would proceed after that, because I want to capture *all* rows in the "group" of consecutive NULL rows, but obviously the count for row 2 in this example is smaller than *n*.
Asked by Edgxxar (13 rep)
Sep 16, 2023, 01:54 PM
Last activity: Sep 20, 2023, 12:23 AM