Oracle - 将值从行转换为范围

Question

是否有任何技术可以允许像这样的行集

WITH 
base AS
(
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL

    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL

    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL

    SELECT 21 N FROM DUAL
)
SELECT  a.N
FROM base a

产生结果

 1     3
 6     7
17    19
21    21

它实际上是行到范围操作。 我正在 Oracle Land 玩，非常感谢任何建议。

Answer 1

我觉得这可能可以改进，但它有效：

WITH base AS  (
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL
    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL
    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL
    SELECT 21 N FROM DUAL
)
, lagged AS
(
    SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
    SELECT n, row_number() OVER (ORDER BY n) groupnum
      FROM lagged
      WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
    SELECT n, (SELECT MAX(groupnum) FROM groups
                 WHERE groups.n <= base.n
              ) groupnum
      FROM base
)
SELECT groupnum, MIN(n), MAX(n)
  FROM grouped
  GROUP BY groupnum
  ORDER BY groupnum

Answer 2

另一种方式：

WITH base AS  
(
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL
    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL
    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL
    SELECT 21 N FROM DUAL
)
select min(n), max(n) from 
  (
    select n, connect_by_root n root from base
    connect by prior n = n-1
    start with n not in (select n from base b 
                         where exists (select 1 from base b1 where b1.n = b.n-1)
                        )
  ) 
  group by root
  order by root

Answer 3

还有另一种方式：

with base as (
    select  1 n from dual union all
    select  2 n from dual union all
    select  3 n from dual union all
    select  6 n from dual union all
    select  7 n from dual union all
    select 17 n from dual union all
    select 18 n from dual union all
    select 19 n from dual union all
    select 21 n from dual)
select a,b 
from (select a
            ,case when b is not null and a is not null
                  then b
                  else lead(n) over (order by n)
            end b
      from (select n
                  ,a
                  ,b
            from (select n
                        ,case n-1 when lag (n) over (order by n) then null else n end a
                        ,case n+1 when lead (n) over (order by n) then null else n end b
                  from base)
      where a is not null
         or b is not null))
where a is not null
order by a