What is the gaps and islands problem and how do you solve it in SQL?
What is the gaps and islands problem and how do you solve it in SQL?
Gaps are missing values in a sequence (missing invoice numbers, days with no logins). Islands are the unbroken runs of consecutive values between the gaps. Window functions solve both.
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM invoices) t
WHERE next_id - id > 1;
If id is 4 and the next id is 9, the gap is 5 to 8.
Within a run of consecutive values, value - ROW_NUMBER() is constant, so it labels the island.
SELECT MIN(id) AS island_start, MAX(id) AS island_end, COUNT(*) AS size
FROM (SELECT id, id - ROW_NUMBER() OVER (ORDER BY id) AS grp FROM invoices) t
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.