按周/月/等分组?

时间:2022-11-02 07:36:16

I'm doing some statics calculation in my product. A user has performed a number of operations, let's say posted comments. I want to be able to show them how many comments they've posted per week for the past month, or per month for the past year.

我在我的产品中做了一些静力计算。用户已经执行了许多操作,比方说发布了评论。我希望能够向他们展示他们过去一个月或过去一年每个月发布的评论数量。

Is there any way with activerecord to group this way? Is my best best to simply do this manually - to iterate over the records summing based on my own criteria?

有没有办法用activerecord这种方式分组?我最好只是手动执行此操作 - 根据我自己的标准迭代记录总结?

class User < ActiveRecord::Base
  has_many :comments
end

class Comments < ActiveRecord::Base
  belongs_to :user
end

@user.comments(:all).map {|c| ...do my calculations here...}

or is there some better way?

或者有更好的方法吗?

thanks! Oren

谢谢!奥伦

7 个解决方案

#1


72  

In Postgres you can do:

在Postgres中你可以做到:

@user.comments.group("DATE_TRUNC('month', created_at)").count

to get:

要得到:

{"2012-08-01 00:00:00"=>152, "2012-07-01 00:00:00"=>57, "2012-09-01 00:00:00"=>132}

It accepts values from "microseconds" to "millennium" for grouping: http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

它接受从“微秒”到“千禧年”的值进行分组:http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

#2


28  

In this case, the best solution for me was to either do it in straight SQL, or to use the Ruby group_by function:

在这种情况下,对我来说最好的解决方案是在直接SQL中执行,或者使用Ruby group_by函数:

@user.all.group_by{ |u| u.created_at.beginning_of_month }

#3


16  

Here is the more refined version of this

这是更精致的版本

@user.comments.group("year(created_at)").group("month(created_at)").count

#4


13  

My guess would be something like:

我的猜测是这样的:

@user.comments.count(:group => "year(created_at),month(created_at)")

Dry-code, ymmv

干码,ymmv

#5


3  

Use group_by

使用group_by

@user.comments.group_by(&:week)

class User < ActiveRecord::Base
  def week
    some_attribute_like_date.strftime('%Y-%W')
  end
end

This will give you a grouped list in the format of YYYY-WW

这将为您提供YYYY-WW格式的分组列表

#6


2  

Check out the has_activity plugin.

查看has_activity插件。

#7


2  

Check out the group date gem

查看组日期gem

https://github.com/ankane/groupdate

https://github.com/ankane/groupdate

it has recent commits, works with postgresql, integrates easily with chart kick for fast charting, and works with time zones!!

它有最近的提交,与postgresql一起工作,可以轻松地与图表踢完成快速制图,并与时区一起工作!

#1


72  

In Postgres you can do:

在Postgres中你可以做到:

@user.comments.group("DATE_TRUNC('month', created_at)").count

to get:

要得到:

{"2012-08-01 00:00:00"=>152, "2012-07-01 00:00:00"=>57, "2012-09-01 00:00:00"=>132}

It accepts values from "microseconds" to "millennium" for grouping: http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

它接受从“微秒”到“千禧年”的值进行分组:http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

#2


28  

In this case, the best solution for me was to either do it in straight SQL, or to use the Ruby group_by function:

在这种情况下,对我来说最好的解决方案是在直接SQL中执行,或者使用Ruby group_by函数:

@user.all.group_by{ |u| u.created_at.beginning_of_month }

#3


16  

Here is the more refined version of this

这是更精致的版本

@user.comments.group("year(created_at)").group("month(created_at)").count

#4


13  

My guess would be something like:

我的猜测是这样的:

@user.comments.count(:group => "year(created_at),month(created_at)")

Dry-code, ymmv

干码,ymmv

#5


3  

Use group_by

使用group_by

@user.comments.group_by(&:week)

class User < ActiveRecord::Base
  def week
    some_attribute_like_date.strftime('%Y-%W')
  end
end

This will give you a grouped list in the format of YYYY-WW

这将为您提供YYYY-WW格式的分组列表

#6


2  

Check out the has_activity plugin.

查看has_activity插件。

#7


2  

Check out the group date gem

查看组日期gem

https://github.com/ankane/groupdate

https://github.com/ankane/groupdate

it has recent commits, works with postgresql, integrates easily with chart kick for fast charting, and works with time zones!!

它有最近的提交,与postgresql一起工作,可以轻松地与图表踢完成快速制图,并与时区一起工作!