sql - Oracle SQL 组问题

Question

我试图总结一个员工表，其中一个员工在一个团队中时存在多条记录。我试图分组，最小/最大超过分区和领先/滞后团队名称，但每个结果都以一个代理从一个团队移出，然后在以后作为一个事件返回到原始团队组，即使我按日期排序。

示例数据库:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 02/JAN/19

John Smith    | 123123      | Team A      | Site A   | 02/JAN/19  | 03/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 04/JAN/19

John Smith    | 123123      | Team A      | Site A   | 04/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 05/JAN/19  | 06/JAN/19

当我运行示例查询时:

SELECT
Employee Name
,Employee ID
,Team Leader
,Location
,MIN(Start Date) OVER(PARTITION BY Team Leader ORDER BY Employee ID, Start Date) AS Starting Date
,MAX(End Date) OVER(PARTITION BY Team Leader ORDER BY Employee ID, End Date) AS End Date
FROM TABLE 1

结果如下:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 06/JAN/19

可以帮助实现预期的结果:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 03/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 04/JAN/19

John Smith    | 123123      | Team A      | Site A   | 04/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 05/JAN/19  | 06/JAN/19

Answer 1

这是一种选择:

test CTE 代表你的数据(简化一点)

有用的代码从第 8 行开始

SQL> with test (ename, team, start_date, end_date) as
  2    (select 'John', 'A', date '2019-01-01', date '2019-01-02' from dual union all
  3     select 'John', 'A', date '2019-01-02', date '2019-01-03' from dual union all
  4     select 'John', 'B', date '2019-01-03', date '2019-01-04' from dual union all
  5     select 'John', 'A', date '2019-01-04', date '2019-01-05' from dual union all
  6     select 'John', 'B', date '2019-01-05', date '2019-01-06' from dual
  7    ),
  8  temp as
  9    (select ename, team, start_date, end_date,
 10       row_number() over (order by start_date) rn,
 11       row_number() over (partition by ename, team order by start_date) rna
 12     from test
 13    )
 14  select ename, team, min(start_date) start_date, max(end_date) end_date
 15  from temp
 16  group by ename, team, (rn - rna)
 17  order by 3;

ENAM T START_DATE  END_DATE
---- - ----------- -----------
John A 01/jan/2019 03/jan/2019
John B 03/jan/2019 04/jan/2019
John A 04/jan/2019 05/jan/2019
John B 05/jan/2019 06/jan/2019

SQL>