I am looking for ideas on how to group numbers into low and high ranges in Oracle SQL. I looking to to avoid cursors...any ideas welcome

Example input

Which would result in the following grouping into ranges

CodePudding user response：

This is a Gaps & Islands problem. You can use the traditional solution.

For example:

select max(id) as id, min(low) as low, max(high) as high
from (
  select x.*, sum(i) over(order by id, low) as g
  from (
    select t.*,
      case when low = lag(high) over(partition by id order by low) 
            and id = lag(id) over(partition by id order by low) 
           then 0 else 1 end as i
    from t
  ) x 
) y
group by g

Result:

 ID  LOW  HIGH 
 --- ---- ---- 
 A   0    5    
 A   9    15   
 B   0    4    
 B   7    9    
 B   11   18   

See running example at db<>fiddle.

CodePudding user response：

From Oracle 12, you should use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER BY low, high
  MEASURES
    FIRST(low) AS low,
    MAX(high) AS high
  PATTERN (overlapping* last_row)
  DEFINE
    overlapping AS NEXT(low) <= MAX(high)
)

Which, for the sample data:

CREATE TABLE table_name (id, low, high) AS
SELECT 'A',  0,  2 FROM DUAL UNION ALL
SELECT 'A',  2,  3 FROM DUAL UNION ALL
SELECT 'A',  3,  5 FROM DUAL UNION ALL
SELECT 'A',  9, 11 FROM DUAL UNION ALL
SELECT 'A', 11, 13 FROM DUAL UNION ALL
SELECT 'A', 13, 15 FROM DUAL UNION ALL
SELECT 'B',  0,  1 FROM DUAL UNION ALL
SELECT 'B',  1,  4 FROM DUAL UNION ALL
SELECT 'B',  7,  9 FROM DUAL UNION ALL
SELECT 'B', 11, 12 FROM DUAL UNION ALL
SELECT 'B', 12, 17 FROM DUAL UNION ALL
SELECT 'B', 17, 18 FROM DUAL UNION ALL
SELECT 'C',  0, 10 FROM DUAL UNION ALL
SELECT 'C',  1,  3 FROM DUAL UNION ALL
SELECT 'C',  5,  8 FROM DUAL UNION ALL
SELECT 'C',  9, 15 FROM DUAL UNION ALL
SELECT 'C', 10, 14 FROM DUAL UNION ALL
SELECT 'C', 11, 13 FROM DUAL;

Outputs:

fiddle