Таблицы календаря в PostgreSQL 9

Я создаю базу данных аналитики (у меня есть четкое понимание данных и бизнес-целей и только базовые навыки работы с базой данных).

Я столкнулся с некоторыми ссылками на создание похожих складов, которые реализуют концепцию "таблиц календаря". Это имеет смысл и достаточно легко сделать. Однако большинство примеров, которые я вижу, представляют собой таблицы календаря, которые ограничивают область до "дня". Мои данные необходимо проанализировать до уровня часа. Возможно, минут.

Мой вопрос: будет ли реализация таблиц календаря для гранулярности по часовому/минутному уровню быть полезной с точки зрения эффективности пространства и скорости запроса/сортировки? Если да, можете ли вы рекомендовать структуру таблицы и метод/пример популяции?

Моя основная таблица данных будет содержать 20+ миллионов строк данных в любой момент времени, а типичные подмножества для анализа - в диапазоне от 1 до 5 миллионов. Итак, как вы можете видеть, это много полей метки времени.

Ответ 1

Календарные таблицы реализуют компромисс пространства/времени. Используя больше места, некоторые виды запросов выполняются за меньшее время, потому что они могут использовать индексы. Они безопасны до тех пор, пока вы будете осторожны с ограничениями CHECK(), и до тех пор, пока у вас есть административные процессы, чтобы заботиться о любых ограничениях, которые ваши dbms не поддерживают.

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

2011-01-01 00:00:00
2011-01-01 00:01:00
2011-01-01 00:02:00
2011-01-01 00:03:00
2011-01-01 00:04:00

Если вы делаете анализ "ковша", вам может быть лучше с чем-то вроде этого.

bucket_start         bucket_end
--
2011-01-01 00:00:00  2011-01-01 00:01:00
2011-01-01 00:01:00  2011-01-01 00:02:00
2011-01-01 00:02:00  2011-01-01 00:03:00
2011-01-01 00:03:00  2011-01-01 00:04:00
2011-01-01 00:04:00  2011-01-01 00:05:00

Так как оператор SQL BETWEEN включает конечные точки, вам обычно нужно избегать его использования. Это потому, что оно включает конечные точки, и трудно выразить bucket_end как "bucket_start плюс одна минута, минус наименьший бит времени, который этот сервер может распознать". (Опасность - это значение, которое на микросекунду больше, чем bucket_end, но все же меньше следующего значения для bucket_start.)

Если бы я собирался построить эту таблицу, я бы сделал это так. (Хотя я подумал бы о том, должен ли я назвать это "календарем".)

create table calendar (
  bucket_start timestamp primary key,
  bucket_end timestamp unique,
  CHECK (bucket_end = bucket_start + interval '1' minute)
  -- You also want a "no gaps" constraint, but I don't think you 
  -- can do that in a CHECK constraint in PostgreSQL. You might
  -- be able to use a trigger that counts the rows, and compares
  -- that count to the number of minutes between min(bucket_start)
  -- and max(bucket_start). Worst case, you can always run a report
  -- that counts the rows and sends you an email.
);

Ограничение UNIQUE создает неявный индекс в PostgreSQL.

Этот запрос будет вставлять строки в один день (24 часа * 60 минут) за раз.

insert into calendar
select coalesce(
                (select max(bucket_start) from calendar), 
                 cast('2011-01-01 00:00:00' as timestamp)
               ) 
             + cast((n || 'minute') as interval) as bucket_start, 
       coalesce(
                (select max(bucket_start) from calendar), 
                 cast('2011-01-01 00:00:00' as timestamp)
               ) 
             + cast((n + 1 || ' minute') as interval) as bucket_end
from generate_series(1, (24*60) ) n;

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

Не требуется слишком много времени для генерации 20 миллионов строк для тестирования и еще 20 миллионов строк "календарных" минут. Длинный обед. Может быть, днем на солнце.

Ответ 2

В PostgreSQL вы можете генерировать календарные таблицы произвольной длины и детализации на лету:

SELECT  CAST('2011-01-01' AS DATE) + (n || ' hour')::INTERVAL
FROM    generate_series(0, 23) n

Это не требует рекурсии (как и для других систем) и является предпочтительным методом генерации летучих результатов.

Ответ 3

В хранилищах данных, которые я построил, я использовал отдельные измерения CALENDAR и TIME_OF_DAY. Первое измерение имеет зернистость на 1 день, а вторая - 1 минута зернистости.

В двух других случаях я знал заранее, что для детализации менее 15 минут не потребуется никаких отчетов. В этом случае для простоты я использовал одно измерение CALENDAR с 96 записями в день.

Я использовал этот подход на складах Oracle до сих пор, но этим летом я мог бы участвовать в проекте хранилища PostgreSQL.