按时间间隔对事件进行分组和计数，再加上运行总计 [英] Group and count events per time intervals, plus running total

问题描述

我是一个相当新的Postgres用户，我敢肯定已经对此有一个答案，但我找不到它.
我需要分析活动日志表中的一些数据，并将结果按时间段分组.

I'm a fairly new Postgres user, I'm sure there's an answer to this already but I can't find it.
I need to analyze some data in an activity log table, grouping the the results by time period.

问题的一个简单版本是一个包含三个字段的表:

A simple version of the problem would be a table with three fields:

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

我要捕获的操作字符串可能是'create_entry'(是的，我知道这很不错，但数据库设计却很糟糕)

The action string I want to capture could be 'create_entry' (yes, I know that sucks as good db design but I'm stuck with it)

我正在寻找的输出是一个报告，该报告按年份和月份显示了"create_entry"操作的计数.像这样:

The output I'm looking for is a report showing count of 'create_entry' actions by year and month. Something like:

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

我的本​​能是通过子查询来攻击它，但是环顾四周似乎暗示着循环可能是解决之道.

My instinct is to attack this with sub-queries but looking around there seems to be a suggestion that loops might be the way to go.

无论哪种方式，我都无法进入正确的方向.

Either way, I'm out of my depth and looking for a push in the right direction.

编辑

基于我在键入此请求时出现的一个请求的补充问题.上面的一个变体将显示每月的累计总和(尽管我想我可以根据我自己找出这一点关于上面的方法-我只是添加这个，以防它与方法有关.

A supplementary question based on a request that came in while I was typing this.. A variation on the above that would show a cumulative total month by month (although I'd like to think I could figure that out by myself based on the approach to the above - I'm just adding this in case it's relevant to the approach).

推荐答案

如果我理解正确，那么对于action为create_entry的每一行，您只想GROUP BY表中的年月. :

If I understand correctly, you just want to GROUP BY the years and months in your table, for every row that has an action of 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提琴

这篇关于按时间间隔对事件进行分组和计数，再加上运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！