Roni Castro - 1 year ago 52
SQL Question

# Split intervals of numbers into groups

I have the sequence of numbers below and I want to make a new group of numbers every time the following number has a difference of more than one unit from the previous value, i.e. breaking the continuous interval

Table T

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

Groups to be formed of continuous interval:

``````min max
1   3
5   7
15  18
``````

I am trying to number the rows using dense_rank() function, like the example below, then i will be able to group by rankNumber and get the MIN(value) and MAX(value), but i am not finding a pattern to use in the PARTITION BY clause of this function

``````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
``````

Code to create table:

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

Current output using the query above:

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

You need to think out someway to turn the sequences into the corresponding groups. I've just learnt this trick from another user right in here. By using the `ROW_NUMBER` which runs through all the records, you can calculate the group key by subtracting the `value` on the same record from that row number. If the `values` are consecutive, there would be no change in the subtraction result (hence the same group key produced). Otherwise the group key will be jumped to the next (lesser) value. Each time of jumping, the group key will be lesser.
``````select min(value) min, max(value) max