SQL按日期统计各部门入职员工数(分今日/昨日/2天前及更早)
按部门维度统计三类入职员工数的SQL实现
需求拆解
- 分组维度:按部门统计,建议同时返回
departmentId和department字段,避免同名部门统计混淆 - 输出格式:每个部门对应一个固定顺序的数值数组,无匹配数据时对应位置补0,数组结构为
[今日入职数, 昨日入职数, 2天及以前入职数] - 依赖字段:
empid(员工唯一标识)、department(部门名称)、departmentId(部门ID)、joinedon(入职时间)
核心实现思路
用条件聚合一次性完成三类计数,避免多次表关联,统计时自动对无匹配的分类返回0值。如果joinedon是带时分秒的日期时间类型,需要先截断为纯日期再比较,避免时间部分导致的统计偏差。
不同数据库落地代码
MySQL(5.7及以上)
SELECT departmentId, department, JSON_ARRAY( SUM(CASE WHEN DATE(joinedon) = CURDATE() THEN 1 ELSE 0 END), SUM(CASE WHEN DATE(joinedon) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 1 ELSE 0 END), SUM(CASE WHEN DATE(joinedon) < DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 1 ELSE 0 END) ) AS emp_count_array FROM 你的实际员工表名 GROUP BY departmentId, department;
若
joinedon本身为DATE类型,可去掉外层DATE()函数,直接比较字段值,能命中索引提升查询效率。
PostgreSQL
SELECT departmentId, department, ARRAY[ COALESCE(SUM(CASE WHEN joinedon::DATE = CURRENT_DATE THEN 1 ELSE 0 END), 0), COALESCE(SUM(CASE WHEN joinedon::DATE = CURRENT_DATE - INTERVAL '1 day' THEN 1 ELSE 0 END), 0), COALESCE(SUM(CASE WHEN joinedon::DATE < CURRENT_DATE - INTERVAL '1 day' THEN 1 ELSE 0 END), 0) ] AS emp_count_array FROM 你的实际员工表名 GROUP BY departmentId, department;
增加
COALESCE是为了避免无员工的部门聚合结果为NULL,保证数组元素全为数值类型。
SQL Server
SELECT departmentId, department, CONCAT( '[', SUM(CASE WHEN CAST(joinedon AS DATE) = CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END), ',', SUM(CASE WHEN CAST(joinedon AS DATE) = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END), ',', SUM(CASE WHEN CAST(joinedon AS DATE) < DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END), ']' ) AS emp_count_array FROM 你的实际员工表名 GROUP BY departmentId, department;
补充注意点
- 若存在未来入职的员工(
joinedon大于当前日期),当前逻辑不会把这类员工计入三个分类,需要过滤的话可在查询中增加对应日期比较的过滤条件 - 如果需要保留无在职员工的部门统计(对应数组全为0),需要先关联部门维度表,再左连接员工表统计,否则无员工的部门不会出现在返回结果中
内容的提问来源于stack exchange,提问作者Samrat Rahul Tiwari
相关产品推荐
相关产品推荐

