随笔- 3  文章- 635  评论- 28  阅读- 28万 
2015年6月23日

      最近,遇到并解决一个SQL上的疑难问题。考勤系统,记录着员工进出公司的刷卡记录。而员工刷卡并不规范,存在刷多次的情况。例如:出去时连续刷多次,进来时也连续刷多次。筛选有效刷卡记录数据的规则:对于出去时连续刷多次(包含一次)的情况,取第一次刷卡记录;对于进来时连续刷多次(包含一次)的情况,取最后一次的刷卡记录。考勤系统的数据量很大,假设公司有2万名员工,一员工一天100条刷卡记录。

      用什么方法可以高效地查出某一时间范围内员工的有效刷卡记录?

      测试表及测试数据如下:       

create table Attendance
(
UserId  int,            --员工ID
ClockInTime datetime,   --员工刷卡时间
Flag char(1)            --进出标志 '1'代表出,'0'代表进   
) 

insert Attendance
values(100001,'2015-06-01 08:03:00','1'),
      (100001,'2015-06-01 08:03:10','1'),
      (100001,'2015-06-01 08:03:50','1'),
      (100001,'2015-06-01 08:04:00','1'),
      (100001,'2015-06-01 08:10:00','0'),
      (100001,'2015-06-01 08:10:10','0'),
      (100001,'2015-06-01 08:15:00','1'),
      (100001,'2015-06-01 08:30:00','1'),
      (100001,'2015-06-01 08:40:10','0'),
      (100001,'2015-06-01 09:00:00','1'),
      (100001,'2015-06-01 09:15:10','0'),
      (100001,'2015-06-01 09:30:00','1'),
      (100002,'2015-06-01 08:03:00','0'),
      (100002,'2015-06-01 08:03:10','0'),
      (100002,'2015-06-01 08:03:50','1'),
      (100002,'2015-06-01 08:04:00','1'),
      (100002,'2015-06-01 08:10:00','1'),
      (100002,'2015-06-01 08:10:10','0'),
      (100002,'2015-06-01 08:15:00','1'),
      (100002,'2015-06-01 08:30:00','1'),
      (100002,'2015-06-01 08:40:10','1'),
      (100002,'2015-06-01 09:00:00','0'),
      (100002,'2015-06-01 09:15:10','0'),
      (100002,'2015-06-01 09:30:00','1')

而需筛选的有效数据为红色标记部分:

而作为测试数据,也就只提供两名员工,每人一天12条的刷卡记录,需要完成的是将红色标记的数据筛选出来。

不难看出问题的难点在于判断哪些数据是连续(进或出)的,无论出去还是进来。把这一点解决了,所有的问题也就迎刃而解。

1)首先,想到了递归查询。但是很快否定了想法,这个方法判断不出来数据是否连续。

2)其次,又考虑游标。或许游标能判断是否连续的问题,但是处理大数据量时,性能肯定极其低。

 

最后,闪现一个思路,没想到顺着这个思路把问题解决了。

1,先按UserID、日期分组,组内按ClockInTime升序排列。

1
2
3
select *,
ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime) as RN into #tmp
 from Attendance <br><br>select * from #tmp order by UserId,ClockInTime

结果如图:

 

2,再按UserID、日期、Flag分组,组内按ClockInTime升序排列。

1
2
3
select *,
ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RN1 into #tmp1
from Attendance <br><br>select * from #tmp1 order by UserId,ClockInTime

结果如图:

 

3,用#tmp1中的RN1与#tmp中的RN做差。

select a.*,b.RN1,b.RN1-a.RN as RN2 into #tmp2
 from #tmp as a,#tmp1 as b 
 where a.UserId=b.UserId and a.ClockInTime=b.ClockInTime and a.Flag=b.Flag

select * from #tmp2 order by UserId,ClockInTime

结果如图:

 

3,根据UserID、日期、Flag、RN2可以判断出哪些数据是连续的,然后,用Row_Number()排序一下,就可以筛选所需要的数据。

1
2
3
4
5
select *,
 case when Flag='1' then ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN2 order by ClockInTime)
 else ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN2 order by ClockInTime desc) end as RId
 into #tmp3
 from #tmp2 <br><br>select * from #tmp3 order by UserId,ClockInTime

结果如图:

 

4,RID=‘1’的数据是正确的结果,即有效的刷卡记录数据。

1
2
3
4
select UserId,ClockInTime,Flag
from #tmp3
where Rid='1'
order by UserId,ClockInTime

结果如图:

 

这样问题就解决了。进一步优化sql,其实1,2,3等3个步骤只要一步就解决了

1
2
3
select *,
ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime)-ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RN
from Attendance order by UserId,ClockInTime

有了上面查询的结果,后面的查询也简单多了。如果用一句SQL来解决的话,那就是:

1
2
3
4
5
6
7
8
9
10
11
select UserId,ClockInTime,Flag from (
select *,
case when Flag='1' then ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN order by ClockInTime)
else ROW_NUMBER() over(Partition by UserID,convert(varchar(10),ClockInTime,23),Flag,RN order by ClockInTime desc) end as RId
from (
select *,
ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime,23) order by ClockInTime)-ROW_NUMBER() over(partition by UserId,convert(varchar(10),ClockInTime),Flag order by ClockInTime) as RN
from Attendance
) as a
) as b
where RId='1' order by UserId,ClockInTime

  

      

posted @ 2015-06-23 10:01 会飞的金鱼 阅读(2090) 评论(13) 推荐(3) 编辑
2014年4月19日
摘要: 在现实生活中,我们可能会遇到这样的问题:三个人合伙买一件商品,假如这件商品的价格为1。三个人平摊的费用为1/3,可以用分数表示,但是计算机处理最后的结果是小数0.33(保留两位小数),这样0.33*3=0.99x,2--->y,3--->Z等,同一组的总费用是相同的。 因为每组的总费用不一样... 阅读全文
posted @ 2014-04-19 21:55 会飞的金鱼 阅读(2055) 评论(0) 推荐(0) 编辑
2014年1月14日
摘要: 今天,我在博客园看到一篇文章,其中的问题我稍微改动一下:有两个医生,甲医生,男性手术失败率18.75%,女性手术,失败率3.9%;乙医生,男性手术失败率20%,女性手术失败率3.92%。问甲乙两个医生谁更优秀一些? 当时,我是这样想的:甲医生手术失败率=(18.75%+3.9%)/2,约等于11.33%;乙医生手术失败率=(20%+3.92%)/2,约等于11.96%;由于11.33%比11.96%小,所以甲医生更优秀一些。还有的人甚至不用计算,无论男性手术还是女性手术,甲医生的失败率都比乙医生的小,整体上甲医生的失败率还是比乙医生的小,所以甲医生相对乙医生更优秀一些。 看到这个题目... 阅读全文
posted @ 2014-01-14 21:47 会飞的金鱼 阅读(1871) 评论(12) 推荐(4) 编辑
点击右上角即可分享
微信分享提示