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