Более эффективный способ поиска сотрудников с охватом между двумя датами

У меня есть требование получить список сотрудников, а для каждого сотрудника список месяцев, в течение которых они активно получали льготы в данном году. Существует таблица с данными о работе и таблица с информацией о преимуществах. Существует также таблица дат доставки, в которой перечислены все даты с 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
...

Таблица вакансий

(А=активно; я=неактивно)

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, если вы удалите отдельные. Однако количество строк, над которыми работает отдельный объект, будет намного меньше, чем раньше.

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