【新人教育 資料】第7章 SQLへの道 〜集合関数（SUM、MAX、MIN、AVG、COUNT）編〜

あらすじ

新人がいっぱい入ってくる。新人のレベルもバラバラ。教育資料も古くなっているので、更新しましょう。
どうせなら、公開しちゃえばいいじゃん。という流れになり、新人教育用の資料を順次更新していくことにしました。

※後々、リクエストに応じて更新することが多いのでストックしておくことをおすすめします。

自分はTEMONA株式会社でCTOをしていますが、頭でっかちに理論ばっかり学習するよりは、イメージがなんとなく掴めるように学習し、実践の中で知識を深めていく方が効率的に学習出来ると考えています。

※他の登壇やインタビュー記事はWantedlyから見てください。

教育スタイルとしては正しい事をきっちりかっちり教えるのではなく、未経験レベルの人がなんとなく掴めるように、資料を構成していきます。

以下のようなシリーズネタで進めます。

では、今回もはじめていきましょう！

SQL 集合関数（SUM、MAX、MIN、AVG、COUNT）

集合関数とは、SQLに備わっている演算機能です。Excelある機能と類似しているので、馴染みのある方も多いかと思います。
集計関数,集約関数と言ったりすることもあります。
集合関数には主に次の5つがあります。

これを使いこなせれば,「SQLって便利じゃん!!」となること請け合いです。

基本編

【新人教育 資料】第5章 SQLへの道 〜絞込編〜
で使ったデータを元に話を進めます。
導入されていない方は下記のレポジトリをForkして、Readmeを参考に環境構築をしてください。
https://github.com/TEMONA/mysql_study

※全てにおいて自己責任でお願いします。

全ての基本構造は下記の形です。

SELECT 関数名(カラム名) FROM テーブル名

個別に見ていきましょう。

COUNT

countは総数です。「このテーブルのレコードは幾つなんだろう」「大分県生まれの方は何人いるのだろう」という際に利用します。

「このテーブルのレコードは幾つなんだろう」

select count(*) from users;

アスタリスク【*】は全てをあらわします。
users というテーブルには1000行あることがわかります。

「大分県生まれの方は何人いるのだろう」

select count(*) from users where birthplace = '大分県';

もちろんwhere枠も使えます。大分県生まれは15人ですね。

SUM

sumは総和です。「合計金額はいくらだろう」といった際に利用します。
「合計金額はいくらだろう」

select sum(price) from lineitems;

【price】と言うカラムの総和なので

sum(price)

です。

当然ですが、計算できる値でなければダメです。

MAX

maxは最大値です。
当然ですが、これも最大がある値でなければダメです。

select max(age) from users where birthplace = '大分県';

MIN

minは最小値です。
当然ですが、これも最小がある値でなければ...

select count(*),max(age),min(age) from users where birthplace = '大分県' and gender_id = 0;

このように、集合関数同士同時に利用できます。

AVG

avgは平均値です。
当然ですが、これも平均を取れる値で...

SELECT count(*) AS 総数,
       max(age) AS 最高齢,
       min(age) AS 最年少,
       avg(age) AS 平均年齢
FROM users
WHERE birthplace = '大分県';

どんどん長くなるため、わかりやすく改行しています。大文字の部分がありますが、もちろん小文字でも問題ありません。
【as】は名前をつけます。この場合はわかりやすくするためです。

これで基本編は終了です。

「ちょっと寄り道〜サブクエリ編〜」

【新人教育 資料】第5章 SQLへの道 〜絞込編〜
でちらりとでてきたサブクエリーを使ってみるとこんなこともできます。

SELECT sum(price) AS 大分県生まれ最高年齢購買合計金額
FROM lineitems
WHERE order_id IN
    (SELECT id
     FROM orders
     WHERE user_id IN
         (SELECT id
          FROM users
          WHERE birthplace = '大分県'
            AND age IN
              (SELECT max(age)
               FROM users
               WHERE birthplace = '大分県')));

長いのでやる気を失うかもしれませんが、今まででてきた知識で読むことができます。
分解して考えてみましょう。
まず末端から見ていきます。
①

SELECT max(age)
FROM users
WHERE birthplace = '大分県'

上記に出てきたSQLです。
大分県生まれの方の最高年齢を出しています。

徐々に範囲を広げます。
②

SELECT id
FROM users
WHERE birthplace = '大分県'
  AND age IN
    (SELECT max(age)
     FROM users
     WHERE birthplace = '大分県')

大分県生まれの方の最高年齢の方のusersのidを出しています。
上記の①がSQLの中に入っていることがわかります。

①の値がわかっているのでわかるかと思います。
このSQLのwhereにも

WHERE birthplace = '大分県'

があるのは、これが無いと
①の年齢の人全員が対象になってしまうためです。
こうするとわかりやすいでしょうか。

後はわかりますね。
③

SELECT id
FROM orders
WHERE user_id IN
    (SELECT id
     FROM users
     WHERE birthplace = '大分県'
       AND age IN
         (SELECT max(age)
          FROM users
          WHERE birthplace = '大分県'))

これで大分県生まれ最高年齢の方のordersのidを出しています。
これを繰り返せばいくらでも長く書くこともできます。

演習

・全顧客の最大の誕生日と最小の誕生日を取得
・1980年代生まれの方の注文合計数を取得
・20代の方の購買合計金額を取得

参考文献

mysql5.6 リファレンスマニュアル:https://dev.mysql.com/doc/refman/5.6/ja/

あとがき

今回の集合関数編は以上です。
実際にプログラムに組み込む際は、言語などで開発しているアプリケーション側で計算をさせるべきか、データべース側で計算させるべきか
みたいな判断も必要になってきます。

次回は「【新人教育 資料】第8章 SQLへの道 〜グループ編〜」をお送りする予定です