По-ефективен начин за намиране на служители с покритие между две дати

Имам изискване да извлека списък със служители и за всеки служител списък с месеци, в които са били активно в покритието на обезщетенията през дадена година. Има таблица с данни за работата и таблица с информация за предимствата. Има и таблица с доставени дати, която изброява всяка дата от 2007-2018 г. и за всяка дата показва деня от месеца, месеца от годината и календарната година.

Начинът, по който написах заявката сега, е да кажа: намерете всички дати в таблицата с дати, които са 1) между 01/01 и 12/31 от подканата година (или текущата дата, която от двете е по-стара), 2) по време на времето, през което служителят е бил активен в таблицата с доходи. За всяка дата искам също дептида от таблицата с работни места и плана за доходи от таблицата с доходи към тази дата. След това правя отделен, показващ само месеца от годината и календарната година за всеки служител.

Това работи, но проблемът идва, когато се опитам да го направя за отдели с много хора в тях. Отнема много време, за да се изпълнява, вярвам, защото извлича до 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)

Вместо да казвам „извлечете всяка отделна дата и проверете дали отговаря на критериите“, мога ли да променя логиката по някакъв начин, за да получа същите резултати, без да преминавам през толкова много редове?


sql oracle peoplesoft
person Community    schedule 05.11.2015    source източник
comment
вие cross joinкачите dates и jobs маси.   -  person Vamsi Prabhala    schedule 05.11.2015
comment
изглежда така, но не, има условие за свързване между двете таблици въз основа на последния предикат. Хитро, хитро!   -  person Boneist    schedule 05.11.2015

Отговори (1)


arrow_upward
1
arrow_downward

Да; като използвате аналитичната функция LEAD(), можете да изчислите следващия 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 реда.

За съжаление, все още ще ви е необходим различен, за да вземете предвид, когато имате няколко реда, започващи в един и същи месец.

Например, с данните в следния пример, ще получите 3 реда за месец 6, ако премахнете distinct. Въпреки това, броят на редовете, върху които 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
person Boneist    schedule 05.11.2015
comment
Този SQL е интересен за подзаявката за създаване на таблицата dates. Ще трябва да го запазя, в случай че мога да го използвам по-късно. Въпреки това, той все още извлича всяка дата и проверява дали отговаря на критериите. Например, ако премахнете ключовата дума distinct от заявката, се връщат 219 реда. Моята цел е да попреча на SQL да се разбърква през толкова много редове. Има ли някакъв начин да получите същите резултати, без да използвате ключовата дума distinct? - person ; 05.11.2015
comment
Дух; Исках да проверя отделната част, но забравих! Ще погледна отново това утре. - person Boneist; 05.11.2015
comment
Оценявам вашата помощ, това ми беше трудно да разбера. Не го казах конкретно във въпроса, въпреки че вероятно трябваше, че допусках, че това изобщо може да се направи без таблицата dates. Мислех, че може да има някакъв начин да разгледам диапазоните в таблицата benefits и да изляза със списъка с месеците, в които са били активни. Но може да не е възможно без да използвате таблицата с дати и да питате дали тази дата отговаря на критерия на всеки ред. - person ; 05.11.2015
comment
Напълно възможно е да избегнете таблицата с дати, въпреки че това е малко по-сложно. Това, което можете да направите междувременно, е да използвате аналитичната функция row_number() срещу таблицата с дати на trunc(the_date, 'mm') и след това да я обедините с таблиците за работни места и предимства (където ‹row_num› = 1) (въпреки че вие Ще трябва също да съкратя началните/крайните дати до началото на месеца. - person Boneist; 05.11.2015
comment
Добре, актуализирах отговора си с друго решение. За съжаление, не мисля, че ще можете да премахнете различния (освен ако не можете да гарантирате, че таблиците за работа/доходи никога няма да имат повече от един ред с effdt в един и същи месец). - person Boneist; 06.11.2015
comment
Благодаря, че отделихте време да работите върху това. Не е точно това, което исках, но звучи сякаш това, което искам, може наистина да не е възможно. Така че ще продължа и ще маркирам вашия като отговор. Благодаря! - person ; 09.11.2015