劳动力人数的季度计算效率 [英] Efficiency of quarterly calculation of workforce headcount

问题描述

我有一个表per_all_peopl_f，其中包含以下列:

I have one table, per_all_peopl_f, with following columns:

name  person_id  emp_flag  effective_start_date   effective_end_date   DOJ
--------------------------------------------------------------------------------    
ABC   123          Y       30-MAR-2011              30-MAR-2013       10-FEB-2011
ABC   123          Y       24-FEB-2011              27-FEB-2011       10-FEB-2011
DEF   345          N       10-APR-2012              30-DEC-4712       15-SEP-2011

有很多条目(1000多个)，这些条目具有重复的数据和不同的有效开始日期.

There are many entries (1000+) with repeated data and different effective start dates.

我必须计算劳动力人数.也就是说，每季度退出公司的员工人数.

I have to calculate the Workforce headcount. That is, the number of employees that exits the company quarterly.

必须提取以下列:

1. 2012年员工人数(第一季度)
2. 2013年员工人数(第一季度)
3. 两个员工人数之间的差异
4. ％差异

我用来查找季度人数的查询是:

The query I used to find the headcount quarterly is:

功能1:

CREATE OR REPLACE FUNCTION function_name
  (l_end_date ,l_start_date  ) 
  RETURN number;
  IS 
    l_emp

  BEGIN
    select count(distinct papf.person_id)
      into l_emp
      from per_all_people_f papf
     where papf.emp_flag ='Y'
       and effective_start_date >=l_end_date
       and effective_end_date <=l_start_date ;

    return l_emp; 
END function_name;

主程序包:

create xx_pack_name  body
is 
    cursor cur_var
    is 
       select function_name('01-MAR-2012','31-MAY-2012') EMP_2012,
              function_name('01-MAR-2013','31-MAY-2013') EMP_2013,
              function_name('01-MAR-2012','31-MAY-2012')-function_name('01-MAR-2013','31-MAY-2013') Diff
         from dual;

end xx_pack_name  ;

这具有成本效益吗?

推荐答案

如果至少在per_all_people_f表的effective_start_date和effective_end_date字段上有索引，则似乎是一个不错的选择.

Seems to be a good variant in case if there are index on at least effective_start_date and effective_end_date fields of per_all_people_f table.

此查询的理想变体是

create index x_per_all_people_search on per_all_people_f(   
  effective_start_date,
  effective_end_date, 
  person_id,  
  emp_flag
)

但是维护起来可能太昂贵了(磁盘成本，插入速度).

but it may be too expensive to maintain (disk cost, insertion speed).

此外，包主体中的游标必须包含子查询和重用函数调用结果:

Also, cursor in package body must contain subquery and reuse function call results:

cursor cur_var
is 
  select 
    EMP_2012,
    EMP_2013,
    (EMP_2013 - EMP_2012) Diff
  from (
   select 
     function_name('01-MAR-2012','31-MAY-2012') EMP_2012,
     function_name('01-MAR-2013','31-MAY-2013') EMP_2013
   from dual
  );

当然，最好的解决方案是最小化上下文切换并从单个SQL查询中获取所有值.另外，您可以直接向游标提供参数:

Of course best solution is to minimize context switches and get all values from single SQL query. Also, you can supply parameters directly to cursor:

cursor cur_var(
  start_1 date, end_1 date, 
  start_2 date, end_2 date
)
is 
  select 
    EMP_2012,
    EMP_2013,
    (EMP_2013 - EMP_2012) Diff
  from (
   select 
     (
       select 
         count(distinct papf.person_id)
       from 
         per_all_people_f papf
       where 
         papf.emp_flag = 'Y'
         and 
         effective_start_date >= trunc(start_1)
         and 
         effective_end_date   <= trunc(end_1)
     ) EMP_2012,
     (
       select 
         count(distinct papf.person_id)
       from 
         per_all_people_f papf
       where 
         papf.emp_flag = 'Y'
         and 
         effective_start_date >= trunc(start_2)
         and 
         effective_end_date   <= trunc(end_2)
     ) EMP_2013
   from dual
  );

从我的角度来看，函数/游标参数太通用了，可能最好创建一个包装器，该包装器将四分之一的数字和两年的输入参数进行比较.

From my point of view function/cursor parameters is too generic, may be better to create a wrapper which takes as input parameters quarter number and two years to compare.

最后，如果计划在PL/SQL中使用结果(我想是因为返回一行)，则根本不使用游标，只需通过输出参数返回计算值即可.从另一个角度来看，如果您需要在一个游标中获取全年的季度数据，则计算所有季度并在单个查询中进行比较可能会更有效.

And last, if results planned to be used in PL/SQL (I suppose that because of returning a single row) don't use cursor at all, just return calculated values through output parameters. From another point of view if you need to get quarter data for full year in one cursor it may be more efficient to count all quarters and compare it in single query.

这篇关于劳动力人数的季度计算效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！