如何按不同员工的职务为一系列 Oracle 分配日期记录编写 sql 脚本

Question

我正在尝试按员工的职务为一系列分配日期记录编写即席查询。这些示例用于 Oracle 应用程序分配表。

第一个示例：

AsgId  Start_Date  End_Date  Job_ID 
  1     1/1/14      6/30/14    10
  1     7/1/14      11/15/14   10
  1     11/16/14    1/10/15    20
  1     1/11/15     3/10/15    10
  1     3/11/15     3/31/15    10
  1     4/1/15      12/31/18   20

我尝试过分析函数、内联视图和其他代码，但没有成功。

按职位名称划分的 3 条日期范围记录的预期报告结果：

asgid   start_date   end_date  job_title
 1       1/1/14      11/15/14     10
 1       11/16/14    1/10/15      20
 1       1/11/15     3/31/15      10
 1       4/1/15      12/31/18     20

第二个样本：

EMP_ID START_DATE END_DATE JOB_TITLE 
1      1/1/14     11/15/14 10 
1      11/16/14   11/10/15 10 
1      11/11/15   12/31/15 20 
1      1/1/16     1/31/16  10 
1      2/1/16     12/31/16 10 

按职称划分的 3 条日期范围记录的预期报告结果

EMP_ID START_DATE END_DATE JOB_TITLE 
1      1/1/14     11/10/15 10 
1      11/11/15   12/31/15 20 
1      1/1/16     12/31/16 10

Answer 1

这是一种间隙和孤岛问题。 假设没有间隙或重叠，则可以使用left join和累积总和来确定孤岛。 其余的是聚合：

select asgid, job_id, min(start_date) as start_date,
       max(end_date) as end_date
from (select a.*,
             sum(case when aprev.asgid is null then 1 else 0 end) over (partition by a.asgid, a.job_id order by a.start_date) as grp
      from assignment a left join
           assignment aprev
           on aprev.asgid = a.asgid and
              aprev.job_id = a.job_id and
              aprev.end_date = a.start_date - 1
     ) a
group by asgid, job_id, grp
order by asgid, min(a.start_date);

这是一个数据库<>小提琴。