Форум программистов, компьютерный форум, киберфорум
Наши страницы
solbrain
Войти
Регистрация
Восстановить пароль
Рейтинг: 5.00. Голосов: 1.

Чтобы не потерять...

Запись от solbrain размещена 18.03.2019 в 14:32
Обновил(-а) solbrain 18.03.2019 в 18:05 (Категория)

Второй раз в жизни сталкиваюсь с необходимостью отслеживания перемещения людей по датам.
Это полезно, например, для отслеживания движения людей по карьерной лестнице, занимаемых пациентами коек в больнице... да мало ли вариантов.

Обычно это выглядит как таблица следующего вида:
Человек Позиция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

В качестве эпилога добавлю, что в моей практике запрос получился гораздо более громоздким за счёт того, что в таблице хранились идентификаторы должностей, а сами должности еще и по категориям делились. Поэтому потребовались некоторые дополнительные преобразования. Тем не менее, представленный алгоритм более, чем рабочий. Я давно собираю простые и изящные решения нетривиальных задач. Надеюсь, со временем этот список дополнится.
Размещено в SQL
Просмотров 187 Комментарии 0
Всего комментариев 0
Комментарии
 
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Рейтинг@Mail.ru