Проблема Oracle SQL Group

Я пытаюсь суммировать таблицу сотрудников, в которой существует несколько записей, пока сотрудник находится в одной команде. Я попытался сгруппировать по Min / Max Over Partition By и Lead / Lag по названию команды, но каждый результат заканчивается агентом, который перешел из одной команды, а затем в более поздний срок обратно в исходную группу команды как одно событие, хотя Сортирую по датам.

Пример базы данных:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 02/JAN/19

John Smith    | 123123      | Team A      | Site A   | 02/JAN/19  | 03/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 04/JAN/19

John Smith    | 123123      | Team A      | Site A   | 04/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 05/JAN/19  | 06/JAN/19

Когда я запускаю пример запроса:

SELECT
Employee Name
,Employee ID
,Team Leader
,Location
,MIN(Start Date) OVER(PARTITION BY Team Leader ORDER BY Employee ID, Start Date) AS Starting Date
,MAX(End Date) OVER(PARTITION BY Team Leader ORDER BY Employee ID, End Date) AS End Date
FROM TABLE 1

Результаты приведены ниже:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 06/JAN/19

Может ли помочь в достижении желаемых результатов:

Employee Name | Employee ID | Team Leader | Location | Start Date | End Date

John Smith    | 123123      | Team A      | Site A   | 01/JAN/19  | 03/JAN/19

John Smith    | 123123      | Team B      | Site A   | 03/JAN/19  | 04/JAN/19

John Smith    | 123123      | Team A      | Site A   | 04/JAN/19  | 05/JAN/19

John Smith    | 123123      | Team B      | Site A   | 05/JAN/19  | 06/JAN/19

sql oracle gaps-and-islands
person gkaess    schedule 11.07.2019    source источник
comment
Ищите пробелы и острова. Можно найти много сообщений   -  person Serg    schedule 11.07.2019
comment
Это повторяющийся вопрос, хотя мне сложно найти ответ, который здесь лучше всего подходит. Решение похоже на здесь.   -  person Ponder Stibbons    schedule 11.07.2019
comment
@PonderStibbons Я протестировал аналогичную логику из предоставленной вами ссылки, и, похоже, она работает. Я проверю, когда добавлю в запрос больше сотрудников. Спасибо!   -  person gkaess    schedule 11.07.2019

Ответы (3)


arrow_upward
0
arrow_downward

Это похоже на форму промежутков и островков, где записи связаны по диапазонам дат.

Вот один из методов, который использует left join, чтобы найти, где начинаются острова, а затем совокупную сумму для идентификации групп и агрегации:

select employeename, employeeid, teamleader, location,
       min(startdate), max(enddate)
from (select t1.*,
             sum(case when tprev.employeeid is null  -- new group
                      then 1 else 0
                 end) over (partition by employeeid, teamleader, location
                            order by startdate
                           ) as grouping
      from table1 t1 left join
           table1 tprev
           on t1.startdate = tprev.enddate and
              t1.employeeid = tprev.employeeid and
              t1.teamleader = tprev.teamleader and
              t1.location = tprev.location
     ) t
group by employeeid, teamleader, location, grouping
order by employeeid, min(startdate);
person Gordon Linoff    schedule 11.07.2019
comment
Большое спасибо, похоже, это решило мою проблему и правильно сгруппировало агент. - person gkaess; 12.07.2019

arrow_upward
2
arrow_downward

Вот один вариант:

  • test CTE представляет ваши данные (немного упрощено)
  • полезный код начиная со строки №8 и далее.
SQL> with test (ename, team, start_date, end_date) as
  2    (select 'John', 'A', date '2019-01-01', date '2019-01-02' from dual union all
  3     select 'John', 'A', date '2019-01-02', date '2019-01-03' from dual union all
  4     select 'John', 'B', date '2019-01-03', date '2019-01-04' from dual union all
  5     select 'John', 'A', date '2019-01-04', date '2019-01-05' from dual union all
  6     select 'John', 'B', date '2019-01-05', date '2019-01-06' from dual
  7    ),
  8  temp as
  9    (select ename, team, start_date, end_date,
 10       row_number() over (order by start_date) rn,
 11       row_number() over (partition by ename, team order by start_date) rna
 12     from test
 13    )
 14  select ename, team, min(start_date) start_date, max(end_date) end_date
 15  from temp
 16  group by ename, team, (rn - rna)
 17  order by 3;

ENAM T START_DATE  END_DATE
---- - ----------- -----------
John A 01/jan/2019 03/jan/2019
John B 03/jan/2019 04/jan/2019
John A 04/jan/2019 05/jan/2019
John B 05/jan/2019 06/jan/2019

SQL>
person Littlefoot    schedule 11.07.2019

arrow_upward
2
arrow_downward

Если у вас версия 12c или более поздняя, ​​сопоставление с образцом строк является хорошим альтернативным решением. В отличие от решений типа «пробелы и острова», я тоже забочусь о перекрытиях. Предложение WITH содержит тестовые данные, после чего запускается решение.

with test (ename, team, start_date, end_date) as
 (select 'John', 'A', date '2019-01-01', date '2019-01-02' from dual union all
  select 'John', 'A', date '2019-01-02', date '2019-01-03' from dual union all
  select 'John', 'B', date '2019-01-03', date '2019-01-04' from dual union all
  select 'John', 'A', date '2019-01-04', date '2019-01-05' from dual union all
  select 'John', 'B', date '2019-01-05', date '2019-01-06' from dual
 )
select * from test
match_recognize(
  partition by ename, team order by start_date
  measures first(start_date) start_date, last(end_date) end_date
  pattern(a b*)
  define b as start_date <= a.end_date
)
order by ename, start_date;

ENAM T START_DATE       END_DATE        
---- - ---------------- ----------------
John A 2019-01-01 00:00 2019-01-03 00:00
John B 2019-01-03 00:00 2019-01-04 00:00
John A 2019-01-04 00:00 2019-01-05 00:00
John B 2019-01-05 00:00 2019-01-06 00:00
person Stew Ashton    schedule 11.07.2019