sql - 在 Ruby、Activerecord 或 Postgresql 中按周对事件进行分组和计数

Question

我有一个跨越几年的事件日志。我被要求计算应用程序每个用户的每周参与度。我将参与度定义为用户在任何给定的一周内进行一项或多项记录的事件。

我如何将这些事件分组并按周为每个用户计算？我读了很多不同的帖子，关于是否ruby methods似乎存在争议。 , sql或 arel 语法是最好的。我的用户不超过 500 人，因此性能不是问题，而是简洁的问题。

我已经成功地尝试过了:

user = User.first.activity_logs.group_by { |m| m.created_at.beginning_of_week } 
       # => {Mon, 11 Mar 2013 00:00:00 EDT -04:00=>
             [#<ActivityLog id: 12345, user_id: 429, ... ]}

然后唯一的下一步我可以无错误地返回任何东西:

user.map { |week| week.count } => [2, 2, 2, 2, 2, 2, 2, 2]

看来我把它弄得太复杂了。如何简洁地计算每周的事件数量并为每个用户计算？

我只想要一些我最终可以粘贴到电子表格(例如，下面)中的东西，以便为管理人员制作热图或其他图表。

| User          | Week            | Activity|
| ------------- | :-------------: | -------:|
| jho           | 2013-1          | 20      |
| bmo           | 2013-1          | 5       |
| jlo           | 2013-1          | 11      |
| gdo           | 2013-2          | 2       |
| gdo           | 2013-5          | 3       |
| jho           | 2013-6          | 5       |

编辑

供他人引用:
导轨 3.1
使用 PostgreSQL 9.1.4
这是 ruby​​ on rails 的模式文件

create_table "activity_logs", :force => true do |t|
  t.integer  "user_id"
  t.string   "activity_type"
  t.datetime "created_at"
  t.datetime "updated_at"
end

| ------+| --------+| ----------------+| ----------------+ | ----------------+ | 
| id     | user_id  | activity_type    | created_at        | updated_at        | 
| ------+| --------+| ----------------+| ----------------+ | ----------------+ | 
| 28257  | 8        | User Signin      | 2013-02-14 1...   | 2013-02-14 1...   | 
| 25878  | 7        | Password Res...  | 2013-02-03 1...   | 2013-02-03 1...   | 
| 25879  | 7        | User Signin      | 2013-02-03 1...   | 2013-02-03 1...   | 
| 25877  | 8        | Password Res...  | 2013-02-03 1...   | 2013-02-03 1...   | 
| 19325  | 8        | Created report   | 2012-12-16 0...   | 2012-12-16 0...   | 
| 19324  | 9        | Added product    | 2012-12-16 0...   | 2012-12-16 0...   | 
| 18702  | 8        | Added event      | 2012-12-15 1...   | 2012-12-15 1...   | 
| 18701  | 1        | Birthday Email   | 2012-12-15 0...   | 2012-12-15 0...   | 
| ------+| --------+| ----------------+| ----------------+ | ----------------+ | 

解决方案

修改@Erwin Brandstetter 的命令，我在命令行上得到了想要的结果:

ActivityLogs.find_by_sql("
  SELECT user_id, to_char(created_at, 'YYYY-WW') AS week, count(*) AS activity
  FROM   activity_logs
  GROUP  BY 1, 2
  ORDER  BY 1, 2;")

Answer 1

我借考了table from @ideamotor并简化了它。事件类型无关紧要，将每个事件计为 1:

CREATE TEMP TABLE log(usr text, day date);
INSERT INTO log VALUES 
  ('bob' , '2012-01-01')
 ,('bob' , '2012-01-02')
 ,('bob' , '2012-01-14')
 ,('susi', '2012-01-01')
 ,('susi', '2012-01-14');

查询(不会比这更简洁):

SELECT usr, to_char(day, 'YYYY-WW') AS week, count(*) AS activity
FROM   log
GROUP  BY 1, 2
ORDER  BY 1, 2;

结果:

usr  | week     | activity
-----+----------+---------
bob  | 2012-01  | 2
bob  | 2012-02  | 1
susi | 2012-01  | 1
susi | 2012-02  | 1

to_char()使这非常简单。我引用 the manual here :

WW week number of year (1-53) (The first week starts on the first day of the year.)

作为替代考虑:

IW ISO week number of year (01 - 53; the first Thursday of the new year is in week 1.)