sql – 按年份分组,月份,然后在ActiveRecord中计数3

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不能在一个查询中给我这个结果？

解决方法

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}}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!