sql - 合并日期范围

Question

Oracle SQL 新手在这里和第一次海报。

我以为这很简单，直到我意识到我无法弄清楚如何拆分返回分配。

这是我的任务表:ASGN

ID  ST_DT       END_DT      POS    LOCN  STATUS  WAGE_CD
--  ----------  ----------  -----  ----  ------  -------
A   12-31-2006  08-16-2009  CLERK  LAX   3       A
A   08-17-2009  10-04-2009  CLERK  LAX   0       Z
A   10-05-2009  06-30-2010  OPR    NYC   3       A
A   07-01-2010  12-31-2010  OPR    NYC   3       B
A   01-01-2011  06-30-2012  OPR    NYC   3       C
A   07-01-2012  04-09-2013  OPR    NYC   3       D
A   04-10-2013  06-30-2013  CLERK  LAX   3       A
A   07-01-2013  08-10-2014  CLERK  LAX   3       B
A   07-01-2013  08-10-2014  CLERK  LAX   3       C
B   04-10-2013  05-31-2013  SUP    LAX   3       A
B   06-01-2013  06-30-2014  SUP    LAX   0       Z
B   07-01-2013  08-10-2014  SUP    LAX   3       B
B   08-11-2014  08-11-2014  CLERK  NYC   3       A
B   08-12-2014  01-11-2015  SUP    LAX   3       A
B   01-12-2015  02-10-2016  SUP    LAX   3       B
B   02-11-2016  08-12-2016  OPER   SFO   3       A
B   02-11-2016  08-12-2016  OPER   SFO   3       B

我已经在下面尝试过这个，结果出乎意料。

SELECT *
FROM (
   SELECT ID
         ,MIN(ST_DT) ST_DT
         ,MAX(END_DT) END_DT
         ,POS
         ,LOCN
         ,STATUS
   FROM ASGN
   GROUP BY ID, LOCN, POS, STATUS
) SUBQRY
ORDER BY ID, ST_DT

出乎意料的结果，但在情理之中。这是返回分配不与以前的分配相结合的地方。

ID  ST_DT       END_DT      POS    LOCN  STATUS
--  ----------  ----------  -----  ----  ------
A   12-31-2006  08-10-2014  CLERK  LAX   3
A   08-17-2009  10-04-2009  CLERK  LAX   0
A   10-05-2009  04-09-2010  OPR    NYC   3
B   04-10-2013  02-10-2015  SUP    LAX   3
B   06-01-2013  06-30-2014  SUP    LAX   0
B   08-11-2014  08-11-2014  CLERK  NYC   3
B   02-11-2016  08-12-2016  OPER   SFO   3

结果我想查看每个 ID、POSition、LOCatioN 和 STATUS 中相邻日期的组合位置:

ID  ST_DT       END_DT      POS    LOCN  STATUS
--  ----------  ----------  -----  ----  ------
A   12-31-2006  08-16-2009  CLERK  LAX   3
A   08-17-2009  10-04-2009  CLERK  LAX   0
A   10-05-2009  04-09-2010  OPR    NYC   3
A   04-10-2013  08-10-2014  CLERK  LAX   3
B   04-10-2013  05-31-2013  SUP    LAX   3
B   06-01-2013  06-30-2014  SUP    LAX   0
B   07-01-2013  08-10-2014  SUP    LAX   3
B   08-11-2014  08-11-2014  CLERK  NYC   3
B   08-12-2014  02-10-2015  SUP    LAX   3
B   02-11-2016  08-12-2016  OPER   SFO   3

我问了一个更高级的 Oracle SQL 程序员，他说我必须给我们 PLSQL，但我认为必须有一种方法可以通过 SQL 使这个工作。

测试设置脚本:

create table asgn
(id varchar2(10)
,st_dt date
,end_dt date
,pos varchar2(10)
,locn varchar2(10)
,status number
,wage_cd varchar2(10));

insert into asgn values('A',to_date('12-31-2006','mm-dd-yyyy'),to_date('08-16-2009','mm-dd-yyyy'),'CLERK','LAX',3,'A');
insert into asgn values('A',to_date('08-17-2009','mm-dd-yyyy'),to_date('10-04-2009','mm-dd-yyyy'),'CLERK','LAX',0,'Z');
insert into asgn values('A',to_date('10-05-2009','mm-dd-yyyy'),to_date('06-30-2010','mm-dd-yyyy'),'OPR','NYC',3,'A');
insert into asgn values('A',to_date('07-01-2010','mm-dd-yyyy'),to_date('12-31-2010','mm-dd-yyyy'),'OPR','NYC',3,'B');
insert into asgn values('A',to_date('01-01-2011','mm-dd-yyyy'),to_date('06-30-2012','mm-dd-yyyy'),'OPR','NYC',3,'C');
insert into asgn values('A',to_date('07-01-2012','mm-dd-yyyy'),to_date('04-09-2013','mm-dd-yyyy'),'OPR','NYC',3,'D');
insert into asgn values('A',to_date('04-10-2013','mm-dd-yyyy'),to_date('06-30-2013','mm-dd-yyyy'),'CLERK','LAX',3,'A');
insert into asgn values('A',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'CLERK','LAX',3,'B');
insert into asgn values('A',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'CLERK','LAX',3,'C');
insert into asgn values('B',to_date('04-10-2013','mm-dd-yyyy'),to_date('05-31-2013','mm-dd-yyyy'),'SUP','LAX',3,'A');
insert into asgn values('B',to_date('06-01-2013','mm-dd-yyyy'),to_date('06-30-2014','mm-dd-yyyy'),'SUP','LAX',0,'Z');
insert into asgn values('B',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'SUP','LAX',3,'B');
insert into asgn values('B',to_date('08-11-2014','mm-dd-yyyy'),to_date('08-11-2014','mm-dd-yyyy'),'CLERK','NYC',3,'A');
insert into asgn values('B',to_date('08-12-2014','mm-dd-yyyy'),to_date('01-11-2015','mm-dd-yyyy'),'SUP','LAX',3,'A');
insert into asgn values('B',to_date('01-12-2015','mm-dd-yyyy'),to_date('02-10-2016','mm-dd-yyyy'),'SUP','LAX',3,'B');
insert into asgn values('B',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'OPER','SFO',3,'A');
insert into asgn values('B',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'OPER','SFO',3,'B');

Answer 1

员工 A 在 07-01-2013 到 08-10-2014 有两行；我认为这是一个错误，我删除了其中一行。

除此之外，这是“tabibitosan 方法”的应用，用于解决日期范围的“间隙和岛屿”问题。诀窍在于为相邻间隔创建“组”( gp )。

with
     prep ( id, st_dt, end_dt, gp, pos, locn, status ) as (
       select id, st_dt, end_dt, 
              end_dt - sum( end_dt - st_dt + 1 ) over (partition by id, pos, locn, status 
                                                       order by st_dt),
              pos, locn, status
       from   asgn
     )
select id, min(st_dt) as st_dt, max(end_dt) as end_dt, pos, locn, status
from   prep
group by id, gp, pos, locn, status
order by id, st_dt
;



ID         ST_DT      END_DT     POS        LOCN           STATUS
---------- ---------- ---------- ---------- ---------- ----------
A          12-31-2006 08-16-2009 CLERK      LAX                 3
A          08-17-2009 10-04-2009 CLERK      LAX                 0
A          10-05-2009 04-09-2013 OPR        NYC                 3
A          04-10-2013 08-10-2014 CLERK      LAX                 3
B          04-10-2013 05-31-2013 SUP        LAX                 3
B          06-01-2013 06-30-2014 SUP        LAX                 0
B          07-01-2013 08-10-2014 SUP        LAX                 3
B          08-11-2014 08-11-2014 CLERK      NYC                 3
B          08-12-2014 02-10-2016 SUP        LAX                 3
B          02-11-2016 08-12-2016 OPER       SFO                 3

 10 rows selected