Roni Castro Roni Castro - 7 months ago 12
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

Answer

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.

Here is the query:

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(value)
Comments