Gaps and islands, whole division edition

user_12334839 0
Viewed 116

so I'm trying to implement gaps and islands problem over a set of numbers, here's an example:

0 0 3 4 5 6 6 7 7 8 11 12 18 22

That's a cumulative sum, so the numbers can not decrease. What I need to do is to separate respective records into groups by these rules:

  1. The first (and the smallest) number in a group is a "leading" number
  2. A number can be in the same group as its' leading number only if it's larger by no more than a certain number (let's say 7 for this example)
  3. The first number to exceed leading number + 7 is a leading number for the next group

So with the example shown above, the groups would be: 0 0 3 4 5 6 6 7 7 8 11 12 18 22

It's almost like dividing a number by the gap, and getting a group number that way, but since a gap between the last number in a group and the leading number in the next group can be any positive number, this would get more and more incorrect as the sequence grows. I tried case when sum(...) over(...) > 7 then sum(...) else 0 end but it affects every single number that's not in the first group, so I'm not sure how to approach this anymore. Any help would be appreciated! In case that's important, the table is in Snowflake

snowflake-cloud-data-platform gaps-and-islands sql
1 Answers

Snowflake supports MATCH_RECOGNIZE which is designed to find patterns in series of rows:

SELECT col2,  bin_num
FROM T
MATCH_RECOGNIZE (
  ORDER BY col2
  MEASURES  MATCH_NUMBER() AS bin_num
  ALL ROWS PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN ( A+ )
  DEFINE A AS FIRST(col2) + 7 >=  A.col2
)
ORDER BY COL2;

db<>fiddle demo

Output:

enter image description here

An alternative approach to be used is recursive cte.

WITH RECURSIVE cte AS (
   SELECT *, ROW_NUMBER() OVER(ORDER BY col2) AS rn
   FROM  t
), rec AS (
  SELECT col2, rn, col2 AS first_val, 1 AS grp
  FROM cte
  WHERE rn = 1
  UNION ALL
  SELECT c.col2, c.rn, IFF(r.first_val + 7 >= c.col2, r.first_val, c.col2), 
         grp + IFF(r.first_val + 7 >= c.col2, 0, 1)
  FROM rec r
  JOIN cte c
    ON r.rn = c.rn-1
)
SELECT col2, grp
FROM rec
ORDER BY col2;

db<>fiddle demo

Related: https://stackoverflow.com/a/53994970/5070879 and https://stackoverflow.com/a/52936314/5070879

user_50708790
Related