sql - 查找覆盖两个日期之间的员工的更有效方法

Question

我需要检索员工列表，并为每个员工提供他们在给定年份积极享受福利的月份列表。有一个包含工作数据的表和一个包含福利信息的表。还有一个交付日期表，列出了 2007 年至 2018 年的每个日期，并且对于每个日期，它显示了月份的日期、月份和日历年。

我现在编写查询的方式是说:在日期表上查找所有日期，这些日期是 1) 在提示年份的 01/01 和 12/31 之间(或当前日期，以较早者为准)，2) 在员工在福利表上活跃的时间。对于每个日期，我还需要工作表中的 Deptid 和该日期的福利表中的福利计划。然后我做了一个不同的，只显示每个员工的月份和日历年。

这行得通，但是当我尝试为有很多人的部门这样做时，问题就来了。运行需要很长时间，我相信是因为它为每个员工检索多达 365 行，然后只显示其中的 12 行，因为它只提取不同的月份。我觉得有更好的方法可以做到这一点，我只是想不出它是什么。

以下是我正在使用的表格的一些简化示例:

日期表

THE_DATE   MONTHOFYEAR   CALENDAR_YEAR
01-OCT-15  10            2015
02-OCT-15  10            2015
03-OCT-15  10            2015
...

工作表

(A=活跃；I=不活跃)

EMPLID     EFFDT         DEPTID           HR_STATUS
00123      01-FEB-15     900              A
00123      30-JUN-15     900              I
00123      01-AUG-15     901              A

福利表

EMPLID     EFFDT         BENEFIT_PLAN     STATUS
00123      01-MAR-15     PPO              A
00123      31-JUL-15                      I
00123      01-SEP-15     HMO              A

想要的结果

EMPLID     CALENDAR_YEAR MONTHOFYEAR      DEPTID         BENEFIT_PLAN
00123      2015          3                900            PPO
00123      2015          4                900            PPO
00123      2015          5                900            PPO
00123      2015          6                900            PPO
00123      2015          7                900            PPO
00123      2015          9                901            HMO
00123      2015          10               901            HMO
00123      2015          11               901            HMO
^ (shows November row even though employee was only covered for part of this month)

获取上述结果的示例 SQL

SELECT DISTINCT J.EMPLID, D.CALENDAR_YEAR, D.MONTHOFYEAR, J.DEPTID, B.BENEFIT_PLAN
FROM DATES D, 
     JOBS J 
     JOIN 
     BENEFITS B 
     ON J.EMPLID = B.EMPLID
WHERE D.THE_DATE <= SYSDATE
AND D.THE_DATE BETWEEN 
        TO_DATE(:YEAR_PROMPT || '01-01', 'YYYY-MM-DD') 
        AND 
        TO_DATE(:YEAR_PROMPT || '12-31', 'YYYY-MM-DD')
AND B.STATUS = 'A'
AND D.THE_DATE BETWEEN 
        B.EFFDT 
        AND 
        NVL(SELECT MIN(B_ED.EFFDT) 
            FROM BENEFITS B_ED
            WHERE B_ED.EMPLID = B.EMPLID
            AND B_ED.EFFDT > B.EFFDT
        , SYSDATE)
AND J.EFFDT = (SELECT MAX(J_ED.EFFDT)
               FROM JOBS J_ED
               WHERE J_ED.EMPLID = J.EMPLID
               AND J_ED.EFFDT <= D.THE_DATE)

与其说“检索每个日期并检查它是否符合条件”，我是否可以以某种方式更改逻辑以获得相同的结果，而无需翻阅这么多行？

Answer 1

是的;通过使用 LEAD()分析函数，可以计算jobs和benefits表中的下一个effdt，方便区间之间的查询。

就像是:

with dates as (select trunc(sysdate, 'yyyy') - 1 + level the_date,
                      to_number(to_char(trunc(sysdate, 'yyyy') - 1 + level, 'mm')) monthofyear,
                      to_number(to_char(sysdate, 'yyyy')) calendar_year
               from   dual
               connect by level <= 365),
      jobs as (select 123 emplid, to_date('01/02/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all
               select 123 emplid, to_date('30/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'I' hr_status from dual union all
               select 123 emplid, to_date('01/08/2015', 'dd/mm/yyyy') effdt, 901 deptid, 'A' hr_status from dual),
  benefits as (select 123 emplid, to_date('01/03/2015', 'dd/mm/yyyy') effdt, 'PPO' benefit_plan, 'A' status from dual union all
               select 123 emplid, to_date('31/07/2015', 'dd/mm/yyyy') effdt, null benefit_plan, 'I' status from dual union all
               select 123 emplid, to_date('01/09/2015', 'dd/mm/yyyy') effdt, 'HMO' benefit_plan, 'A' status from dual),
-- ********* end of mimicking your tables ********* --
         j as (select emplid,
                      effdt,
                      deptid,
                      hr_status,
                      lead(effdt, 1, sysdate) over (partition by emplid order by effdt) next_effdt
               from   jobs),
         b as (select emplid,
                      effdt,
                      benefit_plan,
                      status,
                      lead(effdt, 1, sysdate) over (partition by emplid order by effdt) next_effdt
               from   benefits)
select distinct j.emplid,
                d.calendar_year,
                d.monthofyear,
                j.deptid,
                b.benefit_plan
from   j
       inner join dates d on (d.the_date >= j.effdt and d.the_date < j.next_effdt)
       inner join b on (j.emplid = b.emplid)
where  d.the_date <= sysdate
and    d.the_date between to_date (:year_prompt || '01-01', 'YYYY-MM-DD')
                      and to_date (:year_prompt || '12-31', 'YYYY-MM-DD') -- if no index on d.the_date, maybe use trunc(the_date, 'yyyy') = :year_prompt
and    b.status = 'A'
and    d.the_date between b.effdt and b.next_effdt
order by 1, 4, 2, 3;

    EMPLID CALENDAR_YEAR MONTHOFYEAR     DEPTID BENEFIT_PLAN
---------- ------------- ----------- ---------- ------------
       123          2015           3        900 PPO         
       123          2015           4        900 PPO         
       123          2015           5        900 PPO         
       123          2015           6        900 PPO         
       123          2015           7        900 PPO         
       123          2015           9        901 HMO         
       123          2015          10        901 HMO         
       123          2015          11        901 HMO   

(显然，您可以从上述查询中排除 dates 、 jobs 和 benefits 子查询，因为您已经拥有这些表。它们仅存在于查询中以模拟具有包含该数据的表而无需实际创建表。)。

ETA:这是一个仅根据传入的年份计算 12 个月的版本，它将日期行减少到 12，而不是 365/366 行。

不幸的是，您仍然需要不同的，以考虑在同一个月开始有多行。

例如，对于以下示例中的数据，如果您删除了不同的数据，则第 6 个月的数据最终将有 3 行。但是，distinct 操作的行数将远少于以前。

with dates as (select add_months(to_date(:year_prompt || '-01-01', 'YYYY-MM-DD'), - 1 + level) the_date,
                      level monthofyear,
                      :year_prompt calendar_year -- assuming this is a number
               from   dual
               connect by level <= 12),
      jobs as (select 123 emplid, to_date('01/02/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all
               select 123 emplid, to_date('15/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'I' hr_status from dual union all
               select 123 emplid, to_date('26/06/2015', 'dd/mm/yyyy') effdt, 900 deptid, 'A' hr_status from dual union all
               select 123 emplid, to_date('01/08/2015', 'dd/mm/yyyy') effdt, 901 deptid, 'A' hr_status from dual),
  benefits as (select 123 emplid, to_date('01/03/2015', 'dd/mm/yyyy') effdt, 'PPO' benefit_plan, 'A' status from dual union all
               select 123 emplid, to_date('31/07/2015', 'dd/mm/yyyy') effdt, null benefit_plan, 'I' status from dual union all
               select 123 emplid, to_date('01/09/2015', 'dd/mm/yyyy') effdt, 'HMO' benefit_plan, 'A' status from dual),
-- ********* end of mimicking your tables ********* --
         j as (select emplid,
                      trunc(effdt, 'mm') effdt,
                      deptid,
                      hr_status,
                      trunc(coalesce(lead(effdt) over (partition by emplid order by effdt) -1, sysdate), 'mm') end_effdt
                        -- subtracting 1 from the lead(effdt) since here since the original sql had d.the_date < j.next_effdt and we need
                        -- to take into account when the next_effdt is the first of the month; we want the previous month to be displayed
               from   jobs),
         b as (select emplid,
                      trunc(effdt, 'mm') effdt,
                      benefit_plan,
                      status,
                      trunc(lead(effdt, 1, sysdate) over (partition by emplid order by effdt), 'mm') end_effdt
               from   benefits)
select distinct j.emplid,
                d.calendar_year,
                d.monthofyear,
                j.deptid,
                b.benefit_plan
from   j
       inner join dates d on (d.the_date between j.effdt and j.end_effdt)
       inner join b on (j.emplid = b.emplid)
where  d.the_date <= sysdate
and    b.status = 'A'
and    d.the_date between b.effdt and b.end_effdt
order by 1, 4, 2, 3;

    EMPLID CALENDAR_YEAR MONTHOFYEAR     DEPTID BENEFIT_PLAN                    
---------- ------------- ----------- ---------- --------------------------------
       123 2015                    3        900 PPO                             
       123 2015                    4        900 PPO                             
       123 2015                    5        900 PPO                             
       123 2015                    6        900 PPO                             
       123 2015                    6        900 PPO                             
       123 2015                    7        900 PPO                             
       123 2015                    9        901 HMO                             
       123 2015                   10        901 HMO                             
       123 2015                   11        901 HMO