Блог

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

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

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

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

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

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

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

Таблицы календаря реализуют компромисс между пространством и временем. Используя больше места, некоторые виды запросов выполняются за меньшее время, поскольку они могут использовать преимущества индексов. Они безопасны до тех пор, пока вы соблюдаете ограничения 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.
);
  

Ограничение УНИКАЛЬНОСТИ создает неявный индекс в 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 миллионов строк «календарных» минут. Долгий обед. Может быть, день на солнце.

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

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

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