SQL: встроенный способ обнаружения пробелов в произвольной серии дат?

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

Одним из ключей этих кеш-таблиц будет поле даты. Я хочу убедиться, что у меня нет пропусков в какой-либо серии дат, которые я использую - например, я не хочу иметь записи за 1 и 3 января, в то время как у 2 нет строки.

Я не могу придумать способ реализовать это с помощью любого из реляционных инструментов в СУБД. Я мог бы создать таблицу дат, чтобы убедиться, что связанные записи не выходят за пределы ряда дат - чтобы запретить 2 января, если в моей таблице "ряд дат" не было соответствующей строки - но я не не вижу, как автоматически убедиться, что я не пропускаю даты в диапазоне.

Существует ли автоматический способ сделать это в определении схемы базы данных? Или мне придется проверять пробелы в моем коде?

Я использую MySQL, но это похоже на RDBMS-независимость.

Источник

3 ответа

Решение

Я не знаю встроенного способа сделать это, но способ, которым я сам справился, состоит в том, чтобы сгенерировать таблицу дат. У меня есть статическая таблица tbDatesи когда мне нужно сообщить о диапазоне дат, и я не хочу пропускать даты, для которых у меня могут отсутствовать данные, я делаю:

select *
from 
    tbDates d
    left join myTable t
        on d.date = t.date

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

Источник

Это отличный пример полезности таблицы чисел (http://www.projectdmx.com/tsql/tblnumbers.aspx).

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

SELECT DATEADD(d, a.id, '2012-01-01')
FROM NumbersTable

С этим запросом все просто, как ЛЕВОЕ ПОДКЛЮЧЕНИЕ к вашим данным, и вы можете увидеть, какие даты отсутствуют.

В качестве альтернативы, вместо того, чтобы использовать таблицу чисел, я хотел бы создать таблицу дат. На самом деле, вы можете заполнить таблицу дат только рабочими днями (не выходными). Легко!

Источник

Нет способа сделать это автоматически, но вы можете сгенерировать даты и сохранить их во временной таблице.

Это может быть сделано в хранимой процедуре:

  • создать временную таблицу
  • вставьте значения от начальной до конечной даты (каждый день)
  • выберите свои результаты и присоединитесь к временной таблице
  • выбрать только те строки, которые не соответствуют временной таблице
Источник
Другие вопросы по тегам