Второй раз в жизни сталкиваюсь с необходимостью отслеживания перемещения людей по датам.
Это полезно, например, для отслеживания движения людей по карьерной лестнице, занимаемых пациентами коек в больнице... да мало ли вариантов.
Обычно это выглядит как таблица следующего вида:
| Человек | Позиция1 | Позиция2 | ПозицияN | Дата |
Совокупность этих позиций определяет точное положение человека в структуре на дату. Например, так:
| ФИО | Должность | Отдел | Филиал | Дата | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 28.09.2012 | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 29.09.2012 |
Понятно, что для разных баз данных структура может меняться. Например, вместо одной даты, может быть две: Дата начала и Дата окончания. Но это не слишком влияет на алгоритм, применяющийся для выполнения задачи, которая звучит следующим образом: определить периоды, в которые сотрудник занимал каждую должность.
Вполне логичным выглядит решение группировкой:
| T-SQL | 1
2
3
4
5
6
7
8
9
| select
ФИО,
Должность,
Отдел,
Филиал,
min(дата) as ДатаНачала,
max(Дата) as ДатаОкончания
from Таблица
Group by ФИО, Должность, Отдел, Филиал |
|
| ФИО | Должность | Отдел | Филиал | ДатаНачала | ДатаОкончания | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | 29.09.2012 |
Но в реальной жизни может быть и такая ситуация:
| ФИО | Должность | Отдел | Филиал | Дата | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | | Иванов И.И. | Начальник | Отдел продаж | Москва | 28.09.2012 | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 29.09.2012 |
И запрос нам вернёт следующее:
| ФИО | Должность | Отдел | Филиал | ДатаНачала | ДатаОкончания | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | 29.09.2012 | | Иванов И.И. | Начальник | Отдел продаж | Москва | 28.09.2012 | 28.09.2012 |
Что, мягко говоря, правдой не является. Так что простой группировкой тут не обойдешься. Особенно, если нужно считать стаж работника в определённой должности.
Благодаря помощи invm, получилось для каждого положения сотрудника сделать идентификатор.
Как это работает: для начала пронумеруем записи в таблице по каждому сотруднику по дате:
| T-SQL | 1
| row_number() over (partition by ФИО order by Дата) as RN1 |
|
| T-SQL | 1
| row_number() over (partition by ФИО, Должность, Отдел, Филиал order by Дата) as RN2 |
|
| RN1 | RN2 | ФИО | Должность | Отдел | Филиал | Дата | | 1 | 1 | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | | 2 | 1 | Иванов И.И. | Начальник | Отдел продаж | Москва | 28.09.2012 | | 3 | 2 | Иванов И.И. | Менеджер | Отдел продаж | Москва | 29.09.2012 |
Далее нужно из RN1 вычесть RN2 и получить идентификатор:
| T-SQL | 1
2
3
4
5
6
7
8
9
| select
row_number() over (partition by ФИО order by Дата) - row_number() over (partition by ФИО, Должность, Отдел, Филиал order by Дата) as gr_id
ФИО,
Должность,
Отдел,
Филиал,
min(дата) as ДатаНачала,
max(Дата) as ДатаОкончания
from Таблица |
|
| gr_id | ФИО | Должность | Отдел | Филиал | Дата | | 0 | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | | 1 | Иванов И.И. | Начальник | Отдел продаж | Москва | 28.09.2012 | | 1 | Иванов И.И. | Менеджер | Отдел продаж | Москва | 29.09.2012 |
И делаем выборку с группировкой, в т.ч. по нашему идентификатору:
| T-SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| with tab as (select
row_number() over (partition by ФИО order by Дата) - row_number() over (partition by ФИО, Должность, Отдел, Филиал order by Дата) as gr_id
ФИО,
Должность,
Отдел,
Филиал,
min(дата) as ДатаНачала,
max(Дата) as ДатаОкончания
from Таблица)
select
ФИО,
Должность,
Отдел,
Филиал,
min(дата) as ДатаНачала,
max(Дата) as ДатаОкончания
from tab
Group by ФИО, Должность, Отдел, Филиал, gr_id |
|
| ФИО | Должность | Отдел | Филиал | ДатаНачала | ДатаОкончания | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 27.09.2012 | 27.09.2012 | | Иванов И.И. | Начальник | Отдел продаж | Москва | 28.09.2012 | 28.09.2012 | | Иванов И.И. | Менеджер | Отдел продаж | Москва | 29.09.2012 | 29.09.2012 |
В качестве эпилога добавлю, что в моей практике запрос получился гораздо более громоздким за счёт того, что в таблице хранились идентификаторы должностей, а сами должности еще и по категориям делились. Поэтому потребовались некоторые дополнительные преобразования. Тем не менее, представленный алгоритм более, чем рабочий. Я давно собираю простые и изящные решения нетривиальных задач. Надеюсь, со временем этот список дополнится.
|
