Решение задачи декомпозиции средствами SQL

Время прочтения: 5 мин.

Для того чтобы проверить соблюдение офисом режима работы необходимо иметь лог операций и данные по режиму работы. В нашем случае источник режима работы представлен в агрегированном виде (структура данных на рис.1). Для выявления отклонений в работе офиса, через объединение операций и режима работы, необходимо декомпозировать поля WorkWeekday (день недели) и WorkTime (режим работы) на строки. В скобках поля WorkTime указано стандартное время перерыва в работе подразделения.

В MS SQL Server для решения задачи декомпозиции есть удобная функция STRING_SPLIT(), которая разбивает строку на несколько с использованием выбранного разделителя. В нашем случае время работы разделено пробельными символами. Рассмотрим, как работает данная функция на примере одного офиса. Запрос на выборку данных (рис.2):

select * from tst.TST_WRKTIME where IdOffice = 3052620;

В функцию STRING_SPLIT передаётся первым параметром строка, вторым разделитель.

Пример результата работы функции (рис. 3).

select value from 
string_split('09:30 18:00(14:00 15:00) 09:30 18:00(14:00 15:00)',' ')

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

CREATE function [tst].[repl_time](@time_text nvarchar(500)) returns nvarchar(500)
	as begin
	declare @i int
	set @i = 1
	while @i < len(@time_text) 
	begin
		if
		(
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '%I__:__(%' or
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '%I__:__ __:__%' or
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '%I__:__ __:__ %' or
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '__:__I%' or 
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '%(__:__I__:__)%' or
		(select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) like '%I__:__'
		)
		and (select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) not like '%-__:__I%'
		and (select concat(left(@time_text, @i-1),'I',right(@time_text,len(@time_text)-@i))) not like '%)I%'
		begin
			set @time_text = concat(left(@time_text, @i-1),'-',right(@time_text,len(@time_text)-@i))
		end
	set @i = @i + 1

	end
	return @time_text
	end

В данной функции переменная @i представляет собой порядковый номер символа в строке. Функция по очереди меняет каждый символ на «I» (символ «I» был выбран поскольку он не встречается в этом поле и для исключения некорректных срабатываний LIKE) и меняет пробелы между диапазонами времени на дефис. Пробелы остаются только в случае перехода дня недели. Ниже приведем пример работы написанной функции (рис.4):

select [tst].[repl_time] ('09:30 18:00(14:00 15:00) 09:30 18:00(14:00 15:00)') as result

Теперь рабочая неделя явно разделилась на 2 дня по пробелу и можно применять STRING_SPLIT(). Используем «cross apply» для применения функции к столбцу. Пример для времени работы (рис.5):

select row_number() over (order by (select 1)) row_id, IdOffice,value value_
from tst.TST_WRKTIME   
cross apply string_split (tst.repl_time (WorkTime),' ')
where IdOffice = 3052620

Пример для разделения дней недели (рис 6.):

select row_number() over (order by (select 1)) row_id,IdOffice,value value_
from tst.TST_WRKTIME    
cross apply string_split (WorkWeekday,',')
where IdOffice = 3052620

Поле row_id в данном примере генерируем для связки данных по IdOffice и дню недели, значения которых вычисляются позиционно. В нашем случае расчеты производятся ежедневно за день t-1, поэтому было принято решение о создании функции, возвращающей табличное значение, которая будет выводить режим работы по всем подразделениям за одну дату. Дата будет преобразована в день недели и из данных о режиме работы фильтром будут выбраны нужные строки. Ниже приведен пример такой функции, @dt – дата для расчета:

CREATE function [tst].[TBL_WRKTIME](@dt date) RETURNS TABLE
AS

RETURN 
(select dt.IDOffice, @dt ddate, datepart(dw, @dt) wday,
cast(substring(tm.value_, 1, 5) as time(0)) sbeginclient,
cast(substring(tm.value_, 7, 5) as time(0)) sendclient,
iif(tm.value_ like '%(%',cast(substring(tm.value_,13,5) as time(0)),null) sbeginpause,
iif(tm.value_ like '%(%',cast(substring(tm.value_,19,5) as time(0)),null) sendpause
from (select row_number() over (order by (select 1)) row_id,IDOffice,
case when value = 'Пн.' then 1 
when value ='Вт.' then 2 
when value ='Ср.' then 3 
when value ='Чт.' then 4 
when value ='Пт.' then 5 
when value ='Сб.' then 6 
when value ='Вск.' then 7 end value_
from tst.TST_WRKTIME   
cross apply string_split (WorkWeekday,',')) dt,
(select row_number() over (order by (select 1)) row_id,IDOffice,value value_
from tst.TST_WRKTIME   
cross apply string_split (tst.repl_time (WorkTime),' ')) tm
where  dt.IDOffice = tm.IDOffice
and dt.row_id = tm.row_id
and dt.value_ = datepart(dw, @dt));
Вызов функции:
set datefirst 1;
select * from [tst].[TBL_WRKTIME]('2021-01-25')

Datefirst 1 означает, что при подсчете номер дня недели понедельник будет считаться первым днем.

Расшифровка полей:
ddate – отчетная дата

wday – порядковый номер дня недели (1-понедельник)

sbeginclient – время начала работы офиса

sendclient – время окончания работы офиса

sbeginpause – время начало перерыва

sendpause – время окончания перерыва

Далее эти данные можно использовать для проверки обслуживание клиентов в нерабочее время, опозданий сотрудников и другого несоблюдения режима рабочего дня. Однако, не стоит забывать про разницу часовых поясов подразделений, а также праздничные дни.