Блог

У меня есть Postgres DB под управлением версии 7.4 (да, мы находимся в процессе обновления)

У меня есть четыре отдельных запроса для получения ежедневных, ежемесячных, годовых и пожизненных записей

 SELECT COUNT(field)
FROM database
WHERE date_field
    BETWEEN DATE_TRUNC('DAY' LOCALTIMESTAMP) 
    AND DATE_TRUNC('DAY' LOCALTIMESTAMP)   INTERVAL '1 DAY'
  

Для месяца просто замените слово DAY на MONTH в запросе и так далее для каждой продолжительности времени.

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

Заранее спасибо!

ПРИМЕЧАНИЕ: date_field — это временная метка без часового пояса

Обновить:

Извините, я отфильтровываю записи с дополнительными ограничениями запроса, просто хотел передать суть сравнений date_field. Извините за любую путаницу

У меня есть некоторое представление об использовании подготовленных инструкций и простой таблицы статистики (record_count_t) для этого:

 -- DROP TABLE IF EXISTS record_count_t;
-- DEALLOCATE record_count;
-- DROP FUNCTION updateRecordCounts();

CREATE TABLE record_count_t (type char, count bigint);
INSERT INTO record_count_t (type) VALUES ('d'), ('m'), ('y'), ('l');

PREPARE record_count (text) AS
UPDATE record_count_t SET count =
(SELECT COUNT(field)
FROM database
WHERE
CASE WHEN $1 <> 'l' THEN
    DATE_TRUNC($1, date_field) = DATE_TRUNC($1, LOCALTIMESTAMP)
ELSE TRUE END)
WHERE type = $1;

CREATE FUNCTION updateRecordCounts() RETURNS void AS
$$
    EXECUTE record_count('d');
    EXECUTE record_count('m');
    EXECUTE record_count('y');
    EXECUTE record_count('l');
$$
LANGUAGE SQL;

SELECT updateRecordCounts();
SELECT type,count FROM record_count_t;
  

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

Я бы предположил, что оптимизировать это дальше, чем это уже есть, невозможно.

Если вы собираете статистику за день / месяц / год, как я предполагаю, вы делаете, одним из вариантов (после обновления, конечно) является оператор with и соответствующие объединения, например:

 with daily_stats as (
(what you posted)
),
monthly_stats as (
(what you posted monthly)
),
etc.
select daily_stats.stats,
       monthly_stats.stats,
       etc.
stats
left join yearly_stats on ...
left join monthly_stats on ...
left join daily_stats on ...
  

Однако на самом деле это будет работать хуже, чем выполнение каждого запроса отдельно в производственной среде, потому что вы введете в БД левые соединения, которые можно было бы точно так же выполнять в промежуточном программном обеспечении (т. Е. Показывать ежедневную, затем ежемесячную, затем годовую и, наконец, статистику за время жизни). (Если не лучше, так как вы будете избегать полного сканирования таблицы.)

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

Ого! Не делайте этого!!! Не потому, что вы не можете сделать то, что вы просите, а потому, что вам, вероятно, не следует делать то, что вы просите таким образом. Я предполагаю, что причина, по которой вы получили date_field в вашем примере, заключается в том, что у вас есть date_field привязанные к пользователю или некоторым другим метаданным. данные.

Подумайте об этом: вы просите PostgreSQL отсканировать 100% записей, относящихся к данному пользователю. Если это не одноразовая операция, вы почти наверняка не захотите этого делать. Если это одноразовая операция, и вы планируете кэшировать это значение как метаданные, то кого волнует оптимизация? Пространство дешево и сэкономит вам кучу времени выполнения в будущем.

Вы должны добавить 4 поля метаданных для каждого пользователя (или что бы это ни было), которые помогают суммировать данные. У вас есть два варианта, я расскажу вам, как это использовать, чтобы вы сохраняли исторические подсчеты, но вот простая версия:

 CREATE TABLE user_counts_only_keep_current (
  user_id , -- Your user_id
  lifetime INT DEFAULT 0,
  yearly INT DEFAULT 0,
  monthly INT DEFAULT 0,
  daily INT DEFAULT 0,
  last_update_utc TIMESTAMP WITH  TIME ZONE,
  FOREIGN KEY(user_id) REFERENCES "user"(id)
);
CREATE UNIQUE INDEX this_tbl_user_id_udx ON user_counts_only_keep_current(user_id);
  

Настройте некоторые хранимые процедуры, которые обнуляют отдельные столбцы, если last_update_utc не соответствует текущему дню в соответствии с NOW() . Здесь вы можете проявить творческий подход, но увеличение количества записей, подобных этой, будет правильным решением.

Обработка данных временных рядов в любой реляционной базе данных требует специальной обработки и обслуживания. Загляните в наследование таблиц PostgreSQL, если вам нужно хорошее управление временными данными …. но на самом деле, не делайте того, что вы собираетесь сделать со своим приложением, потому что это почти наверняка приведет к плохим результатам ™.