Меню

Ms sql генератор непрерывного интервала дат



Как получить последовательность дат в указанном промежутке на T-SQL

Новости

Linux

Базы данных

Курсы

Книги

Всем привет! Сегодня мы поговорим о том, как на языке T-SQL можно сформировать последовательность дат в указанном диапазоне, т.е. когда требуется получить все даты между двумя определенными датами, при этом чтобы каждое значение даты в результирующем наборе данных было в отдельной строке.

Допустим, Вам требуется вывести все даты, начиная с 01.01.2020 по 12.01.2020, иными словами, Вам необходимо сформировать следующую таблицу.

dt
01.01.2020
02.01.2020
03.01.2020
04.01.2020
05.01.2020
06.01.2020
07.01.2020
08.01.2020
09.01.2020
10.01.2020
11.01.2020
12.01.2020

И первое, что может прийти в голову, это использовать или оператор UNION, или конструктор табличных значений VALUES, и в этом конкретном случае, когда требуется сформировать всего 12 записей, это может показаться достаточно простой задачей, однако представим, что нам требуется сформировать даты за большой промежуток времени, например за год, или за несколько лет, тогда этот способ сразу отпадает, так как вручную формировать тысячи строк, наверное, как минимум очень трудоемко, т.е. не очень эффективно. А если еще представить, что нам требуется формировать такие списки дат постоянно и динамически, т.е. начало и конец периода постоянно будут меняться, то такой ручной способ точно не подходит.

Поэтому сейчас мы рассмотрим способы для автоматической генерации последовательности дат.

Способы реализации генерации последовательности дат

В интернете можно встретить решения, которые подразумевают использование вспомогательных таблиц, однако в языке T-SQL все это можно сделать без каких-то внешних вспомогательных инструментов, т.е. с использованием только стандартных конструкций языка.

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

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

Способ 1 – использование цикла WHILE

Первый способ подразумевает использование обычного цикла WHILE.

В отличие от ситуаций, когда нам требуется сформировать последовательность чисел или просто набор тестовых данных, эту тему мы рассматривали в отдельном материале – Как сформировать на языке T-SQL большое количество строк, в данном случае использовать цикл можно, так как даже если нам потребуется сформировать последовательности дат за несколько веков, у нас получится всего несколько десятков тысяч записей, которые сгенерируются достаточно быстро, тем более такое скорей всего будет требоваться только в каких-то частных случаях.

Итак, вот инструкция T-SQL, которая создает табличную функцию для генерации последовательности дат.

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

Способ 2 – использование рекурсивного обобщенного табличного выражения

Второй, альтернативный способ генерации последовательности дат, подразумевает использование рекурсивного обобщенного табличного выражения.

Данная табличная функция работает точно так же как и предыдущая, и принимает ровно те же самые параметры.

Пример использования функций для генерации последовательности дат

Теперь, когда у нас есть функция для генерации последовательности дат, давайте представим, что нам необходимо сформировать последовательность дат за 2020 год, т.е. нам нужны даты в промежутке начиная с 01.01.2020 и заканчивая 31.12.2020.

В итоге у нас должно быть 366 записей, т.е. отдельная запись для каждого дня года (в 2020 году 366 дней, так как это високосный год).

Таким образом, чтобы получить данную последовательность дат, мы обращаемся к нашей табличной функции и передаём в нее соответствующие значения (начало и конец года).

В результате мы получили то, что нам и было нужно.

Читайте также:  Как правильно подключить генератор камаз 5320

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

На сегодня это все, надеюсь, материал был Вам полезен, пока!

Источник

sql — генерировать дни из диапазона дат

Я хотел бы выполнить запрос как

И вернуть данные как:

Решение

Это решение использует нет циклов, процедур или временных таблиц. Подзапрос генерирует даты за последние 10 000 дней и может быть расширен, чтобы перейти так далеко назад или вперед, как вы хотите.

Выход:

Примечания по производительности

Тестирование это Вот , производительность на удивление хорошая: вышеуказанный запрос занимает 0,0009 сек.

Если мы расширим подзапрос для генерации ок. 100 000 чисел (и, следовательно, около 274 лет дат), это работает в 0,0458 сек.

Кстати, это очень переносимый метод, который работает с большинством баз данных с небольшими изменениями.

Другие решения

Вот еще один вариант с использованием представлений:

И тогда вы можете просто сделать (посмотреть, как это элегантно?):

Обновить

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

Принятый ответ не работает для PostgreSQL (синтаксическая ошибка в или около «a»).

То, как вы делаете это в PostgreSQL, с помощью generate_series функция, т.е.

Используя рекурсивное выражение общих таблиц (CTE), вы можете создать список дат, а затем выбрать его. Очевидно, что обычно вы не захотите создавать три миллиона дат, так что это просто иллюстрирует возможности. Вы можете просто ограничить диапазон дат внутри CTE и опустить предложение where в операторе select, используя CTE.

На Microsoft SQL Server 2005 создание списка CTE всех возможных дат заняло 1:08. Генерация ста лет заняла меньше секунды.

Старое школьное решение сделать это без цикла / курсора — создать NUMBERS таблица, которая имеет один столбец Integer со значениями, начинающимися с 1.

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

Когда у вас есть NUMBERS Таблицу можно использовать:

Абсолютное низкотехнологичное решение будет:

Что бы вы использовали для этого?

Сформировать списки дат или номеров для того, чтобы присоединиться к. Вы должны сделать это, чтобы увидеть, где есть пропуски в данных, потому что вы ЛЕВЫЕ ПРИСОЕДИНЯЕТЕСЬ к списку последовательных данных — нулевые значения сделают очевидным, где существуют пропуски.

Для доступа 2010 — требуется несколько шагов; Я следовал той же схеме, что и выше, но думал, что смогу помочь кому-то в Access. Отлично сработало для меня, мне не нужно было хранить таблицу с датами.

Создайте таблицу с именем DUAL (аналогично тому, как работает таблица Oracle DUAL)

  • ID (AutoNumber)
  • DummyColumn (Text)
  • Добавить значения одной строки (1, «DummyRow»)

Создайте запрос с именем «ZeroThru9Q»; вручную введите следующий синтаксис:

Создайте запрос с именем «TodayMinus1KQ» (для дат до сегодняшнего дня); вручную введите следующий синтаксис:

Создайте запрос с именем «TodayPlus1KQ» (для дат после сегодняшнего дня); вручную введите следующий синтаксис:

Создайте объединенный запрос с именем «TodayPlusMinus1KQ» (для дат +/- 1000 дней):

Теперь вы можете использовать запрос:

Thx Pentium10 — вы заставили меня присоединиться к stackoverflow 🙂 —
это мое портирование на msaccess — думаю, оно будет работать на любой версии:

ссылающиеся на MSysObjects просто «потому что для доступа нужен счетчик таблиц» как минимум в 1 записи, в предложении from — подойдет любая таблица с хотя бы 1 записью.

Источник

Последовательности значений даты и времени в T-SQL

В самых различных ситуациях работы с данными требуется генерировать последовательности дат и времени между заданными на входе точками начала @start и ©end и с заданным интервалом (например, 1 день, 12 часов и т.п.). За примерами таких ситуаций не нужно далеко ходить — наполнение размерности времени в хранилище данных, планирование запуска приложений и т.п.

Читайте также:  Прокладки для дизельных генераторов

Эффективным средством решения этой задачи является описанная в предыдущей статье функция GetNums. На вход поступают начальные и конечные дата и время — ©start и ©end, и с применением функции DATEDIFF вычисляется, сколько интервалов нужной величины помещается в заданный диапазон. Далее вызывается функция GetNums со следующими входными данными: ©low — «0», a ©high равно вычисленной на предыдущем этапе разнице. Наконец для получения результирующих даты и времени к @start добавляется умноженный на n временной интервал.

Вот пример генерации последовательности дат в диапазоне с 1 по 12 февраля 2012 года:

Если интервал является кратным определенной единице времени, например 12 часов, используйте эту единицу (в данном случае час) при вычислении разницы между @start и @end, и разделите результат на 12, чтобы получить @high, а затем умножьте n на 12, чтобы получить число часов, которые нужно добавить к @start для вычисления результирующих значений даты и времени. В качестве примера, следующий код генерирует последовательность значений даты и времени между 12 и 18 февраля 2012 года с 12-часовым интервалом между значениями последовательности:

Источник

Генерация последовательности дат и generate_series в PostgreSQL

Данная статья может оказаться сферическим примером велосипедостроения. Если вам известно стандартное или более изящное решение задачи, то буду рад увидеть его в комментариях.

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

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

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

gs
31.01.2018
28.02.2018
28.03.2018
28.04.2018
28.05.2018

Результат оказался столь же неожиданным, как и логичным. Функция generate_series по честному итерационно сгенерировала последовательность дат по принципу последовательного прибавления сдвига к предыдущему значению. При этом на каждом шаге проверялась корректность и правка полученной даты. 31 февраля не бывает, поэтому дата преобразовалась в 28 февраля и дальнейшее прибавление месяца сбила всю последовательность на 28 число.

UPD. Пояснения после вопросов в комментариях. Вообще изначальная задача стоит шире — группировать данные на произвольные дни месяца. Например, сгруппировать по 20-м числам каждого месяца, по 15-м числам, но с такими датами проблем при генерации не наблюдается. Механизм, который мы ищем должен одинаково хорошо строить последовательность 10-х чисел каждого месяца, 21-х чисел и корректно отрабатывать концы месяцев.

Интересно как поведет себя операция сложения с несколькими месяцами сразу? Что будет если мы будем прибавлять интервал не итерационно, а «оптом»?

В этом случае прибавление производится по честному.
Как применяя этот подход сгенерировать нужные даты?

Если известно количество месяцев, то очень просто:

gs
31.01.2018
28.02.2018
31.03.2018
30.04.2018
31.05.2018

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

Следующий код представляет собой в некоторой степени макетную плату и не претендует на изящность, первые варианты запросов мы в компании пишем с упором на гибкость и взаимозаменяемость блоков

gs
31.01.2018
28.02.2018
31.03.2018
30.04.2018
31.05.2018

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

Вариант 2.
Спустя время меня осенило, что последовательная генерация дат по сути рекурсивная процедура. Только не в чистом виде, так как в нашем случае расчет следующей даты от предыдущей приводит к первоначальной проблеме. Зато на каждом шаге мы можем увеличивать интервал, прибавляемый к началу нашего периода:

gs
31.01.2018
28.02.2018
31.03.2018
30.04.2018
31.05.2018

Данный запрос корректно работает с любыми входными временными отрезками и интервалами.

Источник

Как найти самый длинный непрерывный ряд событий с помощью SQL

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

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

Нетрудно понять, что они означают. Зайдите на сайт в первый день. Затем на второй день. Затем на третий (возможно несколько раз, это не имеет значения). Не зашли на четвёртый? Начинаем считать заново.

Как отследить это с помощью SQL?

Для доступа к данным мы будем использовать Stack Exchange Data Explorer.

Обратите внимание, что мы не будем запрашивать даты посещений, так как эта информация не предоставляется. Вместо этого, давайте запросим даты размещённых пользователем сообщений.

В качестве базы данных используется SQL Server, следовательно мы можем использовать следующий запрос:

…который выдаст что-то подобное:

Как можно заметить, есть несколько пропусков:

Человеку легко увидеть, сколько дней подряд идут даты без пропусков. Но как сделать это посредством SQL?

Чтобы упростить задачу, давайте «сохраним» индивидуальные запросы в обобщённых табличных выражениях. Предыдущий запрос мы назовём dates:

Теперь цель полученного запроса — поместить все последовательные даты в одну и ту же группу, чтобы мы могли объединить их. Вот как мы это сделаем:

Мы хотим объединить каждую группу «grp» и подсчитать количество дат в группе, а также найти минимум и максимум в каждой группе.

Создание групп для последовательных дат

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

Как можно видеть, независимо от того, что существует разрыв между датами (две даты не являются последовательными), их номера строк по-прежнему будут последовательными. Мы можем сделать это с помощью функции ROW_NUMBER():

Теперь давайте посмотрим вот такой интересный запрос:

Приведённый выше запрос даёт нам следующий результат:

Все, что мы сделали, это вычли номер строки из дня, чтобы получить новую дату «grp». Полученная таким образом дата не имеет смысла, это просто вспомогательное значение.

Однако, мы можем гарантировать, что для последовательных дат, значение «grp» будет одинаковое, потому что для всех последовательно идущих дат, следующие два уравнения верны:

Для непоследовательных дат, разница в номерах строк будет также 1, но разница в днях будет больше единицы. Группы теперь можно легко различить:

Таким образом, финальный запрос будет следующим:

И его результат:

Бонус: найти последовательность недель

То, что мы использовали дни — это просто наш выбор. Мы взяли точное время и округлили его до дня с помощью функции CAST:

Если бы мы хотели узнать последовательность, например, из недель, мы могли бы округлять время до недель:

Этот запрос использует численное выражение года и недели и создаёт числа типа 201503 для третьей недели 2015 года. Остальная часть запроса остаётся без изменений:

И вот что мы получим:

Неудивительно, что последовательные недели охватывают гораздо более длинные диапазоны, так как автор регулярно пишет на Stack Overflow.

Источник