Truncate postgresql date: PostgreSQL date_trunc Function By Examples

postgresql — квартал postgres date_trunc с настраиваемым месяцем начала

Задавать вопрос

спросил 2 года, 1 месяц назад

Изменено 2 года, 1 месяц назад

Просмотрено 2к раз

Я пытаюсь создать среднее квартальное количество очков игроков, однако поведение postgres 9 по умолчанию0013 date_trunc(‘четверть’, источник) заключается в том, что первый квартал начинается с ГГГГ-01-01.

Есть ли какой-либо способ или обходной путь, который я могу сделать, чтобы сказать, что первый месяц первого квартала, например, сентябрь? Итак, вместо традиционного: Q1: 1-3, Q2: 4-6, Q3: 7-9, Q4: 10-12

я хочу иметь возможность указать, какой месяц является началом Q1, поэтому, если я скажу Сентябрь это должно быть: Q1: 9-11, Q2: 12-2, Q3: 3-5, Q4: 6-8

Вот как я делаю стандартный квартальный средний балл с кварталом по умолчанию.

 ВЫБЕРИТЕ идентификатор,
       имя,
       date_trunc('квартал', дата) КАК дата,
       AVG(ранг) AS ранг,
       AVG(оценка) как оценка,
       страна,
       устройство
ОТ player_daily_score
СГРУППИРОВАТЬ ПО id, имя, 3, страна, устройство
ЗАКАЗАТЬ ПО 3 уб.;
 

Я открыт для всех предложений, чтобы заставить это работать.

• postgresql
• статистика

0

Если вам действительно не нужна дата начала квартала, вы можете легко сделать это с помощью выражения CASE:

 ВЫБЕРИТЕ идентификатор,
       имя,
       случай
         при извлечении (месяц с даты) в (9,10,11), затем «Q1»
         при извлечении (месяц с даты) в (12,1,2), затем «Q2»
         при извлечении (месяц с даты) в (3,4,5), затем «Q3»
         при извлечении (месяц из даты) в (6,7,8), затем «Q4»
       конец как четверть,
       AVG(ранг) AS ранг,
       AVG(оценка) как оценка,
       страна,
       устройство
ОТ player_daily_score
СГРУППИРОВАТЬ ПО id, имя, 3, страна, устройство
ЗАКАЗАТЬ ПО 3 уб.
  ;
 

Конечно, вы можете поместить это в хранимую функцию, чтобы упростить ваши запросы.

0

На самом деле вы не используете номер квартала, только метки времени. Итак, все, что вам нужно, это смещение , которое вы вычитаете, а затем добавляете обратно после усечения, что составляет interval '2 month' для вашего примера:

 SELECT id, date
     , date_trunc('квартал', дата - интервал '2 месяца')
                                  + интервал "2 месяца" AS квартал
ОТ player_daily_score;
 

db<>скрипка здесь

Однако ваш запрос не имеет смысла. Включение столбца id (при условии, что это PK) в GROUP BY является шумовой операцией, так как ничего не будет агрегировано. Это могло бы иметь смысл:

 SELECT date_trunc('квартал', дата - интервал '2 месяца')
                                  + интервал "2 месяца" AS квартал
     , страна
     , устройство
     , AVG(ранг) AS avg_rank
     , AVG(оценка) AS avg_score
ОТ player_daily_score
СГРУППИРОВАТЬ ПО 1,2,3
ЗАКАЗАТЬ ПО 1 уб;
 

Если id не является ПК, дизайн вашей базы данных может выиграть от нормализации.

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

.

Получение даты последнего изменения таблицы базы данных PostgreSQL

Нет достоверной и достоверной записи о времени последнего изменения таблицы. Использование relfilenode неправильно по многим причинам:

• Записи сначала записываются в журнал записывающей головки (WAL), затем лениво в кучу (табличные файлы). Как только запись находится в WAL, Pg не спешит записывать ее в кучу, и она может даже не записываться до следующей системной контрольной точки;

• Большие таблицы имеют несколько ответвлений, вам придется проверить все ответвления и выбрать самую новую отметку времени;

• Простой SELECT может генерировать активность записи в базовую таблицу из-за установки бита подсказки;

• autovaccum и другое обслуживание, которое не изменяет видимые пользователю данные, по-прежнему изменяет файлы отношений;

• некоторые операции, такие как vaccum full , заменят relfilenode.

  Это может быть не так, как вы ожидаете, если вы пытаетесь просмотреть его одновременно, не используя соответствующую блокировку.

Несколько вариантов

Если вам не нужна надежность, вы потенциально можете использовать информацию в pg_stat_database и pg_stat_all_tables . Они могут дать вам время последнего сброса статистики и статистику активности с последнего сброса статистики. Он не сообщает вам, когда была самая последняя активность, только то, что это было с момента последнего сброса статистики, и нет информации о том, что произошло до этого сброса статистики. Так что это ограничено, но это уже есть.

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

Чуть менее ужасная альтернатива — использовать ПРОСЛУШАТЬ и УВЕДОМИТЬ . Подключите внешний процесс демона к PostgreSQL и LISTEN для событий. Используйте ON INSERT OR UPDATE OR DELETE запускает отправку NOTIFY s при изменении таблицы с oid таблицы в качестве полезной нагрузки уведомления. Они отправляются, когда транзакция фиксируется. Ваш демон может накапливать уведомления об изменениях и лениво записывать их обратно в таблицу базы данных. Если система дает сбой, вы теряете записи о самых последних изменениях, но это нормально, вы просто обрабатываете все таблицы как только что измененные, если запускаете после сбоя.

Чтобы избежать худших проблем с параллелизмом, вместо этого вы можете регистрировать временные метки изменений, используя перед вставкой, обновлением, удалением или усечением имени таблицы для каждого оператора выполняется триггер , обобщенный для использования отношения oid в качестве параметра. Это вставит пару (relation_oid, timestamp) в таблицу регистрации изменений. Затем у вас есть вспомогательный процесс в отдельном соединении или периодически вызываемый вашим приложением, который объединяет эту таблицу для получения последней информации, объединяет ее в сводную таблицу самых последних изменений и усекает таблицу журнала. Единственное преимущество этого подхода по сравнению с подходом «прослушивание/уведомление» заключается в том, что он не теряет информацию при сбое, но при этом он еще менее эффективен.

Другим подходом может быть написание функции расширения C, которая использует (например) ProcessUtility_hook , ExecutorRun_hook и т. д. для перехвата изменений в таблице и ленивого обновления статистики. Я не смотрел, насколько это практично; взгляните на различные параметры _hook в исходниках.

Лучшим способом было бы исправить код статистики для записи этой информации и отправить исправление в PostgreSQL для включения в ядро. Не начинайте просто с написания кода; поднимите свою идею на -hackers, как только вы обдумаете ее достаточно, чтобы иметь четко определенный способ сделать это (т.е. начните с чтения кода, а не просто спрашивайте «как мне…»). Было бы неплохо добавить время последнего обновления к pg_stat_... , но вам нужно будет убедить сообщество, что это того стоило, или предоставить способ опционального отслеживания — и вам придется написать код, чтобы сохранить статистику и отправить патч , потому что этим будет заниматься только тот, кому нужна эта функция.

Как бы я это сделал

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

Обновление для PostgreSQL 9.5 метки времени фиксации

Обновление : PostgreSQL 9.5 имеет метки времени фиксации . Если вы включили их в postgresql.