PL/SQL如何实现滚动12个月时间窗口内的离职人数汇总

现有数据基础 #

现有查询可返回2017年1月1日以来的两类数据：

• 离职人数
• 对应离职日期
  注意：仅存在离职记录的日期会生成对应数据行，无离职发生的日期没有对应记录。

具体统计需求 #

需要创建滚动12个月时间桶，汇总各时间桶内的离职总人数，规则如下：

• 最新时间桶的时间范围：起始为2021年7月1日0点，截止为2022年6月30日23:59，统计该区间内的离职人数总和
• 最终输出近60个月对应的共60个12个月滚动时间桶，以及每个时间桶对应的离职人数统计值

现有可复用查询代码 #

select 
    count(distinct employee_number) Number_of_terminations
    , to_char(term_date, 'MM/DD/YYYY') term_date
from 
        (
                select paa.person_id
                  ,max(paa.effective_end_date)+1 term_date
                  ,pap.employee_number
                                        
                from 
                apps.per_all_assignments_f paa
                , apps.per_assignment_status_types past
                    ,(select distinct paa.person_id
                        from 
                        apps.per_all_assignments_f paa
                        , apps.per_assignment_status_types past
                        where paa.assignment_status_type_id = past.assignment_status_type_id
                        and sysdate between paa.effective_start_date and paa.effective_end_date
                        and past.user_status in ('Active Assignment','Transitional - Active','Transitional - Inactive','Sabbatical','Sabbatical 50%')) active_person
                , apps.per_all_people_f pap    
                , apps.hr_organization_units org
                
                  ,(select case when orgp.name = 'Random University' then orgc.attribute1 else orgp.attribute1 end unit_number
                      ,case when orgp.name = 'Random State University' then orgc.name else orgp.name end unit_name
                      ,orgc.attribute1 dept_number
                      ,orgc.name dept_name
                    from apps.per_org_structure_elements_v2 pose
                      ,apps.per_org_structure_versions posv
                      ,apps.hr_all_organization_units orgp
                      ,apps.hr_all_organization_units orgc
                    where pose.org_structure_version_id = posv.org_structure_version_id
                    and pose.organization_id_parent = orgp.organization_id
                    and pose.organization_id_child = orgc.organization_id
                    and trunc(sysdate) between posv.date_from and nvl(posv.date_to,'31-dec-4712')
                    and pose.org_structure_hierarchy = 'Units'
                    order by case when orgp.name = 'Colorado State University' then orgc.attribute1 else orgp.attribute1 end
                      ,orgc.attribute1) u
                              
                 , apps.per_jobs pj
                , apps.per_job_definitions pjd
                
                where paa.assignment_status_type_id = past.assignment_status_type_id
                and paa.person_id = active_person.person_id(+)
                and active_person.person_id is null
                and past.user_status in ('Active Assignment','Transitional - Active','Transitional - Inactive','Sabbatical','Sabbatical 50%')
                and pap.person_id = paa.person_id
                and paa.organization_id = org.organization_id
                and org.attribute1 = u.dept_number(+)
                and paa.job_id = pj.job_id
                and pj.job_definition_id = pjd.job_definition_id
                and pap.employee_number is not null
                and (
                    paa.effective_end_date like '%17' or
                    paa.effective_end_date like '%18' or
                    paa.effective_end_date like '%19' or
                    paa.effective_end_date like '%20' or
                    paa.effective_end_date like '%21' or
                    paa.effective_end_date like '%22' 
                    )
             
                group by paa.person_id
                , pap.employee_number


        ) terms
        --group by substr(term_date, 4, 6)
        group by to_char(term_date, 'MM/DD/YYYY')

补充说明 #

当前查询返回结果示例、Excel中对应计算逻辑可参考配套示例截图。

内容的提问来源于stack exchange，提问作者smithmds