将数字的区间分成组

Question

我有以下数字序列，并想在每个相邻数值之间的差大于1时创建一个新的数字组，即打破连续间隔

表T

value
1 
2
3
5
6
7
15
16
17
18

需要形成连续区间的群组：

min max
1   3
5   7
15  18

我正在尝试使用dense_rank()函数对行进行编号，就像下面的例子一样，然后我将能够按rankNumber分组并获取MIN(value)和MAX(value)，但是我没有找到在此函数的PARTITION BY子句中使用的模式。

value rankNumber
1     1
2     1
3     1
5     2
6     2
7     2
15    3
16    3
17    3
18    3

    WITH T2 AS
    (
     SELECT value, LEAD(value) OVER(ORDER BY value) as nextValue
     FROM T 
    )

    SELECT value, DENSE_RANK() 
     OVER(PARTITION BY CASE WHEN nextValue - value > 1 THEN 1 ELSE 0 END ORDER BY value)
    FROM T2

创建表的代码：

CREATE TABLE t(
    value   INT
);
INSERT INTO t VALUES
    (1), (2), (3), (5), (6), (7), (15), (16), (17), (18);

使用上述查询的当前输出：

value rankNumber
1     1
2     2
3     1
5     3
6     4
7     2
15    5
16    6
17    7
18    8

- Roni Castro

---

你可能会对这个问题的答案感兴趣：如何使用row_number()和dense_rank()解决“间隙和岛屿”问题？ - Evan Carroll

Answer 1

您需要想出一种方法将序列转化为相应的组。我刚刚从另一个用户那里学到了这个技巧。通过使用贯穿所有记录的ROW_NUMBER，您可以通过将同一记录上的value减去该行号来计算组键。如果values是连续的，则减法结果不会改变（因此产生相同的组键）。否则，组键将跳到下一个（较小的）值。每次跳跃时，组键都会变得更小。

以下是查询内容：

select min(value) min, max(value) max
from (select value, ROW_NUMBER() over (order by value) - value as [key] 
      from t) v
group by [key]
order by min

- King King

---

那是一个很棒的答案！ :) - Evaldas Buinauskas

@EvaldasBuinauskas 是的，这个想法非常有趣 :) 正如我在回答中提到的那样，这个想法最初来自于这里 https://dev59.com/ZloU5IYBdhLWcg3w7qIt#36927721 （如果你感兴趣的话 - 我昨天刚读了那篇答案）。 - King King

关于这个方法的解释，您可能会对使用row_number()和dense_rank()解决“间隔和岛屿”问题的答案感兴趣。 - Evan Carroll