Группировать и подсчитывать события по временным интервалам, а также промежуточный итог

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

Простым вариантом задачи будет таблица с тремя полями:

    Column    |           Type           |              Modifiers
--------------+--------------------------+-------------------------------------
 period_start | timestamp with time zone | not null
 user_id      | text                     | not null
 action       | text                     | not null

Строка действия, которую я хочу захватить, может быть «create_entry» (да, я знаю, что это отстой, как хороший дизайн БД, но я застрял с этим)

Результат, который я ищу, — это отчет, показывающий количество действий «create_entry» по годам и месяцам. Что-то типа:

 Year | Month | Entries
------+-------+----------
 2013 |  12   | 14345
 2014 |   1   | 9876
 2014 |   2   | 10234

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

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

ИЗМЕНИТЬ

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


postgresql sql loops window-functions aggregate-functions
person PerryW    schedule 26.02.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Если я правильно понимаю, вы просто хотите GROUP BY годы и месяцы в своей таблице для каждой строки, которая имеет action из create_entry:

SELECT
  DATE_PART('YEAR', period_start) as Year,
  DATE_PART('MONTH', period_start) as Month,
  COUNT(*) as Entries
FROM activity_log
WHERE action = 'create_entry'
GROUP BY Year, Month;

скрипт SQL

person Mike Christensen    schedule 26.02.2014
comment
Вот почему я люблю StackOverflow — DATE_PART и date_trunc — я никогда их раньше не видел - person PerryW; 27.02.2014
comment
Не уверен, что между ними есть какая-то разница. @Эрвин знал бы, он гораздо больший эксперт, чем я. - person Mike Christensen; 27.02.2014
comment
Ой. DATE_PART возвращает число (2013, 2 и т. д.), а DATE_TRUNC возвращает отметку времени, усеченную до этой части. Итак, мой путь будет больше похож на вывод вашего примера. - person Mike Christensen; 27.02.2014
comment
Иногда я ненавижу выбирать предпочтительный ответ... Оба отличные, оба +1. Я не могу сказать, какой из них «лучший», я подозреваю, что это, вероятно, @Erwin. Но я выбираю этот, потому что это решение, которое я действительно использовал. - person PerryW; 28.02.2014
comment
@PerryW: я предложил date_trunc(), отклоняясь от предложенного вами формата результата, потому что агрегирование по одному столбцу дешевле, чем по двум столбцам. Однако наиболее интересным моментом является текущий счетчик, и для этого ему нужен только один уровень запроса. - person Erwin Brandstetter; 28.02.2014

arrow_upward
3
arrow_downward

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

Чтобы получить текущий счет, который вы упомянули в своем дополнительном вопросе, добавьте оконную функцию:

SELECT to_char(date_trunc('month', period_start), 'Mon YYYY') AS month
     , count(*) AS month_ct
     , sum(count(*)) OVER (ORDER BY date_trunc('month', period_start)) AS running_ct
FROM   activity_log
WHERE  action = 'create_entry'
GROUP  BY date_trunc('month', period_start);

sqlfiddle для Psotgres 9.6
db‹>fiddle здесь для Postgres 12

Оконные функции выполняются после агрегатных функций, поэтому мы можем запускать оконную функцию над агрегатом на том же уровне запроса. Связанный:

Очень важно использовать одно и то же базовое выражение в оконной функции и в GROUP BY: date_trunc('month', period_start).

person Erwin Brandstetter    schedule 26.02.2014