Oracle SQL组问题

gkaess 发表于 Dev

凯斯

我试图汇总一个雇员表，其中一个雇员在一个团队中时存在多个记录。我曾尝试按组名称进行分组，“最小/最大”，“组长/滞后”，但每个结果都以一个代理商的身份结束，该代理商已从一个团队移至另一天，即使后来出现，也回到了原来的团队我按日期排序。

示例数据库：

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

戈登·利诺夫

这看起来像是一种缺口和孤岛的形式，其中记录按日期范围链接。

这是一种left join用于查找岛屿从何处开始的方法，然后是用于确定组和聚集的累积总和：

select employeename, employeeid, teamleader, location,
       min(startdate), max(enddate)
from (select t1.*,
             sum(case when tprev.employeeid is null  -- new group
                      then 1 else 0
                 end) over (partition by employeeid, teamleader, location
                            order by startdate
                           ) as grouping
      from table1 t1 left join
           table1 tprev
           on t1.startdate = tprev.enddate and
              t1.employeeid = tprev.employeeid and
              t1.teamleader = tprev.teamleader and
              t1.location = tprev.location
     ) t
group by employeeid, teamleader, location, grouping
order by employeeid, min(startdate);

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。