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