仅为数字序列 +1 选择范围(最小值、最大值)

Question

仅为数字序列选择范围（最小值、最大值） +1

有一张表：

col_number
1
2
3
4
9
10
12

有必要获取数据，以便：

min   max
1     4
9     10
12    12

Answer 1

在 Oracle 版本 12.1 或更高版本中，您可以使用如下match_recognize：

with
  my_table(col_number) as (
    select  1 from dual union all
    select  2 from dual union all
    select  3 from dual union all
    select  4 from dual union all
    select  9 from dual union all
    select 10 from dual union all
    select 12 from dual
  )
-- end of sample data (for testing only, not part of the solution)
select min_number, max_number
from   my_table
match_recognize(
  order by col_number
  measures first(col_number) as min_number, last(col_number) as max_number
  pattern  (a b*)
  define b as col_number = prev(col_number) + 1
);
MIN_NUMBER MAX_NUMBER
---------- ----------
         1          4
         9         10
        12         12

Answer 2

这被称为间隙和孤岛问题。您可以通过从值中减去行号来解决此问题以获取组键：

select min(col), max(col)
from
(
  select
    col,
    col - row_number() over (order by col) as grp
  from mytable
) 
group by grp
order by min(col);

Answer 3

在 Oracle 中，您实际上可以在没有窗口函数的情况下执行此操作：

select min(col) as "min", max(col) as "max"
from (select t.*, rownum as seqnum
      from (select t.* from t order by col) t
     ) t
group by (col - seqnum)
order by min(col)

Answer 4

没有窗口函数：

WITH 
nmin AS (
  SELECT col_number FROM numbers t WHERE NOT EXISTS 
    (SELECT col_number FROM numbers WHERE numbers.col_number = t.col_number - 1)),
nmax AS (
  SELECT col_number FROM numbers t WHERE NOT EXISTS 
    (SELECT col_number FROM numbers WHERE numbers.col_number = t.col_number + 1))
SELECT 
  nmin.col_number minnumber, 
  (SELECT MIN(nmax.col_number) FROM nmax WHERE nmax.col_number >= nmin.col_number) maxnumber
FROM nmin
ORDER BY nmin.col_number;