背景
一个老同事发来的需求,需求如下:
我这有个表,就比如说是出差表吧,里面有开始结束时间两个字段
比如一条记录,开始时间是: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如下:
with t as
(SELECT *
FROM (select emp_id, online_date d, 'I' SOURCE
from ONLINE_RECORD
union all
select empid, start_date, 'S' SOURCE
from EMP_RECORD
union all
select empid, end_date, 'E' SOURCE
from EMP_RECORD)
ORDER BY EMP_ID, D)
select emp_id,
case
when start_date - 1 = end_date then
null
else
start_date
end start_date,
case
when start_date - 1 = end_date then
null
else
end_date
end end_date
from (SELECT EMP_ID,
CASE
WHEN SOURCE = 'S' THEN
D
WHEN SOURCE = 'I' AND SS = 'E' THEN
D + 1
WHEN SOURCE = 'I' AND SS = 'I' THEN
D + 1
ELSE
NULL
END START_DATE,
CASE
WHEN SS = 'E' THEN
DD
WHEN SS = 'I' THEN
DD - 1
WHEN SS = 'S' THEN
NULL
END END_DATE
FROM (SELECT *
FROM (select EMP_ID,
D,
LEAD(D) OVER(PARTITION BY EMP_ID ORDER BY EMP_ID, D) DD,
SOURCE,
LEAD(SOURCE) OVER(PARTITION BY EMP_ID ORDER BY EMP_ID, D) SS
from t)))
where case
when start_date - 1 = end_date then
null
else
start_date
end is not null;
我们可以把SQL一段段拆开来看
第一段with
with t as
(SELECT *
FROM (select emp_id, online_date d, 'I' SOURCE
from ONLINE_RECORD
union all
select empid, start_date, 'S' SOURCE
from EMP_RECORD
union all
select empid, end_date, 'E' SOURCE
from EMP_RECORD)
ORDER BY EMP_ID, D)
把数据union all到一起(D字段)并标识数据来源,为后续数据处理做准备
I:在家上网时间(from 上网表)
S:考勤开始时间(from 考勤表)
E:考勤结束时间(from 考勤表)
临时表里只有2个字段,id和d(date),date字段做了一个排序,就像一个流水日期一样,每个人都记录了从开始到结束,中间穿插着"偷懒"时间。
第二段分析函数
SELECT *
FROM (select EMP_ID,
D,
LEAD(D) OVER(PARTITION BY EMP_ID ORDER BY EMP_ID, D) DD,
SOURCE,
LEAD(SOURCE) OVER(PARTITION BY EMP_ID ORDER BY EMP_ID, D) SS
from t)
使用lead函数计算出D和SOURCE的每条数据的下一条,记为DD和SS,DD为获取最终END_DATE做准备;D为获取最终START_DATE做准备。
这里获取下一条的原因:是因为这一条的结束就是下一条记录的开始。
第三段case when
SELECT EMP_ID,
CASE
WHEN SOURCE = 'S' THEN
D
WHEN SOURCE = 'I' AND SS = 'E' THEN
D + 1
WHEN SOURCE = 'I' AND SS = 'I' THEN
D + 1
ELSE
NULL
END START_DATE,D,
CASE
WHEN SS = 'E' THEN
DD
WHEN SS = 'I' THEN
DD - 1
WHEN SS = 'S' THEN
NULL
END END_DATE,DD,source,ss
FROM xxx
根据标识字段Source和SS来判断,从而对D(Start)和DD(End)进行增减.
Source为S的表示考勤开始,所以开始时间不需要+1
Source为E的表示考勤结束,所以结束时间不需要- 1
其他SS为I的都需要进行提前或推后一天的操作,因为SS为I的都是上网偷懒时间,而偷懒时间不记录考勤。
最后一段过滤连续"偷懒"时间
select emp_id,
case
when start_date - 1 = end_date then
null
else
start_date
end start_date,
case
when start_date - 1 = end_date then
null
else
end_date
end end_date
from ...
where case
when start_date - 1 = end_date then
null
else
start_date
end is not null;
这个比较好理解了,如果 开始时间 - 1 = 结束时间 则说明是连续偷懒,则过滤掉。
思路很巧,长时间没有写这种需求,感觉确实SQL退步了,愿初心常在。