Как я писал в прошлом посте (Различия между T-SQL и PL/SQL), в прошлом семестре я проходил курс по хранилищам данных. Изначально я собирался написать больше постов, но времени не было. В черновике уже долгое время лежит несколько постов, но когда я их доделаю и опубликую — не знаю. Но сегодня руки дошли до второго поста из этой серии. В нём мы рассмотрим функцию, которая возвращает таблицу со всеми днями между двумя датами.
Кто знаком с концепцией хранилищя данных, знает, что в каждом хранилище существует таблица измерения времени. Часто рекомендуют создать отдельную таблицу для календаря, которая будет содержать все нужные даты и из неё потом выбираются с помощью запроса select нужные годы, месяцы, дни, часы, минуты или даже секунды, если речь идёт, например, о телефонных звонках, ограничиваются условием where и добавляются в измерение времени. Но на мой взгляд такой подход — это костыль и нормальная система управления базами данных должна содержать такой функционал, как говорится, «из коробки». Для заполнения этого измерения я написал функцию, чтобы обойтись без такого клендаря.
Чтобы написать такую функцию, мне нужно было узнать, как в SQL Server можно сделать две вещи: Как получить все дни между двумя датами? И как вернуть таблицу из функции? На оба вопроса я нашёл ответ на StackOverflow. Скомбинировав эти две вещи, я написал свою функцию.
CREATE FUNCTION get_orderdates() RETURNS @returnTable TABLE(orderdate DATETIME) AS BEGIN DECLARE @min_date DATETIME; DECLARE @max_date DATETIME; SELECT @min_date = min(orderdate) FROM ( SELECT min(DATEADD(YEAR, 2, orderdate) ) AS orderdate FROM data_ds1.sales.salesorderheader UNION SELECT min(orderdate) AS orderdate FROM data_ds2.dbo.orders ) AS tbl; SELECT @max_date = max(orderdate) FROM ( SELECT max(DATEADD(YEAR, 2, orderdate) ) AS orderdate FROM data_ds1.sales.salesorderheader UNION SELECT max(orderdate) AS orderdate FROM data_ds2.dbo.orders ) AS tbl; WITH AllDays AS ( SELECT @min_date AS Date UNION ALL SELECT DATEADD(DAY, 1, Date) FROM AllDays WHERE Date < @max_date ) INSERT INTO @returnTable SELECT Date FROM AllDays OPTION (MAXRECURSION 0); RETURN; END;
Если что, мне нужно было свести данные из двух источников (data_ds1, data_ds2) в одной схеме (звезде). В первом источнике нужные данные находились в таблице salesorderheader, во втором в таблице orders. Кроме того, данные в первом источнике были ошибочными и их нужно было "подвинуть" на два года, что я делаю с помощью функции DATEADD. Я выбираю самую маленькую и самую большую дату из обоих источников, и присваиваю эти значения переменным @min_date и @max_date. Потом в обобщённом табличным выражении начиная с минимальной и заканчивая максимальной датой я прибавляю по одному дню и добавляю в таблицу, которая будет возвращаться функцией.
Понравился пост? Поделись в соцсетях и подписывайся на аккаунты в Twitter и Facebook!