のんびりSEの議事録

プログラミング系のポストからアプリに関してのポストなどをしていきます。まれにアニメ・マンガなど

トップ > Ruby > rubyでEnumerableを用いたSQLライクな操作

rubyでEnumerableを用いたSQLライクな操作

Ruby

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未満の生徒を抽出

  • sql

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

  • アリスのテスト結果

  • sql

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

  • 全生徒のテスト結果

  • sql

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メソッド

  • 英語の合計点

  • sql

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人を全生徒とした場合)

  • sql

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

  • 理科の最高得点

  • sql

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の英語の平均

  • sql

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の英語が最低だった人

  • ruby

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 が定義されていなければなりません。