rubyでEnumerableを用いたSQLライクな操作
rubyのEnumerableモジュールには様々な便利メソッドがありますが、今回はそれを活用して、SQLで出来る操作でrubyならこんな感じかなというやり方をまとめていきます。 特に公式ではただのArrayでのみの簡潔な解説となってますが、以下のデータではArray内Hashでの操作をメインとして説明していきます。
サンプルデータ
ある学年の学期末のテストの結果
- students
id | name | birthday | grader | class_name | height |
---|---|---|---|---|---|
1 | 大宮忍 | 6/6 | 2 | 2-A | 155 |
2 | アリス・カータレット | 4/5 | 2 | 2-C | 139 |
3 | 小路綾 | 2 | 9/15 | 2-A | 159 |
4 | 猪熊陽子 | 2 | 8/20 | 2-C | 163 |
5 | 九条カレン | 2 | 12/1 | 2-A | 150 |
- test_score_lists
id | student_id | subject | score |
---|---|---|---|
1 | 1 | 英語 | 6 |
2 | 1 | 数学 | 36 |
3 | 1 | 理科 | 42 |
4 | 1 | 社会 | 61 |
5 | 1 | 国語 | 68 |
6 | 2 | 英語 | 98 |
7 | 2 | 数学 | 89 |
8 | 2 | 理科 | 79 |
9 | 2 | 社会 | 91 |
10 | 2 | 国語 | 82 |
11 | 3 | 英語 | 88 |
12 | 3 | 数学 | 92 |
13 | 3 | 理科 | 86 |
14 | 3 | 社会 | 71 |
15 | 3 | 国語 | 89 |
16 | 4 | 英語 | 34 |
17 | 4 | 数学 | 38 |
18 | 4 | 理科 | 39 |
19 | 4 | 社会 | 54 |
20 | 4 | 国語 | 58 |
21 | 5 | 英語 | 92 |
22 | 5 | 数学 | 31 |
23 | 5 | 理科 | 32 |
24 | 5 | 社会 | 44 |
25 | 5 | 国語 | 61 |
データ投入
students = [ { id: 1, name: '大宮 忍', birthday: '6/6', grader: 2, class_name: '2-A', height: 155 }, { id: 2, name: 'アリス・カータレット', birthday: '4/5', grader: 2, class_name: '2-C', height: 139 }, { id: 3, name: '小路 綾', grader: 2, birthday: '9/15', class_name: '2-A', height: 159 }, { id: 4, name: '猪熊 陽子', grader: 2, birthday: '8/20', class_name: '2-C', height: 163 }, { id: 5, name: '九条 カレン', grader: 2, birthday: '12/1', class_name: '2-A', height: 150 } ] test_score_lists = [ { id: 1, student_id: 1, subject: '英語', score: 6 }, { id: 2, student_id: 1, subject: '数学', score: 36 }, { id: 3, student_id: 1, subject: '理科', score: 42 }, { id: 4, student_id: 1, subject: '社会', score: 61 }, { id: 5, student_id: 1, subject: '国語', score: 68 }, { id: 6, student_id: 2, subject: '英語', score: 98 }, { id: 7, student_id: 2, subject: '数学', score: 89 }, { id: 8, student_id: 2, subject: '理科', score: 79 }, { id: 9, student_id: 2, subject: '社会', score: 91 }, { id: 10, student_id: 2, subject: '国語', score: 82 }, { id: 11, student_id: 3, subject: '英語', score: 88 }, { id: 12, student_id: 3, subject: '数学', score: 92 }, { id: 13, student_id: 3, subject: '理科', score: 86 }, { id: 14, student_id: 3, subject: '社会', score: 71 }, { id: 15, student_id: 3, subject: '国語', score: 89 }, { id: 16, student_id: 4, subject: '英語', score: 34 }, { id: 17, student_id: 4, subject: '数学', score: 38 }, { id: 18, student_id: 4, subject: '理科', score: 39 }, { id: 19, student_id: 4, subject: '社会', score: 54 }, { id: 20, student_id: 4, subject: '国語', score: 58 }, { id: 21, student_id: 5, subject: '英語', score: 92 }, { id: 22, student_id: 5, subject: '数学', score: 31 }, { id: 23, student_id: 5, subject: '理科', score: 32 }, { id: 24, student_id: 5, subject: '社会', score: 44 }, { id: 25, student_id: 5, subject: '国語', score: 61 } ]
SELECT
(find)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/detect.htmlメソッド
- 誕生日が6/6の生徒を一人抽出
SELECT id, name, birthday, grader, class_name, height FROM students WHERE birthday = '6/6' LIMIT 1;
students.find { |student| student[:birthday] == '6/6' } => {:id=>1, :name=>"大宮 忍", :birthday=>"6/6", :grader=>2, :class_name=>"2-A", :height=>155}
(select)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/find_all.htmlメソッド
身長が150cm以上 160cm未満の生徒を抽出
SELECT id, name, birthday, grader, class_name, height FROM students WHERE height >= 150 AND height < 160;
students.select { |student| student[:height] >= 150 && student[:height] < 160 } => [{:id=>1, :name=>"大宮 忍", :birthday=>"6/6", :grader=>2, :class_name=>"2-A", :height=>155}, {:id=>3, :name=>"小路 綾", :grader=>2, :birthday=>"9/15", :class_name=>"2-A", :height=>159}, {:id=>5, :name=>"九条 カレン", :grader=>2, :birthday=>"12/1", :class_name=>"2-A", :height=>150}]
JOIN
アリスのテスト結果
SELECT test.id, test.student_id, test.subject, test.score FROM test_score_lists test LEFT OUTER JOIN students student ON student.id = test.student_id WHERE student.name = 'アリス・カータレット' ;
-ruby
test_score_lists.select { |test| test[:student_id] == students.find { |student| student[:name] == 'アリス・カータレット' }[:id] } => [{:id=>6, :student_id=>2, :subject=>"英語", :score=>98}, {:id=>7, :student_id=>2, :subject=>"数学", :score=>89}, {:id=>8, :student_id=>2, :subject=>"理科", :score=>79}, {:id=>9, :student_id=>2, :subject=>"社会", :score=>91}, {:id=>10, :student_id=>2, :subject=>"国語", :score=>82}]
全生徒のテスト結果
SELECT test.id, test.student_id, test.subject, test.score, stundent.name student_name, student.class_name FROM test_score_lists test LEFT OUTER JOIN students student ON student.id = test.student_id
find_students = lambda { |student_id| students.find { |student| student[:id] == student_id } } students_test_score_lists = test_score_lists.map { |test| test.merge(student_name: find_students.call(test[:student_id])[:name], class_name: find_students.call(test[:student_id])[:class_name]) } => [{:id=>1, :student_id=>1, :subject=>"英語", :score=>6, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>2, :student_id=>1, :subject=>"数学", :score=>36, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>3, :student_id=>1, :subject=>"理科", :score=>42, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>4, :student_id=>1, :subject=>"社会", :score=>61, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>5, :student_id=>1, :subject=>"国語", :score=>68, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>6, :student_id=>2, :subject=>"英語", :score=>98, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>7, :student_id=>2, :subject=>"数学", :score=>89, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>8, :student_id=>2, :subject=>"理科", :score=>79, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>9, :student_id=>2, :subject=>"社会", :score=>91, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>10, :student_id=>2, :subject=>"国語", :score=>82, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>11, :student_id=>3, :subject=>"英語", :score=>88, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>12, :student_id=>3, :subject=>"数学", :score=>92, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>13, :student_id=>3, :subject=>"理科", :score=>86, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>14, :student_id=>3, :subject=>"社会", :score=>71, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>15, :student_id=>3, :subject=>"国語", :score=>89, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>16, :student_id=>4, :subject=>"英語", :score=>34, :student_name=>"猪熊 陽子", :class_name=>"2-C"}, {:id=>17, :student_id=>4, :subject=>"数学", :score=>38, :student_name=>"猪熊 陽子", :class_name=>"2-C"}, {:id=>18, :student_id=>4, :subject=>"理科", :score=>39, :student_name=>"猪熊 陽子", :class_name=>"2-C"}, {:id=>19, :student_id=>4, :subject=>"社会", :score=>54, :student_name=>"猪熊 陽子", :class_name=>"2-C"}, {:id=>20, :student_id=>4, :subject=>"国語", :score=>58, :student_name=>"猪熊 陽子", :class_name=>"2-C"}, {:id=>21, :student_id=>5, :subject=>"英語", :score=>92, :student_name=>"九条 カレン", :class_name=>"2-A"}, {:id=>22, :student_id=>5, :subject=>"数学", :score=>31, :student_name=>"九条 カレン", :class_name=>"2-A"}, {:id=>23, :student_id=>5, :subject=>"理科", :score=>32, :student_name=>"九条 カレン", :class_name=>"2-A"}, {:id=>24, :student_id=>5, :subject=>"社会", :score=>44, :student_name=>"九条 カレン", :class_name=>"2-A"}, {:id=>25, :student_id=>5, :subject=>"国語", :score=>61, :student_name=>"九条 カレン", :class_name=>"2-A"}]
集計
(reduce)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/inject.htmlメソッド
英語の合計点
SELECT SUM(score) FROM test_score_lists WHERE subject = '英語';
test_score_lists.select { |test| test[:subject] == '英語' }.reduce(0) { |sum, test| sum + test[:score] } => 318
数学の平均点(5人を全生徒とした場合)
SELECT AVG(score) FROM test_score_lists WHERE subject = '数学';
test_score_lists.select { |test| test[:subject] == '数学' }.reduce(0) { |sum, test| sum + test[:score] } / students.size => 57
理科の最高得点
SELECT MAX(score) FROM test_score_lists WHERE subject = '理科';
test_score_lists.select { |test| test[:subject] == '理科' }.max { |a, b| a[:score] <=> b[:score] }[:score] => 86 test_score_lists.select { |test| test[:subject] == '理科' }.map { |test| test[:score] }.max => 86
test_score_lists.select { |test| test[:subject] == '数学' }.reduce(0) { |sum, test| sum + test[:score] } / students.size => 57
2-Aの英語の平均
SELECT AVG(test.score) FROM test_score_lists test LEFT OUTER JOIN students student ON student.id = test.student_id WHERE test.subject = '英語' AND student.class_name = '2-A';
students_test_score_lists.select { |test| test[:class_name] == '2-A' && test[:subject] == '英語' }.tap { |this| break this.reduce(0) { |sum, test| sum + test[:score] } / this.size } => 62
2-Aの英語が最低だった人
students_test_score_lists.select { |test| test[:class_name] == '2-A' && test[:subject] == '英語' }.min { |a, b| a[:score] <=> b[:score] }[:student_name] => "大宮 忍"
GROUP BY
(group_by)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/group_by.htmlメソッド
- クラス別に生徒を抽出
students.group_by { |student| student[:class_name] } => {"2-A"=> [{:id=>1, :name=>"大宮 忍", :birthday=>"6/6", :grader=>2, :class_name=>"2-A", :height=>155}, {:id=>3, :name=>"小路 綾", :grader=>2, :birthday=>"9/15", :class_name=>"2-A", :height=>159}, {:id=>5, :name=>"九条 カレン", :grader=>2, :birthday=>"12/1", :class_name=>"2-A", :height=>150}], "2-C"=> [{:id=>2, :name=>"アリス・カータレット", :birthday=>"4/5", :grader=>2, :class_name=>"2-C", :height=>139}, {:id=>4, :name=>"猪熊 陽子", :grader=>2, :birthday=>"8/20", :class_name=>"2-C", :height=>163}]}
- 各クラスの科目別平均点
Hash[students_test_score_lists.group_by { |test| test[:class_name] + test[:subject] }.map { |k, v| [ k , v.tap { |this| break this.reduce(0) { |sum, row| row[:score] }/ this.size } ] }] => {"2-A英語"=>30, "2-A数学"=>10, "2-A理科"=>10, "2-A社会"=>14, "2-A国語"=>20, "2-C英語"=>17, "2-C数学"=>19, "2-C理科"=>19 , "2-C社会"=>27, "2-C国語"=>29}
ORDER BY
(sort)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/sort.htmlメソッド
Hash[students_test_score_lists.group_by { |test| test[:class_name] + test[:subject] }.map { |k, v| [k, v.sort { |a, b| b[:score] <=> a[:score] } ] }] => {"2-A英語"=> [{:id=>21, :student_id=>5, :subject=>"英語", :score=>92, :student_name=>"九条 カレン", :class_name=>"2-A"}, {:id=>11, :student_id=>3, :subject=>"英語", :score=>88, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>1, :student_id=>1, :subject=>"英語", :score=>6, :student_name=>"大宮 忍", :class_name=>"2-A"}], "2-A数学"=> [{:id=>12, :student_id=>3, :subject=>"数学", :score=>92, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>2, :student_id=>1, :subject=>"数学", :score=>36, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>22, :student_id=>5, :subject=>"数学", :score=>31, :student_name=>"九条 カレン", :class_name=>"2-A"}], "2-A理科"=> [{:id=>13, :student_id=>3, :subject=>"理科", :score=>86, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>3, :student_id=>1, :subject=>"理科", :score=>42, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>23, :student_id=>5, :subject=>"理科", :score=>32, :student_name=>"九条 カレン", :class_name=>"2-A"}], "2-A社会"=> [{:id=>14, :student_id=>3, :subject=>"社会", :score=>71, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>4, :student_id=>1, :subject=>"社会", :score=>61, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>24, :student_id=>5, :subject=>"社会", :score=>44, :student_name=>"九条 カレン", :class_name=>"2-A"}], "2-A国語"=> [{:id=>15, :student_id=>3, :subject=>"国語", :score=>89, :student_name=>"小路 綾", :class_name=>"2-A"}, {:id=>5, :student_id=>1, :subject=>"国語", :score=>68, :student_name=>"大宮 忍", :class_name=>"2-A"}, {:id=>25, :student_id=>5, :subject=>"国語", :score=>61, :student_name=>"九条 カレン", :class_name=>"2-A"}], "2-C英語"=> [{:id=>6, :student_id=>2, :subject=>"英語", :score=>98, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>16, :student_id=>4, :subject=>"英語", :score=>34, :student_name=>"猪熊 陽子", :class_name=>"2-C"}], "2-C数学"=> [{:id=>7, :student_id=>2, :subject=>"数学", :score=>89, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>17, :student_id=>4, :subject=>"数学", :score=>38, :student_name=>"猪熊 陽子", :class_name=>"2-C"}], "2-C理科"=> [{:id=>8, :student_id=>2, :subject=>"理科", :score=>79, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>18, :student_id=>4, :subject=>"理科", :score=>39, :student_name=>"猪熊 陽子", :class_name=>"2-C"}], "2-C社会"=> [{:id=>9, :student_id=>2, :subject=>"社会", :score=>91, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>19, :student_id=>4, :subject=>"社会", :score=>54, :student_name=>"猪熊 陽子", :class_name=>"2-C"}], "2-C国語"=> [{:id=>10, :student_id=>2, :subject=>"国語", :score=>82, :student_name=>"アリス・カータレット", :class_name=>"2-C"}, {:id=>20, :student_id=>4, :subject=>"国語", :score=>58, :student_name=>"猪熊 陽子", :class_name=>"2-C"}]}
DELETE
(reject)http://docs.ruby-lang.org/ja/2.0.0/method/Enumerable/i/reject.htmlメソッド
DELETE FROM test_score_lists WHERE id = 1;
test_score_lists.reject { |test| test[:id] == 1 } => [{:id=>2, :student_id=>1, :subject=>"数学", :score=>36}, {:id=>3, :student_id=>1, :subject=>"理科", :score=>42}, {:id=>4, :student_id=>1, :subject=>"社会", :score=>61}, {:id=>5, :student_id=>1, :subject=>"国語", :score=>68}, {:id=>6, :student_id=>2, :subject=>"英語", :score=>98}, {:id=>7, :student_id=>2, :subject=>"数学", :score=>89}, {:id=>8, :student_id=>2, :subject=>"理科", :score=>79}, {:id=>9, :student_id=>2, :subject=>"社会", :score=>91}, {:id=>10, :student_id=>2, :subject=>"国語", :score=>82}, {:id=>11, :student_id=>3, :subject=>"英語", :score=>88}, {:id=>12, :student_id=>3, :subject=>"数学", :score=>92}, {:id=>13, :student_id=>3, :subject=>"理科", :score=>86}, {:id=>14, :student_id=>3, :subject=>"社会", :score=>71}, {:id=>15, :student_id=>3, :subject=>"国語", :score=>89}, {:id=>16, :student_id=>4, :subject=>"英語", :score=>34}, {:id=>17, :student_id=>4, :subject=>"数学", :score=>38}, {:id=>18, :student_id=>4, :subject=>"理科", :score=>39}, {:id=>19, :student_id=>4, :subject=>"社会", :score=>54}, {:id=>20, :student_id=>4, :subject=>"国語", :score=>58}, {:id=>21, :student_id=>5, :subject=>"英語", :score=>92}, {:id=>22, :student_id=>5, :subject=>"数学", :score=>31}, {:id=>23, :student_id=>5, :subject=>"理科", :score=>32}, {:id=>24, :student_id=>5, :subject=>"社会", :score=>44}, {:id=>25, :student_id=>5, :subject=>"国語", :score=>61}]
UPDATE
UPDATE test_score_lists SET score = 100 WHERE id = 1;
test_score_lists.map! { |test| test[:id] == 1 ? test[:score] = 100 : test[:score]; test } => [{:id=>1, :student_id=>1, :subject=>"英語", :score=>100}, {:id=>2, :student_id=>1, :subject=>"数学", :score=>36}, {:id=>3, :student_id=>1, :subject=>"理科", :score=>42}, {:id=>4, :student_id=>1, :subject=>"社会", :score=>61}, {:id=>5, :student_id=>1, :subject=>"国語", :score=>68}, {:id=>6, :student_id=>2, :subject=>"英語", :score=>98}, {:id=>7, :student_id=>2, :subject=>"数学", :score=>89}, {:id=>8, :student_id=>2, :subject=>"理科", :score=>79}, {:id=>9, :student_id=>2, :subject=>"社会", :score=>91}, {:id=>10, :student_id=>2, :subject=>"国語", :score=>82}, {:id=>11, :student_id=>3, :subject=>"英語", :score=>88}, {:id=>12, :student_id=>3, :subject=>"数学", :score=>92}, {:id=>13, :student_id=>3, :subject=>"理科", :score=>86}, {:id=>14, :student_id=>3, :subject=>"社会", :score=>71}, {:id=>15, :student_id=>3, :subject=>"国語", :score=>89}, {:id=>16, :student_id=>4, :subject=>"英語", :score=>34}, {:id=>17, :student_id=>4, :subject=>"数学", :score=>38}, {:id=>18, :student_id=>4, :subject=>"理科", :score=>39}, {:id=>19, :student_id=>4, :subject=>"社会", :score=>54}, {:id=>20, :student_id=>4, :subject=>"国語", :score=>58}, {:id=>21, :student_id=>5, :subject=>"英語", :score=>92}, {:id=>22, :student_id=>5, :subject=>"数学", :score=>31}, {:id=>23, :student_id=>5, :subject=>"理科", :score=>32}, {:id=>24, :student_id=>5, :subject=>"社会", :score=>44}, {:id=>25, :student_id=>5, :subject=>"国語", :score=>61}]
INSERT
INSERT INTO students (id, name, class_name, height) VALUES (6, '松原 穂乃花', '2-A', 157);
students << { id: 6, name: '松原 穂乃花', class_name: '2-A', height: 157 } => [{:id=>1, :name=>"大宮 忍", :birthday=>"6/6", :grader=>2, :class_name=>"2-A", :height=>155}, {:id=>2, :name=>"アリス・カータレット", :birthday=>"4/5", :grader=>2, :class_name=>"2-C", :height=>139}, {:id=>3, :name=>"小路 綾", :grader=>2, :birthday=>"9/15", :class_name=>"2-A", :height=>159}, {:id=>4, :name=>"猪熊 陽子", :grader=>2, :birthday=>"8/20", :class_name=>"2-C", :height=>163}, {:id=>5, :name=>"九条 カレン", :grader=>2, :birthday=>"12/1", :class_name=>"2-A", :height=>150}, {:id=>6, :name=>"松原 穂乃花", :class_name=>"2-A", :height=>157}]
Enumerableモジュール使用時の注意点
メソッドは全てeach を用いて定義されているので、インクルード するクラスには each が定義されていなければなりません。