实际在职人数

最新推荐文章于 2024-06-06 13:24:55 发布
最新推荐文章于 2024-06-06 13:24:55 发布
阅读量702 收藏 9
点赞数 10
分类专栏： SQL 大数据 文章标签： sql 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Taerge0110/article/details/134751585
版权

SQL统计实际在职人数

问题 分两种情况:

  1. 每月在职人数, 只要本月在职, 就算做1人在职
  2. 本月在职几天, 在职人数=在职天数/本月天数

情况一:

问题: 有员工信息表, 统计近 3个月, 每个月实际在职人数(只要本月有在职,不管在职几天,就算做1人在职)
 员工信息表: user_id(用户id), start_date(入职时间), end_date(离职时间,null代表未离职)
 日期月份表: mt(月份)
1. 数据准备
-- 数据准备
WITH t_emp_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, '2023-08-14', '2023-09-23'),
            (1002, '2023-09-10', '2023-10-25'),
            (1003, '2023-09-16', '2023-12-15'),
            (1004, '2023-09-21', NULL ),
            (1005, '2023-10-12', '2023-11-21'),
            (1006, '2023-10-16', '2023-10-28'),
            (1007, '2023-10-20', '2023-12-02'),
            (1008, '2023-10-26', '2023-11-26'),
            (1009, '2023-10-28', NULL ),
            (1010, '2023-11-02', '2023-11-26'),
            (1011, '2023-11-06', '2023-12-16'),
            (1012, '2023-11-12', '2023-12-25')
) AS table_name(user_id, start_date, end_date)
)

, t_mt AS ( -- 日期月份表
SELECT * FROM (
  VALUES
    ('2023-09'), ('2023-10'), ('2023-11')
) AS table_name(mt)
)
2. 代码实现
-- 1. 关联,月份在职人员情况
SELECT 
    /*+ mapjoin(b) */  -- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
      a.mt, b.user_id, start_date, end_date
FROM t_mt a
LEFT JOIN t_emp_info b 
ON  a.mt >= DATE_FORMAT(b.start_date, 'yyyy-MM') AND a.mt <= DATE_FORMAT(NVL(b.end_date, '2023-11-30'), 'yyyy-MM')
 ;
mtuser_idstart_dateend_date
2023-0910012023-08-142023-09-23
2023-0910022023-09-102023-10-25
2023-0910032023-09-162023-12-15
2023-0910042023-09-21\N
2023-1010022023-09-102023-10-25
2023-1010032023-09-162023-12-15
2023-1010042023-09-21\N
2023-1010052023-10-122023-11-21
2023-1010062023-10-162023-10-28
2023-1010072023-10-202023-12-02
…………
-- 2. 统计每月在职人数
SELECT 
    mt, COUNT(DISTINCT user_id) AS user_ct
FROM (
    SELECT 
        /*+ mapjoin(b) */  -- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
          a.mt, b.user_id, start_date, end_date
    FROM t_mt a
    LEFT JOIN t_emp_info b 
    ON  a.mt >= DATE_FORMAT(b.start_date, 'yyyy-MM') AND a.mt <= DATE_FORMAT(NVL(b.end_date, '2023-11-30'), 'yyyy-MM')
) c 
GROUP BY mt
;
mtuser_ct
2023-094
2023-108
2023-119

情况二:

问题: 有员工信息表, 统计近 3个月, 每个月实际在职人数( 在职人数=在职天数/本月天数 )
 员工信息表: user_id(用户id), start_date(入职时间), end_date(离职时间,null代表未离职)
 日期表: dt_date(日期)
1. 数据准备
WITH t_emp_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, '2023-08-14', '2023-09-23'),
            (1002, '2023-09-10', '2023-10-25'),
            (1003, '2023-09-16', '2023-12-15'),
            (1004, '2023-09-21', NULL ),
            (1005, '2023-10-12', '2023-11-21'),
            (1006, '2023-10-16', '2023-10-28'),
            (1007, '2023-10-20', '2023-12-02'),
            (1008, '2023-10-26', '2023-11-26'),
            (1009, '2023-10-28', NULL ),
            (1010, '2023-11-02', '2023-11-26'),
            (1011, '2023-11-06', '2023-12-16'),
            (1012, '2023-11-12', '2023-12-25')
) AS table_name(user_id, start_date, end_date)
)

, t_dt AS ( -- 日期表
SELECT * FROM (
  VALUES
                      ('2023-09-10'), ('2023-09-20')
    , ('2023-09-01'), ('2023-09-11'), ('2023-09-21')
    , ('2023-09-02'), ('2023-09-12'), ('2023-09-22')
    , ('2023-09-03'), ('2023-09-13'), ('2023-09-23')
    , ('2023-09-04'), ('2023-09-14'), ('2023-09-24')
    , ('2023-09-05'), ('2023-09-15'), ('2023-09-25')
    , ('2023-09-06'), ('2023-09-16'), ('2023-09-26')
    , ('2023-09-07'), ('2023-09-17'), ('2023-09-27')
    , ('2023-09-08'), ('2023-09-18'), ('2023-09-28')
    , ('2023-09-09'), ('2023-09-19'), ('2023-09-29')
    , ('2023-09-30'), ('2023-10-10'), ('2023-10-20')
    , ('2023-10-01'), ('2023-10-11'), ('2023-10-21')
    , ('2023-10-02'), ('2023-10-12'), ('2023-10-22')
    , ('2023-10-03'), ('2023-10-13'), ('2023-10-23')
    , ('2023-10-04'), ('2023-10-14'), ('2023-10-24')
    , ('2023-10-05'), ('2023-10-15'), ('2023-10-25')
    , ('2023-10-06'), ('2023-10-16'), ('2023-10-26')
    , ('2023-10-07'), ('2023-10-17'), ('2023-10-27')
    , ('2023-10-08'), ('2023-10-18'), ('2023-10-28')
    , ('2023-10-09'), ('2023-10-19'), ('2023-10-29')
    , ('2023-10-30'), ('2023-11-10'), ('2023-11-20')
    , ('2023-11-01'), ('2023-11-11'), ('2023-11-21')
    , ('2023-11-02'), ('2023-11-12'), ('2023-11-22')
    , ('2023-11-03'), ('2023-11-13'), ('2023-11-23')
    , ('2023-11-04'), ('2023-11-14'), ('2023-11-24')
    , ('2023-11-05'), ('2023-11-15'), ('2023-11-25')
    , ('2023-11-06'), ('2023-11-16'), ('2023-11-26')
    , ('2023-11-07'), ('2023-11-17'), ('2023-11-27')
    , ('2023-11-08'), ('2023-11-18'), ('2023-11-28')
    , ('2023-11-09'), ('2023-11-19'), ('2023-11-29')
    , ('2023-11-30'), ('2023-10-31')
) AS table_name(dt_date)
)
2. 代码实现
-- 1. 日期和用户关联
SELECT 
    /*+ mapjoin(b) */  -- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
    a.dt_date, b.user_id, b.start_date, b.end_date
FROM t_dt a 
LEFT JOIN t_emp_info b  
ON a.dt_date BETWEEN b.start_date AND NVL(b.end_date, '2023-11-30') 
;
dt_dateuser_idstart_dateend_date
2023-09-1010012023-08-142023-09-23
2023-09-2010012023-08-142023-09-23
2023-09-0110012023-08-142023-09-23
2023-09-1110012023-08-142023-09-23
2023-09-2110012023-08-142023-09-23
2023-09-0210012023-08-142023-09-23
2023-09-1210012023-08-142023-09-23
2023-09-2210012023-08-142023-09-23
2023-09-0310012023-08-142023-09-23
2023-09-1310012023-08-142023-09-23
2023-09-2310012023-08-142023-09-23
2023-09-0410012023-08-142023-09-23
…………
-- 2. 统计每月实际在职人数
SELECT 
      DATE_FORMAT(dt_date, 'yyyy-MM') AS mt -- 月份
    , COUNT(user_id) AS user_day_ct -- 在职人天数(本月所有在职人, 总共在职多少天)
    , COUNT(DISTINCT dt_date) AS mt_ct -- 本月天数
    , ROUND(COUNT(user_id) / COUNT(DISTINCT dt_date) , 3) AS user_ct 
FROM (
    SELECT 
        /*+ mapjoin(b) */  -- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
        a.dt_date, b.user_id, b.start_date, b.end_date
    FROM t_dt a 
    LEFT JOIN t_emp_info b  
    ON a.dt_date BETWEEN b.start_date AND NVL(b.end_date, '2023-11-30') 
) c
GROUP BY DATE_FORMAT(dt_date, 'yyyy-MM')
;

-- 2. 方法二: 笛卡尔积
SELECT 
      DATE_FORMAT(dt_date, 'yyyy-MM') AS mt -- 月份
    , COUNT(user_id) AS user_day_ct -- 在职人天数(本月所有在职人, 总共在职多少天)
    , COUNT(DISTINCT dt_date) AS mt_ct -- 本月天数
    , ROUND(COUNT(user_id) / COUNT(DISTINCT dt_date) , 3) AS user_ct 
FROM (
    SELECT -- join 时使用 笛卡尔积, 下游进行过滤
        /*+ mapjoin(b) */  -- MC 运行环境要求, 没有使用 MapJoin 不能使用笛卡尔积, join 条件不能使用范围类型
        a.dt_date, b.user_id, b.start_date, b.end_date
    FROM t_dt a 
    LEFT JOIN t_emp_info b  
) c
WHERE dt_date BETWEEN start_date AND NVL(end_date, '2023-11-30') 
GROUP BY DATE_FORMAT(dt_date, 'yyyy-MM')
;
mtuser_day_ctmt_ctuser_ct
2023-0969302.3
2023-10142314.581
2023-11236307.867
end
Taerge0110
关注
  • 10
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
在职训练实施结果表.doc
06-01
在职训练实施结果表 部 门 项目 班次 人数 时间 费用 备 注 预定 实际 预定 实际 预定 实际 预定 实际
MySQL统计每个月实际在职员工数量
Westbroobo的博客
03-06 2262
两种思路统计每个月实际在职员工数量
SQL笛卡尔积查询的典型应用
未来博客
03-18 2709
SQL笛卡尔积查询的典型应用 作者：AntoniotheFuture 关键词：SQL,笛卡尔积 开发平台：不限 开发语言：SQL 简介： 介绍一个利用SQL笛卡尔积查询来简化代码的典型应用 我的日常工作中经常用到SQL，但没用过笛卡尔积查询，以前一直以为他只会出现在逻辑出错的时候，而且通常意味着大量的资源开销，不过今天有幸能够使用一次，而且发现他能简化代码，分享给大家： 现在有...
问答题目1：根据员工在职明细表，统计2020年每月在职人数
清风送云,天上无月
12-04 2913
逛问答遇到一个自己能解决的，记录一下，写的质量可能有点粗糙，凑合看看吧。 给出一个表格，包括员工号、员工姓名、入职日期、离职日期。 统计2020年每个月在职人数。 帖子地址 https://ask.csdn.net/questions/1679770 代码： import xlrd class Read_Ex(): def read_excel(self): # 打开excel表，填写路径 book = xlrd.open_workbook("wo..
按月统计在职人员数量
Raqsoft
10-21 974
【问题】 I have a table PEOPLE, DATE, DELETED Amanda,2015-03-01,Null Ray,2015-03-01,Null Moe,2015-04-01,Null Yan,2015-05-01,Null Bee,2015-05-05,2015-06-12 now I need to group it and sum it with months like this: March:2people April:3 May:5 June:5 July:4.
MySQL笔记
qq_33898609的博客
09-02 1410
1 条件查询执行顺序 语法格式select from where 执行顺序：先from 然后where，最后select
sql 的基础函数
桃子σ
11-02 819
数据库基础操作库的操作创建库查询库删除库表的操作创建表查看表修改表描述表结构删除表数据的操作查询数据插入数据修改数据删除数据数据类型字段约束基本函数基础函数小数的操作时间的函数条件查询NULLBETWEEN .... AND....LIMIT 分页ORDER BY 排序默认升序聚合函数分组 基础操作 库的操作 创建库 create database 库名 default character set utf-8; # 防止中文乱码 查询库 show databases; 删除库 drop
mysql常见的面试题
热门推荐
方圆几里的博客
08-11 2万+
package com.chp.mysql; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Typ...
在职训练培训报表.doc
02-01
参训人数分为计划人数和实际人数。计划人数是预先设定的目标，而实际人数是最终参与培训的员工数量。两者之间的差距可能反映出报名情况、员工参与度或者资源调度的问题。 训练费用部分是报表的关键，它涵盖了项目的...
SQL2000 统计每周,每月,每季,每年的数据
07-24
实例介绍按照每周,每月,每季,每年如何统计数据
在职训练结训报表.doc
02-01
计划人数是预期的目标，实际人数则反映实际参与情况，可能因各种原因有所变化。 5. **训练费用**：分为预算金额和实际金额，包括讲师费、教材费和其他费用。预算金额是预先规划的花费，实际金额则反映了实际支出，...
Oracle
a769023860a的博客
12-09 528
Oracle基础 1，简单查询 1.1 查询所有信息 select * from table_name; 查询 所有列 来自 表名 1.2 展示指定列 select employee_id,first_name from employees; 查询 列名1 ，列名2 来自 employees表; 1.3列支持数据运算（±*/） select first_name,salary...
一条SQL查询今年每月统计信息
专注力、洞察力、分享力
08-19 1253
前不久，笔者介绍过一种统计近 N 天记录数的需求解决方案。今天，笔者再介绍一种也很常见的统计需求。示例需求： 统计今年每月的注册用户数。你可以基于这个示例需求，去完成各种类似的月统计需求。而且啊，笔者今天这个需求解决方案的重点是在 SQL 上，这类需求问题在 SQL 语句笔试上也挺常见，所以下回再见到类似的需求，你可以好好回想下本篇实现。
mysql统计每月在籍人数_mysql函数实例-统计月登陆用户数
weixin_26886855的博客
02-10 443
统计月使用用户数，仅作备忘。CREATE FUNCTION statics_user_login_month() RETURNS int(11)COMMENT '月使用用户数\r\n'BEGIN#Routine body goes here...DECLARE stopFlag INT DEFAULT 0 ;DECLARE _shop_id INT(11) DEFAULT 0;DECLARE _...
SQL 计算每个月的工作天数
weixin_34387284的博客
07-26 1355
在考勤应用中，经常会有每月工作天数、公休天数、出勤天数的计算。 当月工作天数=当月天数-当月星期六天数-当月星期天天数 我们通过DATEDIF、DATEPART这两个函数运算得出。 selectDATEDIFF(day,'2010-07-01','2010-07-31')+1-(2*DATEDIFF(week,'2010-07...
sql 查询全年入职与离职统计_SQL牛客网刷题（三）——5、6、21、22、23题解析
weixin_39645308的博客
11-27 296
【SQL5. 查找所有员工的last_name和first_name以及对应部门编号dept_no】题目描述：查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL,`dept_no` char(4)...
sql按月份统计每个月的总数
--lky的博客
08-20 4018
由于碰到很多次这种需求了 特别是前端需要做一些折线图的数据时候 就需要每个月的统计数据 特意写个sql 记录下 SELECT yue.m as mon,IFNULL(a.sum,0) as sellValue FROM ( SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELEC
大数据SQL题50 员工在职人数问题
最新发布
qq_35237689的博客
06-06 260
统计2020年每个月实际在职员工数量(只统计2020-03-31之前)，如果1个月在职天数只有1天，数量计算方式：1/当月天数。如果一个月只有一天的话，只算30分之1个人。现有用户表（emp）如下。
在sql server中写一个sql，现在给出每一个人在职时长，那如何以四个时间段作为分组条件，比如0到1个月，1到2个月
03-24
以下是 SQL Server 中根据在职时长将员工分组的 SQL 代码： SELECT CASE WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 0 AND 1 THEN '0-1个月' WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 1 AND 2 THEN '1-2个月' WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 2 AND 3 THEN '2-3个月' ELSE '3个月以上' END AS 在职时长分组, COUNT(*) AS 人数 FROM 员工表 GROUP BY CASE WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 0 AND 1 THEN '0-1个月' WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 1 AND 2 THEN '1-2个月' WHEN DATEDIFF(MONTH, 入职日期, GETDATE()) BETWEEN 2 AND 3 THEN '2-3个月' ELSE '3个月以上' END; 注意：这里的“员工表”和“入职日期”需要替换成实际的表名和字段名。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值