この記事では主にActiveRecordのgroupメソッドやそれに関連するメソッドをみつつ、仕組みを説明します。あと、ActiveRecordの集計関数ちょっと癖あるよね的な話です。
テーブル作成
まずはじめにこういうテーブルを作ります。
exam_scores
- 教科(subject) : String
- 人の名前(name) : String
- 点数(score) : Integer
bundle exec rails g model exam_scores subject:string name:string score:integer
データ作成
中身のデータは以下のようにします
| subject |
name |
score |
| 国語 |
田中 |
50 |
| 社会 |
田中 |
70 |
| 社会 |
山田 |
30 |
scores = [
{ subject: '国語', name: '田中', score: '50' },
{ subject: '社会', name: '田中', score: '70' },
{ subject: '社会', name: '山田', score: '30' }
]
ExamScore.create! scores
メソッドたち
次に、集計操作に使うメソッドを列挙します。
- groupを作る条件を指定する
- 集計
- 集計結果のフィルタリング
group + sum
まず、groupとsumを指定してみましょう。
| subject |
name |
score |
| 国語 |
田中 |
50 |
| 社会 |
田中 |
70 |
| 社会 |
山田 |
30 |
ExamScore.group(:subject).sum(:score)
=> {"国語"=>50, "社会"=>100}
scoreの合計値をsubject毎に表示できました。しかし、結果が ExamScore のインスタンスではなくハッシュですね？
SQLの出力を見てみましょう。
SELECT SUM("exam_scores"."score") AS sum_score, subject AS subject FROM "exam_scores" GROUP BY "exam_scores"."subject"
SQLの結果にnameが含まれません。これはexam_scoresテーブルをsubjectでgroup化した時に、nameが特定できないからです。
AcriveRecordは
- 1テーブル <=> 1クラス
- 1レコード <=> 1インスタンス
を原則として設計されています。
これは ActiveRecordパターン という有名なオブジェクト指向設計パターンに由来しています。group + sum を使ったSQLの結果は、そのテーブルの1レコードと等しくなりません。故に、 ExamScore.group(:subject).sum(:score) の結果も ExamScore のインスタンスではなく、只のハッシュとして戻ってきます。
group + average + having
次に、subjectをnameに、sumをaverageに変えてみましょう。
| subject |
name |
score |
| 国語 |
田中 |
50 |
| 社会 |
田中 |
70 |
| 社会 |
山田 |
30 |
ExamScore.group(:name).average(:score)
=> {"山田"=>30, "田中"=>60}
havingを使ってみましょう。
ExamScore.group(:name).having('avg(score) > 50').average(:score)
=> {"田中"=>60}
山田くんが消えましたね(^-^)
SQLを見てみます。
SELECT AVG("exam_scores"."score") AS average_score, name AS name FROM "exam_scores" GROUP BY "exam_scores"."name" HAVING avg(score) > 50
結果がハッシュなのは先ほどと同様ですが、今回は２つ注意点が有ります。
１つ目は、havingメソッドが 引数をそのまま文字列としてSQLに入れること です。
例えば、 avg(score) を average_score に変えます。
ExamScore.group(:name).having('average_score > 50').average(:score)
=> {"田中"=>60}
とすれば、SQLでも avg(score) が average_score に変わります。
SELECT AVG("exam_scores"."score") AS average_score, name AS name FROM "exam_scores" GROUP BY "exam_scores"."name" HAVING average_score > 50
になります。
２つ目は、havingメソッドを入れる場所は、集計メソッド(sum, average, count)よりも前にする必要があります。
集計メソッドの戻り値はその時点でハッシュにされてしまうからです。
先ほどのrubyコードでhavingメソッドを最後に持ってきてみます。
ExamScore.group(:name).average(:score).having('average_score > 50')
実行結果は以下のようになります。
NoMethodError: undefined method `having' for #<Hash:0x007f5c837ec778>
from (irb):11
from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/console.rb:110:in `start'
from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/console.rb:9:in `start'
from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/commands_tasks.rb:68:in `console'
from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands.rb:17:in `<top (required)>'
from bin/rails:8:in `require'
from bin/rails:8:in `<main>'
¯\_(ツ)_/¯
まとめ
集計関数は ActiveRecordの原則の外にあるので、ActiveRecordからDBに触り始めた人には馴染みにくく、関係するメソッドにも癖が強いです。
ただ、集計関数こそSQLの醍醐味なので、しっかり使えるようになりましょう。
