SQL处理数据同期群分析

最新推荐文章于 2024-06-02 14:44:28 发布

艾斯xi

最新推荐文章于 2024-06-02 14:44:28 发布

阅读量181

点赞数 1

分类专栏： SQL 文章标签： sql mariadb linq

本文链接：https://blog.csdn.net/m0_61059815/article/details/125361644

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL进行数据处理，同期群分析

select 
substr(hire_date,1,7) as in_mon
,count(distinct staff_info_id) as emp_in_m0
,count(distinct if(days>=30, staff_info_id,null)) as emp_num_m1
,count(distinct if(days>=60, staff_info_id,null)) as emp_num_m2
,count(distinct if(days>=90, staff_info_id,null)) as emp_num_m3
,count(distinct if(days>=120,staff_info_id,null)) as emp_num_m4
,count(distinct if(days>=150,staff_info_id,null)) as emp_num_m5
,count(distinct if(days>=180,staff_info_id,null)) as emp_num_m6
,count(distinct if(days>=210,staff_info_id,null)) as emp_num_m7
,count(distinct if(days>=240,staff_info_id,null)) as emp_num_m8
,count(distinct if(days>=270,staff_info_id,null)) as emp_num_m9
,count(distinct if(days>=300,staff_info_id,null)) as emp_num_m10
,count(distinct if(days>=330,staff_info_id,null)) as emp_num_m11
,count(distinct if(days>=360,staff_info_id,null)) as emp_num_m12
,count(distinct if(days>=390,staff_info_id,null)) as emp_num_m13
from (
select staff_info_id
,date(hire_date) hire_date
,if(state=1,null,date(leave_date)) leave_date
,date_diff(if(state=1,null,date(leave_date)), date(hire_date)) as days
FROM bi_pro.hr_staff_info
WHERE job_title in (13,110,452)
AND formal=1)t
group by 1
order by 1;