数据库-5 求员工连续工作天数

之前遇到的一个需求，求公司员工连续工作天数。

大概的数据格式：

员工工号     日期      当天打卡时长

12345    20171201         8

12345    20171202         9

Oracle中有一个比较方便的分析函数可用 ： lag  over 。

使用此函数需要对数据进行预处理。将打卡时长处理成 0和1，有上班记为1，未上班记为0。

取两个0之间的差值减1即为连续工作天数。

关键语句大概如下：

      work_date- lag(work_date,1) over ( partition by  EMPLOYEE_NO order by work_date) -1  

具体lag用法可百度。

注：需要做时间转换，字符串转时间。
     

    计算额外发现一个问题，源数据缺失导致计算错误，如下数据缺失20171127数据，计算时直接计算1128-1125 -1，与实际不符。

   12345   20171125     0

   12345   20171126     8

   12345   20171128    0

  这个算是数据源的问题，但是计算逻辑也存在问题，无打卡记录不能计算为连续工作，但采用如上方法会将无打卡记录的日期算为工作日期。

  用了一种羞于启齿的方法。

  笛卡尔积 (cross join)。

  在计算过程中额外union all 所有员工近一个月无打卡记录的数据(示例如下)，

12345  20171126    0       

12345  20171127    0

将缺失打卡记录算作未上班。

 拼接之后需要做处理

     select   工号，日期 ，sum(打卡)    from ( 源数据 union all  cross join数据)  group by   工号，日期 ；

处理后的数据就可以用lag来计算了（数据量不太大，10s以内处理完成）。

lag方法思路源自 SQL Cookbook。