Написать свое решение меня подтолкнул пост на Хабре на аналогичную тему, в котором эта задача была решена «в лоб» — простым перечислением дней и флагом рабочий/выходной за весь диапазон жизнедеятельности системы, в которой этот календарь используется. В аналогичной ситуации я решил поступить немного хитрее, что в итоге оказывается и гораздо проще в поддержке. Если интересно, как это было сделано — welcome под кат:
У метода «в лоб», кроме простоты, есть два достоинства — 1) логарифмическая скорость поиска/выборки, т.к. сама дата является натуральным ПК, и выбрать признак выходного дня по набору дат — операция на поле (кластерного) индекса, и 2) всего лишь незначительное снижение производительности при проверке рандомного набора (в диапазоне хранения, конечно) относительно непрерывного поддиапазона дат.
В итоге, если а) проверка даты может быть в широком диапазоне (хоть до Великой Октябрьской), б) в предикате проверки размах проверяемых дат не измеряется десятилетием, и в) не хочется возиться с тысячами записей в таблице, которые в 99% случаев не несут полезной информации, то можно сделать по-другому, а именно…
… вообще не хранить все возможные даты (чтобы не было и объекта, к которому применять index seek ;), синтезировать ось дат прямо на лету, по заданному диапазону, получать признак рабочий/выходной по номеру дня недели (Пн… Пт: рабочий, Сб… Вс: выходной), а исключения из этого правила хранить в компактной таблице.
Ну-с, приступим:
Создаем генератор оси дат в заданном диапазоне (пусть это будет inline table-valued function, для возможности переиспользования в разных применениях):
Проверяем:
Закономерно получаем:
Теперь приклеиваем к выводу признак выходного дня по умолчанию:
Уже теплее:
Однако, есть маленькая закавыка: номер дня, возвращаемый встроенной функцией DATEPART, зависит от значения параметра DATEFIRST, а его дефолтное значение — в свою очередь от региональных настроек сервера. Поэтому надеться, что у нас американская система, и неделя начинается с воскресенья, в общем случаенепатриотично ненадежно.
Что можно сделать, чтобы это обойти? Например, считать текущее значение DATEFIRST через переменную @@DATEFIRST (которая возвращает номер первого дня относительно дефолтного представления для США, в котором день с номером 1 — воскресенье (!)) и далее вывести исходя из текущего значения @@DATEFIRST номера дней в текущей нотации, соотвествующие субботе и воскресенью. Засеките минуту и попробуйте сделать это в уме.
Получилось? У меня тоже нет. Я решил не брать еще минуту помощи клуба, и сделал по-другому — получил дни недели в текущей нотации от дат, для которых я априори знаю, что это суббота и воскресенье — например, 1 и 2 января 2000 года. И дальше просто сравнил номера выдаваемых дней с этими, априори известными в текущем контексте:
Можно спросить, для чего все это вот 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 (как нибудь это проверю), но пусть пока будет так.
Проверяем:
Бинго! Даже в Солнечном городе выходные — суббота и воскресенье!
Теперь осталась тривиальщина — наваять таблицу для хранения исключений и правильно ее сджойнить. Сделаем все в предположении, что мы автоматизируем глобальный бизнес, и праздничный день для одной страны может быть вполне себе серым рабочим буднем в другой:
А что если мы хотим оставить лейбл к праздничном дню, чтобы не забывать, что мы празднуем? Или даже не переопределять признак рабочий/выходной, а просто оставить напротив какой-нибудь даты комментарий? Очень просто:
Зачем нам теперь nullable IsHoliday? Затем, что мы однажды можем захотеть дать комментарий просто для определенной даты, не зная/не задумываясь о том, выходной она день или рабочий, и не собираясь переопределять именно этот флаг — а просто сказать, что эта дата требует внимания.
Теперь все готово для финального спурта:
Данные в таблице за июнь:
Вуаля (результат):
На закуску вопрос. Что будет, если
заменить на
?
Есть еще один нюанс, связанный с CTE и MAXRECURSION — из-за него во имя принципа ненарушения инкапсуляции приходится подобные TVF делать multi-statement вместо inline — но об этом в следующий раз.
У метода «в лоб», кроме простоты, есть два достоинства — 1) логарифмическая скорость поиска/выборки, т.к. сама дата является натуральным ПК, и выбрать признак выходного дня по набору дат — операция на поле (кластерного) индекса, и 2) всего лишь незначительное снижение производительности при проверке рандомного набора (в диапазоне хранения, конечно) относительно непрерывного поддиапазона дат.
В итоге, если а) проверка даты может быть в широком диапазоне (хоть до Великой Октябрьской), б) в предикате проверки размах проверяемых дат не измеряется десятилетием, и в) не хочется возиться с тысячами записей в таблице, которые в 99% случаев не несут полезной информации, то можно сделать по-другому, а именно…
… вообще не хранить все возможные даты (чтобы не было и объекта, к которому применять index seek ;), синтезировать ось дат прямо на лету, по заданному диапазону, получать признак рабочий/выходной по номеру дня недели (Пн… Пт: рабочий, Сб… Вс: выходной), а исключения из этого правила хранить в компактной таблице.
Ну-с, приступим:
Создаем генератор оси дат в заданном диапазоне (пусть это будет inline table-valued function, для возможности переиспользования в разных применениях):
CREATE FUNCTION [dbo].[TF_Sys_DateGen](@MinDate DATE, @MaxDate DATE)
RETURNS TABLE AS
RETURN (
WITH DR AS (
SELECT @MinDate AS DateSeq
UNION ALL
SELECT DATEADD(DY, 1, R.DateSeq)
FROM DR R
WHERE DATEADD(DY, 1, R.DateSeq) <= @MaxDate
)
SELECT
DR.DateSeq
FROM 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 AS
RETURN (
WITH DR AS (
SELECT @MinDate AS DateSeq
UNION ALL
SELECT DATEADD(DY, 1, R.DateSeq)
FROM DR R
WHERE 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 IsHoliday
FROM DR
)
Уже теплее:
Однако, есть маленькая закавыка: номер дня, возвращаемый встроенной функцией DATEPART, зависит от значения параметра DATEFIRST, а его дефолтное значение — в свою очередь от региональных настроек сервера. Поэтому надеться, что у нас американская система, и неделя начинается с воскресенья, в общем случае
Что можно сделать, чтобы это обойти? Например, считать текущее значение DATEFIRST через переменную @@DATEFIRST (которая возвращает номер первого дня относительно дефолтного представления для США, в котором день с номером 1 — воскресенье (!)) и далее вывести исходя из текущего значения @@DATEFIRST номера дней в текущей нотации, соотвествующие субботе и воскресенью. Засеките минуту и попробуйте сделать это в уме.
Получилось? У меня тоже нет. Я решил не брать еще минуту помощи клуба, и сделал по-другому — получил дни недели в текущей нотации от дат, для которых я априори знаю, что это суббота и воскресенье — например, 1 и 2 января 2000 года. И дальше просто сравнил номера выдаваемых дней с этими, априори известными в текущем контексте:
ALTER FUNCTION [dbo].[TF_Sys_DateGen](@MinDate DATE, @MaxDate DATE)
RETURNS TABLE AS
RETURN (
WITH DR AS (
SELECT @MinDate AS DateSeq
UNION ALL
SELECT DATEADD(DY, 1, R.DateSeq)
FROM DR R
WHERE 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 IsHoliday
FROM DR
CROSS 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 NULL
GO
ALTER TABLE CountryCalendarOverride ADD DateComment NVARCHAR(256) NULL
Зачем нам теперь nullable IsHoliday? Затем, что мы однажды можем захотеть дать комментарий просто для определенной даты, не зная/не задумываясь о том, выходной она день или рабочий, и не собираясь переопределять именно этот флаг — а просто сказать, что эта дата требует внимания.
Теперь все готово для финального спурта:
SET DATEFIRST 5 -- пусть в Солнечном городе у Незнайки неделя начинается с четверга
SELECT
DX.DateSeq,
ISNULL(CCO.IsHoliday, DX.IsHoliday) AS IsHoliday,
CCO.DateComment
FROM dbo.TF_Sys_DateGen('2020-06-20', '2020-07-01') DX
LEFT JOIN CountryCalendarOverride CCO ON DX.DateSeq = CCO.CalendarDate
AND CCO.CountryID = 1 -- Страна, в которой находится Солнечный город
ORDER BY DX.DateSeq
OPTION (MAXRECURSION 0)
Данные в таблице за июнь:
Вуаля (результат):
На закуску вопрос. Что будет, если
. . .
AND CCO.CountryID = 1 -- Страна, в которой находится Солнечный город
заменить на
. . .
WHERE CCO.CountryID = 1 -- Страна, в которой находится Солнечный город
?
Есть еще один нюанс, связанный с CTE и MAXRECURSION — из-за него во имя принципа ненарушения инкапсуляции приходится подобные TVF делать multi-statement вместо inline — но об этом в следующий раз.
