【新人教育 資料】第8章 SQLへの道 〜グループ編〜
あらすじ
新人がいっぱい入ってくる。新人のレベルもバラバラ。教育資料も古くなっているので、更新しましょう。
どうせなら、公開しちゃえばいいじゃん。という流れになり、新人教育用の資料を順次更新していくことにしました。
※後々、リクエストに応じて更新することが多いのでストックしておくことをおすすめします。
自分はTEMONA株式会社でCTOをしていますが、頭でっかちに理論ばっかり学習するよりは、イメージがなんとなく掴めるように学習し、実践の中で知識を深めていく方が効率的に学習出来ると考えています。
※他の登壇やインタビュー記事はWantedlyから見てください。
教育スタイルとしては正しい事をきっちりかっちり教えるのではなく、未経験レベルの人がなんとなく掴めるように、資料を構成していきます。
以下のようなシリーズネタで進めます。
では、今回もはじめていきましょう！
グループ
こちらは前回の
【新人教育 資料】第7章 SQLへの道 〜集合関数（SUM、MAX、MIN、AVG、COUNT）編〜
の発展編です。
まだこちらを読んでいない方はそちらの一読をお願いします。
前回はある条件の総数や総和、最大、最小そして平均を出す方法を見てきました。
たとえば、全ての注文の「県ごと」の人数を知りたい際はどうすれば良いでしょうか。
もちろん、下記のように頑張ればいけます。
select count(*) from orders where prefectural = '北海道';
select count(*) from orders where prefectural = '青森県';
select count(*) from orders where prefectural = '大分県';
エンジニアは面倒臭がりです。
これを全県分作れば結果が得られますが、実はこんなことしなくても一発で出る方法があります。
SELECT count(*),prefectural FROM orders GROUP BY prefectural;
それが今回紹介する
group by
です。
GROUP BY
group by
は
「〜ごと」
という処理を行います。
上記の例では、全ての注文の「県ごと」の人数を出しています。
この「県ごと」という部分が
GROUP BY prefectural;
の部分です。
基本構造は下記の形です。
SELECT 関数名(カラム名１),カラム名２ FROM テーブル名 GROUP BY カラム名２;
カラム名１が計算されるカラム
カラム名２が「〜ごと」の部分のカラムです。
select部分のカラム名２はなくても動きますが、無いと困ります。
どう困るかわからない方はやってみてください。
いくつか紹介して行きましょう。
例
・全ての顧客の年齢ごとの人数を調べる
select count(*) as 人数,age as 年齢 from users where age is not null group by age order by age;
年齢「ごと」の人数を出しています。
・出身地ごとに、性別ごとの最高齢と最年少を調べています。
SELECT max(age) AS 最高齢,
min(age) AS 最年少,
birthplace AS 出身地,
(CASE
WHEN gender_id = 0 THEN '男性'
ELSE '女性'
END) AS 性別
FROM users
WHERE age IS NOT NULL
GROUP BY birthplace,
gender_id
ORDER BY birthplace,
gender_id;
ちなみに
(CASE
WHEN gender_id = 0 THEN '男性'
ELSE '女性'
END) AS 性別
の部分はcase文と呼ばれ、
基本構文は
case when カラム名 = 値１ then AAA else BBB end
のような条件文です。
when カラム名 = 値１ then AAA
の部分は幾つでも増やすことができます。
読んで字のごとくなのですが、この場合 gender_id が 0 の場合は「男性」それ以外の場合は「女性」を返します。
group by を使えば非常に便利なことがわかっていただけるかと思います。
それをさらに便利にするのが having です。
HAVING
select count(*) as 人数,age as 年齢 from users group by age order by age;
上記に出てきたこちらのSQLを用います。
たとえば、この中で人数が25人以上の年齢を出したい場合にhavingが使えます。
SELECT count(*) AS 人数,
age AS 年齢
FROM users
WHERE age IS NOT NULL
GROUP BY age
HAVING count(*) >= 25
ORDER BY age ;
having は集合関数の結果をもとに絞り込むことができます。
基本構造は下記の形です。
SELECT 関数名(カラム名１),カラム名２ FROM テーブル名 GROUP BY カラム名２ HAVING 関数名(カラム名１);
例
下記3つの違い、わかりますか?
①
SELECT avg(age) AS 平均年齢,
count(*) AS 人数,
birthplace AS 出身地
FROM users
WHERE age IS NOT NULL
AND age >= 40
GROUP BY birthplace ;
②
SELECT avg(age) AS 平均年齢,
count(*) AS 人数,
birthplace AS 出身地
FROM users
WHERE age IS NOT NULL
GROUP BY birthplace
HAVING avg(age) >= 40;
③
SELECT avg(age) AS 平均年齢,
count(*) AS 人数,
birthplace AS 出身地
FROM users
WHERE age IS NOT NULL
AND age >= 40
GROUP BY birthplace
HAVING avg(age) >= 50;
①は出身地ごとの40歳以上の方の平均年齢と人数と出身地
②は出身地ごとの平均年齢が40歳以上の平均年齢と人数と出身地
③は出身地ごとの40歳以上の方の平均年齢が50歳以上の平均年齢と人数と出身地です。
こう書くと、日本語の方がややこしいですね。
「ちょっと寄り道〜基本数学編〜」
【新人教育 資料】第3章 SQLへの道 〜基本数学編〜
で出てきた基本数学を利用すれば各データをSQL上でこねくり回せます。色々遊んでみてください。
例
SELECT count(*) AS 人数,
max(age) AS 県内最高年齢,
min(age) AS 県内最低年齢,
avg(age) AS 県平均年齢,
(max(age) - min(age)) AS 県内最大年齢差,
(max(age) + min(age)) AS 県内最大年齢と最小年齢の和,
(avg(age) * 2) AS 平均２倍,
(max(age) + min(age)) - (avg(age) * 2) AS 県内最大年齢と最小年齢の和と平均の２倍の差,
birthplace AS 出身地
FROM users
WHERE age IS NOT NULL
GROUP BY birthplace
ORDER BY 県内最大年齢と最小年齢の和と平均の２倍の差;
演習
・血液型ごとにそれぞれ何人いるか情報を取得
・県ごとに最高齢と最年少の年齢差の情報を取得し、その差順に並べる
参考文献
mysql5.6 リファレンスマニュアル:https://dev.mysql.com/doc/refman/5.6/ja/
あとがき
これで集合関数は完了です。
ここまで使いこなせればSQLが非常に便利なツールであるということが実感できると思います。
次回の【新人教育 資料】第9章 SQLへの道 〜結合編〜
まで使えるようになれば一人前のSQL初心者と言えるのでは無いでしょうか。