Блог

У меня есть работники и месяцы, в которые они работали. Я хочу выяснить, у кого из работников был выходной. Рассмотрим простой пример:

На графике это выглядит так:

Желаемый результат:

  ----------- ----- 
| worker    | gap |
 ----------- ----- 
| worker001 | 1   |
 ----------- ----- 
| worker002 | 1   |
 ----------- ----- 
| worker003 | 0   |
 ----------- ----- 
  

Предположения:

• У каждого работника может быть разная дата начала и разная дата окончания.
• Таблица содержит только даты (месяцы), когда работник был на работе.

Я бы приветствовал запрос, но я был бы благодарен за просто идею, как это сделать. Моя идея заключается в следующем.

Проверьте минимальные и максимальные даты для каждого работника. Создайте последовательности месяцев между этими двумя днями для каждого работника (не знаю, как). Присоедините его к исходной таблице и проверьте, есть ли нули. Если это так, у нас есть пробелы.

Довольно простой способ подсчета пробелов — подсчитать количество месяцев и посмотреть на разницу между максимальным и минимальным:

 select worker,
       (datediff(month, min(month), max(month))   1) - count(*) as nummissing
from t
group by worker;
  

Примечание: для разницы в месяцах используется синтаксис SQL Server; это просто удобство, большинство других баз данных имеют аналогичную функциональность.

Я бы выбрал некоторый алгоритм оконной функции.

входные тестовые данные :

 create table tt (key varchar(10), dte date);

insert into tt values ('w1', '2017-12-01');
insert into tt values ('w1', '2017-11-01');
insert into tt values ('w1', '2015-12-01');
insert into tt values ('w1', '2015-11-01');
insert into tt values ('w1', '2016-01-01');
insert into tt values ('w1', '2016-02-01');
insert into tt values ('w1', '2016-05-01');
insert into tt values ('w1', '2016-06-01');
insert into tt values ('w2', '2016-02-01');
insert into tt values ('w2', '2016-03-01');
insert into tt values ('w2', '2016-05-01');
insert into tt values ('w3', '2016-01-01');
insert into tt values ('w3', '2016-02-01');
  

Затем мы используем оконную функцию для создания группы между датами для восстановления непрерывного рабочего времени.

Отсюда нам просто нужно выполнить count — 1 с другим выражением group by, чтобы получить количество пробелов.

 with tmp as (
 select key, 
 dte,
 dte - (row_number() over(partition by key order by dte) MONTHS) as rnk_month, 
 row_number() over(partition by key order by dte) as rnk_tot
 from tt)
select key, min(dte), max(dte), rnk_month 
from tmp
group by key, rnk_month
order by key, rnk_month
  

Попытка запроса с синтаксисом SQLServer… (не уверен, что это будет работать, пока у меня не будет sqll)

 with tmp as (
 select key, 
 dte,
 dateadd(month, - row_number() over(partition by key order by dte), dte)  as rnk_month, 
 row_number() over(partition by key order by dte) as rnk_tot
 from tt)
select key, min(dte), max(dte), rnk_month 
from tmp
group by key, rnk_month
order by key, rnk_month
  

Чтобы объяснить немного больше :

Функция: row_number() over(разделение по порядку ключей по dte) выдаст это для рабочего 1 :

 -----------------------------
Worker | Month     | rnk_tot
-----------------------------
w1     |2015-11-01 | 1
w1     |2015-12-01 | 2
w1     |2016-01-01 | 3
w1     |2016-02-01 | 4
w1     |2016-05-01 | 5
w1     |2016-06-01 | 6
w1     |2017-11-01 | 7
w1     |2017-12-01 | 8
  

Теперь, если мы вычтем дату с помощью rnk_tot, считая rnk_tot месяцем, у нас будет несколько новых дат, которые сформируют некоторую непрерывную группу :

 ----------------------------------------
Worker | Month     | rnk_tot | rnk_month
----------------------------------------
w1     |2015-11-01 | 1       |2015-10-01
w1     |2015-12-01 | 2       |2015-10-01
w1     |2016-01-01 | 3       |2015-10-01
w1     |2016-02-01 | 4       |2015-10-01
w1     |2016-05-01 | 5       |2015-12-01
w1     |2016-06-01 | 6       |2015-12-01
w1     |2017-11-01 | 7       |2017-04-01
w1     |2017-12-01 | 8       |2017-04-01
  

Отсюда вы можете выполнить группировку по столбцу worker и столбцу rnk_month для определения непрерывного рабочего времени (это то, что делает запрос, который я опубликовал) :

 ----------------------------------------
Worker | Mth Min   | Mth Max  | rnk_month
----------------------------------------
w1     |2015-11-01 |2016-02-01|2015-10-01
w1     |2016-05-01 |2016-06-01|2015-12-01
w1     |2017-11-01 |2017-12-01|2017-04-01
  

И отсюда вы можете получить количество пробелов.
Таким образом, окончательный запрос может быть :

 with tmp as (
 select key, 
 dateadd(month, - row_number() over(partition by key order by dte), dte) as rnk_month
 from tt)
select key, count(distinct rnk_month) - 1 as gaps
from tmp
group by key
  

Это даст этот результат для тестового примера, который я использовал :

 -----------------
Worker | Gaps
-----------------
w1     | 2
w2     | 1
w3     | 0