关注天善智能，走好数据之路↑↑↑

欢迎关注天善智能，我们是专注于商业智能BI，大数据，数据分析领域的垂直社区，学习，问答、求职一站式搞定！

背景

一个老同事发来的需求，需求如下：

我这有个表，就比如说是出差表吧，里面有开始结束时间两个字段

比如一条记录，开始时间是：2015-04-01结束时间是：2015-08-06

意思就是说，这个时间段内员工在出差

但是现在，有这种情况，中间某一天，比如说，2015-05-05我发现这个员工在他家里有上网记录

现在我要把这天从出差这个时间段内扣出来

最终结果：

那一条记录变成两条，就是2015-04-01到2015-05-04一条，还有一条是2015-05-06到2015-08-06

把处理后的数据插入到EMP_RECORD_NEW里

上网记录可能不止一条，有可能连续，

如果不连续，则分割多条，

如果连续，则将连续部分合并分割。

表数据如下（附件压缩文件解压后impdp到数据库scott用户下即可）：

结果：

过程&结果

最一开始想用SQL实现，然而考虑到一个员工多条上网记录的情况，需要多次拆分进行插入，最终没有思路，又想用SP通过游标循环来实现，但最近一直练车就放着没写，最后把这个需求作为毕业考试发给了我的学生，顺便也放到了Oracle群里，最终一个网友写出来了，思路异常奇特，以此为记。

注：感谢Somnus的思路，此SQL并非我所写，是他一人完成。

最终SQL如下：

我们可以把SQL一段段拆开来看

第一段with

把数据union all到一起（D字段）并标识数据来源，为后续数据处理做准备

I：在家上网时间（from 上网表）

S：考勤开始时间（from 考勤表）

E：考勤结束时间（from 考勤表）

临时表里只有2个字段，id和d(date)，date字段做了一个排序，就像一个流水日期一样，每个人都记录了从开始到结束，中间穿插着"偷懒"时间。

第二段分析函数

使用lead函数计算出D和SOURCE的每条数据的下一条，记为DD和SS，DD为获取最终END_DATE做准备；D为获取最终START_DATE做准备。

这里获取下一条的原因：是因为这一条的结束就是下一条记录的开始。

第三段case when

根据标识字段Source和SS来判断，从而对D(Start)和DD(End)进行增减.

Source为S的表示考勤开始，所以开始时间不需要+1

Source为E的表示考勤结束，所以结束时间不需要- 1

其他SS为I的都需要进行提前或推后一天的操作，因为SS为I的都是上网偷懒时间，而偷懒时间不记录考勤。

最后一段过滤连续"偷懒"时间

这个比较好理解了，如果 开始时间 - 1 = 结束时间 则说明是连续偷懒，则过滤掉。

思路很巧，长时间没有写这种需求，感觉确实SQL退步了，愿初心常在。

本文作者：天善社区专家老头子，他更多文章请登陆天善智能社区。