SQL Challenge/Puzzle: How to merge nested ranges?

Question

This challenge is based on a real life use-case involving IP ranges.
The solution I came with is based on the stack trace challenge I've presented previously. Each range start is treated as a PUSH operation and each range end + 1 is treated as a POP operation.

The Challenge

We have a data set of ranges where each range has a starting point, ending point and a value.

create table ranges
(
    range_start     int         not null
   ,range_end       int         not null
   ,range_val       char(1)     not null
)
;

A range can contain another range or follow another range, but cannot be equal to another range or intersect with another range.

These are valid relations between ranges:

(1)           (2)           (3)           (4)
---------     ---------     ---------     -------- -----------
---                 ---        ---

These relations are not valid:

(5)                (6)
-------        --------       
-------              --------

Our initial ranges, when presented graphically, might look something like this (The letter represents range_val):

AAAAAAAA  BBCCCCCCC
 DDE   F   GGGGG
   H       IIII
             J

The goal is to take the initial set of ranges and create a new set under the following rule:

A contained range will override the corresponding sub-range of the the containing range.

The requested result, when presented graphically, might look something like this

ADDHAAAF  BIIJIGCCC

Requirements

The solution should be a single SQL query (sub-queries are fine).
The use of T-SQL, PL/SQL etc. is not allowed.
The use of UDF (User Defined Functions) is not allowed.

Data Sample

AAAAAAAAAAAAAAAAAAAAAAAAAAAA  BBBB    CCCCCCCCCCCCCCCCCCCCCCCCC
DDDE  FFFFFFFF    GGGGGGGGG               HHHHHHHH    IIIIIII
JJ      KKKLLL       MM NN                              OOOOO
            P                                              QQ

insert into ranges (range_start,range_end,range_val) values (1  ,28 ,'A');
insert into ranges (range_start,range_end,range_val) values (31 ,34 ,'B');
insert into ranges (range_start,range_end,range_val) values (39 ,63 ,'C');
insert into ranges (range_start,range_end,range_val) values (1  ,3  ,'D');
insert into ranges (range_start,range_end,range_val) values (4  ,4  ,'E');
insert into ranges (range_start,range_end,range_val) values (7  ,14 ,'F');
insert into ranges (range_start,range_end,range_val) values (19 ,27 ,'G');
insert into ranges (range_start,range_end,range_val) values (43 ,50 ,'H');
insert into ranges (range_start,range_end,range_val) values (55 ,61 ,'I');
insert into ranges (range_start,range_end,range_val) values (1  ,2  ,'J');
insert into ranges (range_start,range_end,range_val) values (9  ,11 ,'K');
insert into ranges (range_start,range_end,range_val) values (12 ,14 ,'L');
insert into ranges (range_start,range_end,range_val) values (22 ,23 ,'M');
insert into ranges (range_start,range_end,range_val) values (25 ,26 ,'N');
insert into ranges (range_start,range_end,range_val) values (57 ,61 ,'O');
insert into ranges (range_start,range_end,range_val) values (13 ,13 ,'P');
insert into ranges (range_start,range_end,range_val) values (60 ,61 ,'Q');

Requested Results

(Nulls are presented here as empty spaces)

JJDEAAFFKKKLPLAAAAGGGMMGNNGA  BBBB    CCCCHHHHHHHHCCCCIIOOOQQCC

range_start range_end range_val
----------- --------- ---------
1           2          J
3           3          D
4           4          E
5           6          A
7           8          F
9           11         K
12          12         L
13          13         P
14          14         L
15          18         A
19          21         G
22          23         M
24          24         G
25          26         N
27          27         G
28          28         A
29          30         
31          34         B
35          38         
39          42         C
43          50         H
51          54         C
55          56         I
57          59         O
60          61         Q
62          63         C

Optional addition final row:

All those I have tagged. Teradata, Oracle, SQL Server, PostgresSQL and Hive. We had a similar discussion over another post and as you can see ,except for vendor specific solutions, I've submitted a generic solution for all of those DB. http://stackoverflow.com/a/39941615/6336479 — David דודו Markovitz, Oct 13 '16 at 14:17

score 1 · Answer 1 · answered Oct 13 '16 at 13:21

1

Oracle solution:

with l as ( select level lvl from dual connect by level < 66 ),
     r as ( select range_start r1, range_end r2, range_val v, 
                    range_end - range_start + 1 cnt 
              from ranges ),
     t1 as (select distinct lvl, 
                   nvl(max(v) keep (dense_rank first order by cnt) 
                              over (partition by lvl), '*' ) m
              from l left join r on lvl between r1 and r2 ),
     t2 as (select lvl, m, case when lag(m) over (order by lvl) <> m then 0 else 1 end mrk 
              from t1),
     t3 as (select lvl, m, lvl - sum(mrk) over (order by lvl) grp from t2)
select min(lvl) r1, max(lvl) r2, nullif(min(m), '*') val
  from t3 group by grp order by r1

Output is as requested. My English is far from good, so it's hard to explain, but let's try:

l - number generator,
r - data from ranges with counted distance,
t1 - finds value with minimal distance for each lvl,
t2 - adds markers telling if range starts,
t3 - adds column which we will next use for grouping data.

answered Oct 13 '16 at 13:21

Ponder Stibbons

14,723
2
21
24

thank you for putting the effort and taking the challenge. Your solution does return the right results, however its basic concept of creating every dot on the whole range is clearly inefficient. If my data sample would have contained only a single range, but a wide one ,e.g. **(0,4294967295,'A')** ,that would be the end of it. I would like to encourage you to find another solution with a different approach. Thanks again :-) – David דודו Markovitz Oct 13 '16 at 19:02
A comment about **t1**: My opinion is that aggregation would be much suitable here. How about **select lvl ,nvl(max(v) keep (dense_rank first order by cnt), '*' ) m from l left join r on lvl between r1 and r2 group by lvl ;**? – David דודו Markovitz Oct 13 '16 at 19:07
Regarding **t1** and the code following it: Finding the continuous values can be done in just 2 steps (**t1** + additional step). Please take a look on this suggestion: **t1 as ( select lvl ,max(v) keep (dense_rank first order by cnt) m ,row_number () over (partition by max(v) keep (dense_rank first order by cnt) order by lvl) as rn from l left join r on lvl between r1 and r2 group by lvl ) select min(lvl) range_start,max(lvl) range_end,m range_val from t1 group by m,lvl - rn order by range_start ;** – David דודו Markovitz Oct 13 '16 at 19:11

score 1 · Answer 2 · edited May 23 '17 at 12:01

The solution is based on the stack trace challenge I've presented previously. Each range start is treated as a PUSH operation and each range end + 1 is treated as a POP operation.
Performence wise, you may notice how the 2 internal analytic functions use the same windowing, therefore being executed in a single step.

Teradata

select      new_range_start
           ,new_range_end

           ,last_value (range_val ignore nulls) over 
            (
                partition by    stack_depth
                order by        new_range_start ,range_start ,range_end desc 
                rows            unbounded preceding
            )                                                                   as new_range_val

from       (select      new_range_start
                       ,range_val
                       ,range_start
                       ,range_end

                       ,sum (case when range_val is null then -1 else 1 end) over 
                        (
                            order by    new_range_start, range_start ,range_end desc  
                            rows        unbounded preceding
                        )                                                                   as stack_depth

                       ,min (new_range_start) over
                        (
                            order by    new_range_start ,range_start ,range_end desc
                            rows        between 1 following and 1 following

                        ) - 1                                                               as new_range_end

            from        (           select range_start     ,range_start ,range_end ,range_val              from ranges
                        union all   select range_end   + 1 ,range_start ,range_end ,cast (null as char(1)) from ranges
                        )
                        r (new_range_start,range_start,range_end,range_val)
            )
            r

qualify     new_range_end >= new_range_start

order by    new_range_start
;

Oracle

select      new_range_start
           ,new_range_end
           ,new_range_val                       

from       (select      new_range_start
                       ,new_range_end

                       ,last_value (range_val ignore nulls) over 
                        (
                            partition by    stack_depth
                            order by        new_range_start ,range_start ,range_end desc 
                            rows            unbounded preceding
                        )                                                                   as new_range_val


            from       (select      new_range_start
                                   ,range_start
                                   ,range_end
                                   ,range_val

                                   ,sum (case when range_val is null then -1 else 1 end) over 
                                    (
                                        order by    new_range_start, range_start ,range_end desc  
                                        rows        unbounded preceding
                                    )                                                                as stack_depth

                                   ,lead (new_range_start) over
                                    (
                                        order by    new_range_start, range_start ,range_end desc 
                                    ) - 1                                                            as new_range_end

                        from        (           select range_start     as new_range_start ,range_start ,range_end ,range_val              from ranges
                                    union all   select range_end   + 1                    ,range_start ,range_end ,cast (null as char(1)) from ranges
                                    )
                                    r 
                        )
                        r
            )
            r

where       new_range_end >= new_range_start

order by    new_range_start
;

SQL Server / PostgreSQL / Hive

select      *

from       (select      new_range_start
                       ,new_range_end
                       ,min (range_val) over
                        (
                            partition by    stack_depth,new_range_val_group_id
                        )                                                       as new_range_val                       

            from       (select      new_range_start
                                   ,new_range_end
                                   ,range_val
                                   ,stack_depth

                                   ,count (range_val) over 
                                    (
                                        partition by    stack_depth
                                        order by        new_range_start ,range_start ,range_end desc 
                                        rows            unbounded preceding
                                    )                                                                   as new_range_val_group_id


                        from       (select      new_range_start
                                               ,range_start
                                               ,range_end
                                               ,range_val

                                               ,sum (case when range_val is null then -1 else 1 end) over 
                                                (
                                                    order by    new_range_start, range_start ,range_end desc  
                                                    rows        unbounded preceding
                                                )                                                                as stack_depth

                                               ,lead (new_range_start) over
                                                (
                                                    order by    new_range_start, range_start ,range_end desc 
                                                ) - 1                                                            as new_range_end

                                    from        (           select range_start     as new_range_start ,range_start ,range_end ,range_val                           from ranges
                                                union all   select range_end   + 1 as new_range_start ,range_start ,range_end ,cast (null as char(1)) as range_val from ranges
                                                )
                                                r 
                                    )
                                    r
                        )
                        r
            )
            r

where       new_range_end >= new_range_start

order by    new_range_start
;

The problem looked similar to [Packing Intervals](http://blogs.solidq.com/en/sqlserver/packing-intervals/), but I could not figure out how to get the values, rather than simple stack depth count. Very nice. — Vladimir Baranov, Oct 15 '16 at 10:38
@VladimirBaranov: The problem is actually the same as http://sqlmag.com/sql-server/packing-intervals-priorities — dnoeth, Oct 16 '16 at 21:28
@dnoeth, I took a look at the link and I see that there is no restriction on the relations between ranges, which makes the problem I've presented a private case of this problem (with a much cleaner solution) — David דודו Markovitz, Oct 16 '16 at 21:41

Michael Piankov · Answer 3 · 2016-10-15T09:08:03.793

0

Oracle solution 2

 WITH borders AS /*get all borders of interval*/ 
  (SELECT DISTINCT DECODE(is_end, 0, range_start, range_end) AS border 
                  ,is_end 
   FROM   ranges r, 
          (SELECT 0 AS is_end FROM dual UNION ALL 
           SELECT 1 AS is_end FROM dual)), 
 interv AS  /*get all intervals*/ 
  (SELECT border + is_end AS beg_int 
         ,lead(border) over(ORDER BY border, is_end ) 
           - lead(DECODE(is_end, 0, 1, 0)) over(ORDER BY border, is_end) AS end_int 
   FROM   borders 
   ORDER  BY 1) 
 SELECT i.beg_int 
       ,i.end_int 
       ,(SELECT MAX(r.range_val) keep (dense_rank FIRST ORDER BY r.range_end - r.range_start) 
       FROM ranges r 
       WHERE i.beg_int >= r.range_start AND i.end_int <= r.range_end) AS range_val   
 FROM   interv i 
 WHERE  beg_int <= end_int OR end_int IS NULL 
 ORDER  BY i.beg_int;

Add solution without self join : EDIT: fixed defect.

 WITH intervals AS 
  (SELECT DECODE(is_end, -1, range_val, NULL) AS range_val 
         ,DECODE(is_end, -1, range_start, range_end) AS border 
         ,is_end 
         ,- (SUM(is_end) over(ORDER BY DECODE(is_end, -1, range_start, range_end), is_end, (range_end - range_start) * is_end)) AS poss 
         ,(range_end - range_start) * is_end AS ord2 
   FROM   ranges r 
         ,(SELECT -1 AS is_end FROM   dual UNION ALL 
           SELECT 1  AS is_end FROM   dual)), 
 range_stack AS 
  (SELECT border + DECODE(is_end, 1, 1, 0) AS begin_int 
         ,lead(border) over(ORDER BY border, is_end, ord2) 
           + DECODE(lead(is_end) over(ORDER BY border, is_end, ord2), 1, 0, -1) AS end_int 
         ,last_value(range_val ignore NULLS) over(PARTITION BY poss ORDER BY border, is_end, ord2) AS range_val 
   FROM   intervals) 
 SELECT begin_int 
       ,end_int 
       ,range_val 
 FROM   range_stack 
 WHERE  end_int >= begin_int 
        OR end_int IS NULL;

edited Oct 15 '16 at 09:08

answered Oct 13 '16 at 20:18

Michael Piankov

1,989
1
8
19

Hi Michael :-) This is definitely in the right direction and it is well coded, however, performance wise, this **LEFT JOIN** is going to be an issue if we're dealing with large number of ranges. – David דודו Markovitz Oct 13 '16 at 22:38
Left join need for empty intervals. What the issue with left join? One of posible plans: Oracle build all intervls once,and sorted it, After it sorter renges from table and make a merge join with gaps. If there will a lot of data It will be efficient way. – Michael Piankov Oct 14 '16 at 05:20
The issue is not with the **LEFT** but with the **JOIN** which bring us to complexity of O(n^2). We can demonstrate this with this data sample: **create table ranges (range_start,range_end,range_val) as with t (n) as (select level from dual connect by level <= 10000) select -n,n,chr(ascii('A')+ mod(n-1,26)) from t;** The input is very small, only 10**K** rows, but the **JOIN** produces 100**M** rows. On my laptop the execution time is around 2 minutes. The explain plan shows the use of **MERGE JOIN**. If we will use an initial set of 100**K** rows, the join will produce 100**G** rows... – David דודו Markovitz Oct 14 '16 at 07:57
Ok I see. I can rewrite it to using subquery. But in your example first interval will be merged and compared with 2 ranges. Second with 3 nd so on and so on. It will increase to sentral interval (-1,1 ) and decrised further. I thon total count will by lower then you write. But It will be a lot of them – Michael Piankov Oct 14 '16 at 08:23
Also there seems to be a bug. You can see it using the following data sample: **create table ranges (range_start,range_end,range_val) as with t (n) as (select level from dual connect by level <= 26) select n,n,chr(ascii('A')+ mod(n-1,26)) from t;** – David דודו Markovitz Oct 14 '16 at 08:27
Execution time is more than 10 minutes for an optimal set **(n,n,val)** of 100**K** rows. **create table ranges (range_start,range_end,range_val) as with t (n) as (select level from dual connect by level <= 100000) select n,n,chr(ascii('A')+ mod(n-1,26)) from t; ;** – David דודו Markovitz Oct 14 '16 at 10:34
Way better (did you take a look on a previous post?) As you are probably aware, the same 100**K** data set is now running around **20 seconds** instead of more than **10 minutes**. You do have a bug. You can test with the data sample I've provided in the question (I've modified it a little) and take a look at range 15-18. the value should be 'A' and not 'F'. Also the code is more complex than needed. – David דודו Markovitz Oct 15 '16 at 07:47
I edit dolution another one time. Thank you. It was intresting – Michael Piankov Oct 15 '16 at 09:09
Great job. Very, very, very close. We could still gain some performance improvement. – David דודו Markovitz Oct 15 '16 at 10:12