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

Календарные таблицы реализуют компромисс между пространством и временем. Благодаря большему объему памяти некоторые типы запросов выполняются быстрее, поскольку они могут использовать преимущества индексов. Они безопасны до тех пор, пока вы соблюдаете осторожность с ограничениями CHECK() и пока у вас есть административные процессы для обработки любых ограничений, которые не поддерживает ваша СУБД.

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

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 миллионов строк «календарных» минут не займет много времени. Долгий обед. Может быть, полдень на солнце.

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

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

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

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

В двух других случаях я заранее знал, что отчеты не потребуются при детализации менее 15 минут. В этом случае для простоты я использовал одно измерение КАЛЕНДАРЯ с 96 записями в день.

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