Contents
What is the problem of gaps and islands?
In this last part of the tutorial, we’ll look into one final use case that can be solved with window functions: gaps and islands. The gaps and islands problem is about finding holes in sequences (the gaps) or about finding ranges of consecutive values (the islands).
How to calculate gaps and islands in SQL?
There are many ways to calculate gaps and islands using a variety of window functions, subqueries, and CTE structures. Our goal in this article is to keep the T-SQL as simple as possible and avoid obfuscated or overly long code. Consider the following alteration to our data set above:
How to find the start of an island?
When integer_id increases by more than rownum, that provides the start of an island. The prior row to the missing values is the end of the previous island. Add 1 to the end of the previous island to obtain the start of the gap. Subtract 1 from the start of the next island to obtain the end of the gap.
How are islands found in a data set?
Islands can be found within any data set. The key to doing this is establishing rules as to how data relates and is significant. These rules establish two key parameters for analysis: Proximity allows us to correlate data to itself, resulting in groups of data.
When to use row number or dense rank?
It’s very much just the effect of the ordering of the duplicates that throws off the difference: the relationship of the global incremeneting value to the incrementing value over the partition. However, when you have an unique id column, or a series of columns that define uniqueness, by all means use row_number () instead of dense_rank ().
What are the gaps in a data set?
The gaps (all comprised of single letters) are as follows: A data set with N islands of data must have N-1 gaps in between them. We will typically ignore the edges of the data set and not consider them gaps, even if there are missing values.