Блог

Я пытаюсь объединить записи истории сотрудников и получить минимальную дату начала и максимальную дату окончания, когда в других столбцах измерения нет других изменений (Сотрудник, Отдел, Работа, Статус должности).

Ввод:

Выход:

Скрипт для создания таблиц и заполнения данных:

 create table EmployeeHistory (EmployeeHistoryID INT,
                              EmployeeID INT,
                              DepartmentID  INT,
                              JobID INT,
                              PositionStatusID  INT,
                              StartDate DATE,
                              EndDate DATE)

insert into EmployeeHistory values (123, 362880, 450, 243, 1, '2019-05-28', '2020-05-03')
insert into EmployeeHistory values (124, 362880, 450, 243, 2, '2020-05-04', '2020-08-20')
insert into EmployeeHistory values (125, 362880, 450, 243, 1, '2020-08-21', '2020-08-31')
insert into EmployeeHistory values (126, 362880, 450, 243, 1, '2020-09-01',  '2021-09-23')
insert into EmployeeHistory values (127, 362881, 450, 243, 1, '2019-07-01', '2019-07-31')
insert into EmployeeHistory values (128, 362881, 450, 243, 1, '2019-08-01',  '2021-09-23')
 

Когда я использую аналитические функции или группируюсь, это объединение строк 1, 3 и 4, но я хочу объединить только 3 и 4, так как все остальные столбцы одинаковы. Несмотря на то, что строка 1 совпадает с 3 и 4, для сохранения истории строка 1 не должна быть объединена в 3 и 4 в этом сценарии.

Пример кода, который я использую:

 select distinct *
  from (select MAX(EmployeeHistoryID) OVER (PARTITION BY EmployeeID, DepartmentID, JobID, PositionStatusID)  AS EmployeeHistoryID,
               EmployeeID,
               DepartmentID,
               JobID,
               PositionStatusID,
               MIN(StartDate) OVER (PARTITION BY EmployeeID, DepartmentID, JobID, PositionStatusID)  AS StartDate,
               MAX(EndDate) OVER (PARTITION BY EmployeeID, DepartmentID, JobID, PositionStatusID)  AS EndDate
          from EmployeeHistory) m
 

Это тип проблемы пробелов и островов (жанр проблем, связанных с объединением смежных строк с аналогичной информацией).

В ваших данных ваши записи по каждому сотруднику идеально «складываются» вместе. Здесь нет пробелов. Датой начала для одной строки является дата окончания плюс один день предыдущей строки для сотрудника.

Это позволяет решить проблему только с помощью оконных функций. Отказ от агрегирования обычно является преимуществом в производительности. Идея состоит в том, чтобы найти первую строку, в которой есть изменение, сохранить эту строку и рассчитать дату окончания. Существует небольшое осложнение в отношении окончательной даты окончания:

 select eh.EmployeeHistoryID, eh.EmployeeID, eh.DepartmentID, eh.JobID, eh.PositionStatusID, eh.StartDate,
       lead(dateadd(day, -1, StartDate), 1, max_EndDate) over (partition by EmployeeId order by StartDate) as EndDate
from (select eh.*,
             lag(StartDate) over (partition by EmployeeID order by StartDate) as prev_StartDate,
             lag(StartDate) over (partition by EmployeeID, DepartmentID, JobID, PositionStatusID order by StartDate) as prev_StartDate_same,
             max(EndDate) over (partition by EmployeeId) as max_EndDate
      from EmployeeHistory eh
     ) eh
where prev_StartDate_same is null or prev_StartDate_same <> prev_StartDate
order by EmployeeHistoryID;
 

Вот скрипка db<>.

Если я правильно понял, это легко достижимо с помощью group by. Посмотрите, соответствует ли это ожиданиям:

 SELECT Max(employeehistoryid) AS EmployeeHistoryID,
       employeeid,
       departmentid,
       jobid,
       positionstatusid,
       Min(startdate)         AS StartDate,
       Max(enddate)           AS EndDate
FROM   employeehistory
GROUP  BY employeeid,
          departmentid,
          jobid,
          positionstatusid