sql - 缺口和孤岛，全师版

Question

所以我试图在一组数字上实现间隙和孤岛问题，这是一个例子:

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

那是一个累加和，所以数字不能减少。我需要做的是按照这些规则将各个记录分组:

1. 一组中的第一个(也是最小的)数字是“前导”数字
2. 只有当一个数字不超过某个数字(例如本例中的 7)时，它才能与其前导数字在同一组中
3. 第一个超过leading number + 7的数字是下一组的前导数字

因此对于上面显示的示例，组将是:0 0 3 4 5 6 6 7 78 11 1218 22

这几乎就像将一个数字除以差距，并以这种方式得到一个组号，但由于一组中最后一个数字和下一个组中的前导数字之间的差距可以是任何正数，这会得到更多随着序列的增长，错误会越来越多。我尝试了 case when sum(...) over(...) > 7 then sum(...) else 0 end 但它影响了不在第一组中的每个数字，所以我我不确定如何处理这个了。任何帮助，将不胜感激!如果这很重要，该表在 Snowflake 中

Answer 1

雪花支持MATCH_RECOGNIZE它旨在查找一系列行中的模式:

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

输出:

---

要使用的替代方法是递归 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

相关:https://stackoverflow.com/a/53994970/5070879和 https://stackoverflow.com/a/52936314/5070879