RailsのActiveRecord AVG編

average_ageをSQLだけでどう取得できるのかを考えてみる

普段、極力SQLを使わずに書くなら、こうなるかと思う。

def self.average_age
  ages  = all.pluck(:age)
  ages.sum / ages.count
end

SQLを思いっきり使用する場合、こう書く。

result = ActiveRecord::Base.connection.select_all('select avg(users.age) as user_age_avg from users ')
   (5.0ms)  select avg(users.age) as user_age_avg from users 
=> 
#<ActiveRecord::Result:0x000000010e334028
...
irb(main):061> result
=> 
#<ActiveRecord::Result:0x000000010e334028
 @column_types={},
 @columns=["user_age_avg"],
 @hash_rows=nil,
 @rows=[[0.5259e2]]>

ActiveRecord::Base.connection.select_value('select avg(users.age) as user_age_avg from users ')
   (37.5ms)  select avg(users.age) as user_age_avg from users 
=> 0.494038e2

ActiveRecord::Base.connection.select_value('select avg(users.age) as user_age_avg from users ').to_i
   (38.0ms)  select avg(users.age) as user_age_avg from users 
=> 49

SQLで平均値を計算するとなると、対数平均で算出する。 小数点を丸めたい場合、ROUND関数を使用することでそれが可能となる。

> result = ActiveRecord::Base.connection.select_all('select round(avg(users.age)) as user
_age_avg from users ')
   (4.8ms)  select round(avg(users.age)) as user_age_avg from users 
=> 
#<ActiveRecord::Result:0x000000010ecbac00
...
irb(main):063> result
=> 
#<ActiveRecord::Result:0x000000010ecbac00
 @column_types={},
 @columns=["user_age_avg"],
 @hash_rows=nil,
 @rows=[[53]]>
irb(main):064> result = ActiveRecord::Base.connection.select_all('select round(avg(users.age),1) as us
er_age_avg from users ')
   (5.0ms)  select round(avg(users.age),1) as user_age_avg from users 
=> 
#<ActiveRecord::Result:0x000000010e5343f0
...
irb(main):065> result
=> 
#<ActiveRecord::Result:0x000000010e5343f0
 @column_types={},
 @columns=["user_age_avg"],
 @hash_rows=nil,
 @rows=[[0.526e2]]>