记一则需求,实现考勤拆分,愿大家初心常在

浏览: 2780

背景

一个老同事发来的需求,需求如下:

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

比如一条记录,开始时间是: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用户下即可):

Clipboard Image.png


Clipboard Image.png

结果:

Clipboard Image.png


过程&结果

最一开始想用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字段做了一个排序,就像一个流水日期一样,每个人都记录了从开始到结束,中间穿插着"偷懒"时间。

Clipboard Image.png


第二段分析函数

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做准备。

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

Clipboard Image.png


第三段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的都是上网偷懒时间,而偷懒时间不记录考勤。

Clipboard Image.png


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

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退步了,愿初心常在。

推荐 6
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

啥时候讲讲模糊查询
like?

要回复文章请先登录注册