翼度科技»论坛 编程开发 mysql 查看内容

SQL练习之打卡记录数据统计类问题

2

主题

2

帖子

6

积分

新手上路

Rank: 1

积分
6
最近老婆的公司,关闭了OA系统中,各类打卡时间数据统计的功能,为了不麻烦老婆手算,就做了一个简单的打卡系统,方便自动统计老婆想要知道的各类数据。
做的过程中就遇到了几个还挺有意思的SQL,这里写成一篇博文,方便后期练习~
Tip:需要答案的盆友可以访问 参考答案的链接,密码是123456~
建表语句
  1. drop table if exists fx67ll_punch_log;
  2. create table fx67ll_punch_log (
  3.   punch_id             bigint(20)      not null auto_increment    comment '打卡记录主键',
  4.   punch_type           char(1)                                    comment '打卡类型(1代表上班 2代表下班)',
  5.   punch_remark         varchar(1023)   default ''                 comment '打卡记录备注',
  6.   del_flag             char(1)         default '0'                comment '删除标志(0代表存在 2代表删除)',
  7.   user_id              bigint(20)                                 comment '用户ID',
  8.   create_by            varchar(64)     default ''                 comment '记录创建者',
  9.   create_time                datetime                                   comment '记录创建时间',
  10.   update_by            varchar(64)     default ''                 comment '记录更新者',
  11.   update_time          datetime                                   comment '记录更新时间',
  12.   primary key (punch_id)
  13. ) engine=innodb auto_increment=1 comment = '打卡记录表';
复制代码
插入测试数据
  1. INSERT INTO `ruoyi-mysql`.fx67ll_punch_log (punch_type,punch_remark,del_flag,user_id,create_by,create_time,update_by,update_time) VALUES
  2.          ('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-06 00:00:00'),
  3.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-06 21:00:00'),
  4.          ('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 15:00:00'),
  5.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 18:19:00'),
  6.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 21:00:00'),
  7.          ('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 20:50:00'),
  8.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 21:44:00'),
  9.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 22:50:00'),
  10.          ('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-11 10:00:00'),
  11.          ('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-11 20:00:00'),
  12.          ('1','','0',101,'user','2023-12-13 17:03:22','user','2023-12-13 00:06:00'),
  13.          ('2','e e e','0',101,'user','2023-12-13 17:03:14','user','2023-12-11 01:01:00'),
  14.          ('1','123','0',1,'fx67ll','2023-12-14 09:53:54','fx67ll','2023-12-14 09:53:50'),
  15.          ('2','','0',101,'user','2024-03-13 17:49:16','user','2024-03-13 17:49:00'),
  16.          ('1','324','0',101,'user','2024-03-21 11:22:16','user','2024-03-21 11:22:17'),
  17.          ('2','','0',101,'user','2024-03-21 11:22:43','user','2024-03-21 22:22:39'),
  18.          ('2','','0',1,'fx67ll','2024-03-30 20:01:10','fx67ll','2024-03-30 20:01:00'),
  19.          ('1','','0',1,'fx67ll','2024-04-30 15:01:16','fx67ll','2024-04-30 00:01:06'),
  20.          ('2','','0',1,'fx67ll','2024-04-30 15:01:25','fx67ll','2024-04-30 23:01:16'),
  21.          ('1','','0',1,'fx67ll','2024-04-30 15:01:31','fx67ll','2024-04-24 15:01:25'),
  22.          ('1','','0',101,'user','2024-05-03 02:39:33','user','2024-05-03 02:39:29'),
  23.          ('1','','0',101,'user','2024-05-03 02:39:41','user','2024-05-04 00:39:33'),
  24.          ('2','123','0',101,'user','2024-05-03 02:39:52','user','2024-05-04 23:39:41');
复制代码
问题一:统计每个用户每个月的工作总时长、总打卡天数、有效打卡天数以及日均工时

需要得到如下的统计数据,每个字段的含义分别是:


  • punch_user:打卡的用户
  • punch_month:本条记录统计的打卡月份
  • total_work_hours:当月的工作总时长,小时为单位
  • total_work_minutes:当月的工作总时长,分钟为单位
  • total_work_seconds:当月的工作总时长,秒为单位
  • total_punch_days:当月的总打卡天数,只要有打卡记录就算,可能只打了上班卡,或者只打了下班卡,但是,没有打卡记录的天数则不算
  • total_work_days:当月的有效打卡天数,必须满足条件,既有上班打卡记录,又有下班打卡记录,才算一个有效打卡天数
  • work_hours_per_day:当月的日均工时,小时为单位
  1. punch_user|punch_month|total_work_hours|total_work_minutes|total_work_seconds|total_punch_days|total_work_days|work_hours_per_day|
  2. ----------+-----------+----------------+------------------+------------------+----------------+---------------+------------------+
  3. fx67ll    |2023-12    |         39.0000|              2340|            140400|               5|              4|        9.75000000|
  4. fx67ll    |2024-03    |                |                  |                  |               1|              0|                  |
  5. fx67ll    |2024-04    |         23.0028|              1380|             82810|               2|              1|       23.00277778|
  6. user      |2023-12    |                |                  |                  |               2|              0|                  |
  7. user      |2024-03    |         11.0061|               660|             39622|               2|              1|       11.00611111|
  8. user      |2024-05    |         23.0022|              1380|             82808|               2|              1|       23.00222222|
复制代码
问题二:统计每个用户当月的只打了一次卡的缺卡记录

需要得到如下的统计数据,每个字段的含义分别是:


  • punch_user:缺卡的用户
  • punch_month:本条记录的缺卡月份
  • punch_day:本条记录的缺卡日期
  • lost_punch_type:本条记录的缺卡类型,需要输出是上班缺卡还是下班缺卡
  1. punch_user|punch_month|punch_day |lost_punch_type|
  2. ----------+-----------+----------+---------------+
  3. fx67ll    |2023-12    |2023-12-14|下班缺卡           |
  4. fx67ll    |2024-03    |2024-03-30|上班缺卡           |
  5. fx67ll    |2024-04    |2024-04-24|下班缺卡           |
  6. user      |2023-12    |2023-12-11|上班缺卡           |
  7. user      |2023-12    |2023-12-13|下班缺卡           |
  8. user      |2024-03    |2024-03-13|上班缺卡           |
  9. user      |2024-05    |2024-05-03|下班缺卡           |
复制代码
我是 fx67ll.com,如果您发现本文有什么错误,欢迎在评论区讨论指正,感谢您的阅读!
如果您喜欢这篇文章,欢迎访问我的 本文github仓库地址,为我点一颗Star,Thanks~
来源:https://www.cnblogs.com/fx67ll/p/18183022/sql-practice-punch
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具