Oracle 使用分析函数进行时间段分组

嗨，汤姆，

我有一个像下面这样的表格:

GRP,SUBGRP,START_Y,END_Y
122,...
123,A,2010,2011
123,A,2011,2012
123,A,2012,2013
123,A,2013,2014
123,B,2014,2015
123,B,2015,2016
123,B,2016,2017
123,A,2017,2018
123,A,2018,2019
123,A,2019,2020
124,...

我想在此表中找到所有间隔的开始和结束，如下所示:

GRP,SUBGRP,MIN,MAX
122,...
123,A,2010,2014
123,B,2014,2017
123,A,2017,2020
124,...

一个简单的分组将显示整个时间段内的结果，但不显示不同间隔内的结果:

GRP,SUBGRP,MIN,MAX
122,...
123,A,2010,2020
123,B,2014,2017
124,...

我认为使用分析功能应该可以实现，但我不明白。

您需要先将其拆分为连续行的组，然后按这些组进行分组。

或者你可以使用模式匹配 :)

有了匹配识别，你正在寻找

-任何行
-后跟任意数量的行，其中当前开始 = 前一个结束

它给出了这些模式和定义子句:

    pattern ( init consecutive* )
    define
      consecutive as st = prev ( en )

给予:

with rws ( grp, subgrp, st, en ) as (
  select 123,'A',2010,2011 from dual union all
  select 123,'A',2011,2012 from dual union all
  select 123,'A',2012,2013 from dual union all
  select 123,'A',2013,2014 from dual union all
  select 123,'B',2014,2015 from dual union all
  select 123,'B',2015,2016 from dual union all
  select 123,'B',2016,2017 from dual union all
  select 123,'A',2017,2018 from dual union all
  select 123,'A',2018,2019 from dual union all
  select 123,'A',2019,2020 from dual 
)
  select * from rws match_recognize (
    partition by grp, subgrp
    order  by st, en
    measures 
      first ( st ) mn,
      last ( en ) mx
    pattern ( init consecutive* )
    define
      consecutive as st = prev ( en )
  )
  order by mn;
  
GRP    SUBGRP     MN      MX     
   123 A        2010    2014 
   123 B        2014    2017 
   123 A        2017    2020 

要使用普通分析函数:

-计算每个组和子组的行号，按开始/结束排序
-从开始或结束值中减去该行 #，为您提供一个组

这就是众所周知的Tabibitosan方法。

按这样计算的组来得到你想要的分割:

with rws ( grp, subgrp, st, en ) as (
  select 123,'A',2010,2011 from dual union all
  select 123,'A',2011,2012 from dual union all
  select 123,'A',2012,2013 from dual union all
  select 123,'A',2013,2014 from dual union all
  select 123,'B',2014,2015 from dual union all
  select 123,'B',2015,2016 from dual union all
  select 123,'B',2016,2017 from dual union all
  select 123,'A',2017,2018 from dual union all
  select 123,'A',2018,2019 from dual union all
  select 123,'A',2019,2020 from dual 
), grps as (
  select r.*,
         st - row_number () over (
           partition by grp, subgrp
           order  by st, en
         ) g
  from   rws r
)
  select grp, subgrp, 
         min ( st ), max ( en )
  from   grps
  group  by grp, subgrp, g
  order  by 3;
  
GRP    SUBGRP   MIN(ST)   MAX(EN)   
   123 A           2010      2014 
   123 B           2014      2017 
   123 A           2017      2020