I have a table of the following structure, where all columns except the last are non-null:
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
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:
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.