ORACLE SQL 学习笔记4 统计员工最近连续加班次数和连续迟到次数

Oracle 数据库 同时被 2 个专栏收录
3 篇文章 0 订阅
4 篇文章 0 订阅

最近看到一个求助贴,说的是有一张考勤表,表结构为: 员工编号 NO, 上班日期 DATE ,上班状态 STATUS ( 0 正常上班 1 加班 2 迟到), 想统计所有员工最近连续加班次数,开始加班日期,结束加班日期,以及最近连续迟到次数,开始迟到日期,结束迟到日期。之前做过类似的统计,比如用户连续访问天数,打地鼠连续命中次数等,思路基本相同。这里做一次详细记录:
一、准备数据
1.建表

create table stat_work
  (NO CHAR(4) NOT NULL,LOGIN_DATE DATE,STATUS NUMBER(1));

2.插入数据

INSERT INTO STAT_WORK values('1001',to_date('2020-01-17','yyyy-mm-dd'),2);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-16','yyyy-mm-dd'),2);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-16','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-15','yyyy-mm-dd'),2);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-15','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-14','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-14','yyyy-mm-dd'),0);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-13','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-13','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-12','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-12','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1002',to_date('2020-01-11','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-10','yyyy-mm-dd'),1);
  INSERT INTO STAT_WORK values('1001',to_date('2020-01-09','yyyy-mm-dd'),0);
  commit;

数据如图:
在这里插入图片描述
二、分步查询数据
主要分以下几步:
*1.*根据需求,对数据进行筛选。使用排名函数row_number() over()或rank() over()增加伪列,给每行数据添加序号,按no分区,按login_date排序。因为数据行中login_date无重复数据,所以两个函数都可以,甚至可以用dense_rank() over()。同时,增加列(login_date-序号),用以标记登陆日期-序号后的差值,命名为diff_date。

select a.no,
               a.login_date,
               row_number() over(partition by a.no order by a.login_date) rn,
               a.login_date - row_number() over(partition by a.no order by a.login_date) diff_date
          from stat_work a
         where a.status = 1

执行结果如下图:
在这里插入图片描述
从上图可以看到,连续日期在减去其序号后的减值是相同的。所以,再根据diff_date进行分组并计数,个数在2以上的,即是连续天数,不管
*2.*在第1步的结果集基础上,按no和diff_date进行分组统计。其原理是,登陆日期连续,则diff_date相同。

select b.no,
       min(b.login_date) 开始日期,
       max(b.login_date) 结束日期,
       count(*) 连续天数
  from (select a.no,
               a.login_date,
               row_number() over(partition by a.no order by a.login_date) rn,
               a.login_date - row_number() over(partition by a.no order by a.login_date) diff_date
          from stat_work a
         where a.status = 1) b
 group by b.no, b.diff_date
having count(*) >= 2
 order by 1, 2

查询结果如下:在这里插入图片描述
说明:这种查询关键是要理解思路和原理。其关键点就在于对diff_date的理解。

  • 0
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值