我试图拉一个一个月和一个月创建的所有用户的数量,但以下似乎没有按预期工作.
User.group("YEAR(created_AT),MONTH(created_at)").
count("DISTINCT(id),YEAR(created_at),MONTH(created_at)")
我正在寻找类似的东西
{2011 => {1 => 222,2 => 333,4 => 444,5 => 667 ... }}
但我得到了
{1 => 222,5 => 667 ... }
我是否缺少某些东西,还是ActiveRecord不能在一个查询中给我这个结果？
解决方法
count方法不像你想的那样工作.你最终这样做：
select count(distinct(id),year(created_at),month(created_at)) from users group by year(created_at),month(created_at)
那个SELECT子句是非常狡猾的,但MysqL会以通常的方式让它通过.我想你想要这个查询：
select count(distinct(id)),month(created_at) from users group by year(created_at),month(created_at)
我可能直接去select_all这样：
a = User.connection.select_all(%q{
select count(distinct(id)) as c,year(created_at) as y,month(created_at) as m
from users
group by y,m
})
或者你可以这样做：
a = User.connection.select_all(
User.select('count(distinct(id)) as c,month(created_at) as m').
group('y,m')
)
那些会给你一个数组,一个哈希,c,y和m键这样：
a = [
{ 'c' => '23','y' => '2010','m' => '11' },{ 'c' => '1','y' => '2011','m' => '1' },{ 'c' => '5','m' => '3' },{ 'c' => '2','m' => '4' },{ 'c' => '11','m' => '8' }
]
那么你完成这项工作就需要一些数据争吵：
h = a.group_by { |x| x['y'] }.each_with_object({}) do |(y,v),h|
h[y.to_i] = Hash[v.map { |e| [e['m'].to_i,e['c'].to_i] }]
end
# {2010 => {11 => 23},2011 => {1 => 1,3 => 5,4 => 2,8 => 11}}
