Русский
Русский
English
Статистика
Реклама

Yet Another Производственный Календарь на MS SQL. ПО-gtША-gtГО-gtВО

Написать свое решение меня подтолкнул пост на хабре на аналогичную тему, в котором эта задача была решена в лоб простым перечислением дней и флагом рабочий/выходной за весь диапазон жизнедеятельности системы, в которой этот календарь используется. В аналогичной ситуации я решил поступить немного хитрее, что в итоге оказывается и гораздо проще в поддержке. Если интересно, как это было сделано welcome под кат:

У метода в лоб, кроме простоты, есть два достоинства 1) логарифмическая скорость поиска/выборки, т.к. сама дата является натуральным ПК, и выбрать признак выходного дня по набору дат операция на поле (кластерного) индекса, и 2) всего лишь незначительное снижение производительности при проверке рандомного набора (в диапазоне хранения, конечно) относительно непрерывного поддиапазона дат.

В итоге, если а) проверка даты может быть в широком диапазоне (хоть до Великой Октябрьской), б) в предикате проверки размах проверяемых дат не измеряется десятилетием, и в) не хочется возиться с тысячами записей в таблице, которые в 99% случаев не несут полезной информации, то можно сделать по-другому, а именно

вообще не хранить все возможные даты (чтобы не было и объекта, к которому применять index seek ;), синтезировать ось дат прямо на лету, по заданному диапазону, получать признак рабочий/выходной по номеру дня недели (Пн Пт: рабочий, Сб Вс: выходной), а исключения из этого правила хранить в компактной таблице.

Ну-с, приступим:

Создаем генератор оси дат в заданном диапазоне (пусть это будет inline table-valued function, для возможности переиспользования в разных применениях):
CREATE FUNCTION [dbo].[TF_Sys_DateGen](@MinDate DATE, @MaxDate DATE)RETURNS TABLE ASRETURN (WITH DR AS (SELECT @MinDate AS DateSeqUNION ALLSELECT DATEADD(DY, 1, R.DateSeq)FROM DR RWHERE DATEADD(DY, 1, R.DateSeq) <= @MaxDate)SELECT DR.DateSeqFROM DR)


Проверяем:
SELECT * FROM dbo.TF_Sys_DateGen('2020-06-20', '2020-07-01')

Закономерно получаем:



Теперь приклеиваем к выводу признак выходного дня по умолчанию:

ALTER FUNCTION [dbo].[TF_Sys_DateGen](@MinDate DATE, @MaxDate DATE)RETURNS TABLE ASRETURN (WITH DR AS (SELECT @MinDate AS DateSeqUNION ALLSELECT DATEADD(DY, 1, R.DateSeq)FROM DR RWHERE DATEADD(DY, 1, R.DateSeq) <= @MaxDate)SELECT DR.DateSeq,CONVERT(BIT, CASE WHEN DATEPART(WEEKDAY, DR.DateSeq) IN (7, 1) THEN 1 ELSE 0 END) AS IsHolidayFROM DR)


Уже теплее:



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

Что можно сделать, чтобы это обойти? Например, считать текущее значение DATEFIRST через переменную @@DATEFIRST (которая возвращает номер первого дня относительно дефолтного представления для США, в котором день с номером 1 воскресенье (!)) и далее вывести исходя из текущего значения @@DATEFIRST номера дней в текущей нотации, соотвествующие субботе и воскресенью. Засеките минуту и попробуйте сделать это в уме.

Получилось? У меня тоже нет. Я решил не брать еще минуту помощи клуба, и сделал по-другому получил дни недели в текущей нотации от дат, для которых я априори знаю, что это суббота и воскресенье например, 1 и 2 января 2000 года. И дальше просто сравнил номера выдаваемых дней с этими, априори известными в текущем контексте:

ALTER FUNCTION [dbo].[TF_Sys_DateGen](@MinDate DATE, @MaxDate DATE)RETURNS TABLE ASRETURN (WITH DR AS (SELECT @MinDate AS DateSeqUNION ALLSELECT DATEADD(DY, 1, R.DateSeq)FROM DR RWHERE DATEADD(DY, 1, R.DateSeq) <= @MaxDate)SELECT DR.DateSeq,CONVERT(BIT, CASE WHEN DATEPART(WEEKDAY, DR.DateSeq) IN (Q.DOW_Sat, Q.DOW_Sun) THEN 1 ELSE 0 END) AS IsHolidayFROM DRCROSS JOIN (SELECT TOP (1)DATEPART(WEEKDAY, '1/1/2000')AS DOW_Sat,-- достоверная субботаDATEPART(WEEKDAY, '1/2/2000')AS DOW_Sun-- достоверное воскресенье) Q)


Можно спросить, для чего все это вот CROSS JOIN / SELECT TOP (1) подмешано в запрос? А вот для чего: поскольку моя TVF inline, и тело функции по сути один SELECT, то никаких переменных вводить нельзя все, на что мы можем опираться в основном SELECT, можно либо хардкодить (а этого мы как раз и хотим избежать), либо выводить в subquery.
Поэтому применяем CROSS JOIN (можно INNER ON 1 = 1, кому как больше нравится).
TOP (1) здесь нужно больше SQL-движку, чем нам самим (ведь мы-то понимаем, что в subquery всегда одна строка!), но говорят, что для подобных конструкций TOP (1) подсказывает компилятору запроса, что subquery нужно выполнить один раз перед основным, а не выполнять подобно CROSS APPLY для каждой строки основного SELECT'а. Я в этом не уверен на 100%, особенно если тело subquery non-deterministic (как нибудь это проверю), но пусть пока будет так.

Проверяем:
SET DATEFIRST 5-- пусть в Солнечном городе у Незнайки неделя начинается с четвергаSELECT * FROM dbo.TF_Sys_DateGen('2020-06-20', '2020-07-01')

Бинго! Даже в Солнечном городе выходные суббота и воскресенье!


Теперь осталась тривиальщина наваять таблицу для хранения исключений и правильно ее сджойнить. Сделаем все в предположении, что мы автоматизируем глобальный бизнес, и праздничный день для одной страны может быть вполне себе серым рабочим буднем в другой:
CREATE TABLE CountryCalendarOverride (CountryID INT NOT NULL,CalendarDate DATE NOT NULL,IsHoliday BIT NOT NULL,       CONSTRAINT PK_CountryCalendarOverride PRIMARY KEY CLUSTERED        (  CountryID,  CalendarDate       ))


А что если мы хотим оставить лейбл к праздничном дню, чтобы не забывать, что мы празднуем? Или даже не переопределять признак рабочий/выходной, а просто оставить напротив какой-нибудь даты комментарий? Очень просто:
ALTER TABLE CountryCalendarOverride ALTER COLUMN IsHoliday BIT NULLGOALTER TABLE CountryCalendarOverride ADD DateComment NVARCHAR(256) NULL


Зачем нам теперь nullable IsHoliday? Затем, что мы однажды можем захотеть дать комментарий просто для определенной даты, не зная/не задумываясь о том, выходной она день или рабочий, и не собираясь переопределять именно этот флаг а просто сказать, что эта дата требует внимания.

Теперь все готово для финального спурта:
SET DATEFIRST 5-- пусть в Солнечном городе у Незнайки неделя начинается с четверга SELECT DX.DateSeq,ISNULL(CCO.IsHoliday, DX.IsHoliday) AS IsHoliday,CCO.DateCommentFROM dbo.TF_Sys_DateGen('2020-06-20', '2020-07-01') DXLEFT JOIN CountryCalendarOverride CCO ON DX.DateSeq = CCO.CalendarDate                      AND CCO.CountryID = 1-- Страна, в которой находится Солнечный городORDER BY DX.DateSeqOPTION (MAXRECURSION 0)


Данные в таблице за июнь:


Вуаля (результат):


На закуску вопрос. Что будет, если
           .  .  .           AND CCO.CountryID = 1-- Страна, в которой находится Солнечный город

заменить на
          .  .  .          WHERE CCO.CountryID = 1-- Страна, в которой находится Солнечный город

?

Есть еще один нюанс, связанный с CTE и MAXRECURSION из-за него во имя принципа ненарушения инкапсуляции приходится подобные TVF делать multi-statement вместо inline но об этом в следующий раз.
Источник: habr.com
К списку статей
Опубликовано: 22.06.2020 22:22:51
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Sql

Microsoft sql server

Sqlite

Table-valued function

Cte

Subquery

Генератор строк

Maxrecursion

Категории

Последние комментарии

© 2006-2020, personeltest.ru