この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。
前回は検索を解説しました。
今回は、検索結果のソート、グループ化と集約関数を解説します。
目次
1. 検索結果のソート
下図6.1のようなcustomersテーブルに対して並べ替えを行ってみましょう。
なお、データベース検索の結果の行の表示には特定の順序がない事は知っておきましょう。
この例では、主キーであるcustomer_idの昇順に並べ替えがなされています。
しかし、この並び順は保証されているわけではありません。
確実に意図した順番で表示したいときは並べ替えを行う必要があります。
なお、並べ替えのことをソートとも言います。（そうとも言う笑）
問い合わせの結果を並べ替えるにはORDER BY句を使用します。
また、並べ替えには昇順（ASC）と降順（DESC）があります。
それぞれ、英語の【ascending】と【descending】の略です。
ソートの構文
SELECT 列名1,列名2, ... FROM 表名
WHERE 抽出条件
ORDER BY 並べ替えキー列1 ASC[DESC],
並べ替えキー列2 ASC[DESC] ,
...;
並べ替えキー列1 が同じ時には 並べ替えキー列2 で並べ替えが行われます。
また、ASC,DESCを指定しなかった場合は ASCが指定されているものとみなされます。
ORDER BY句の例文 昇順
以下6_1.sqlは郵便番号の昇順で並べ替えるSQL文です。
＜結果を表示する＞
ORDER BY句の例文 降順
以下6_2.sqlは郵便番号の降順で並べ替えるSQL文です。
＜結果を表示する＞
並べ替えでは照合順序が基準になるのですが、漢字の場合は意図しない結果になりますので気をつけましょう。
例えば、customersテーブルに対して以下6_3.sqlを発行したとします。
すると下図6.4の結果が得られました。
“いもと”さんと“まるた”さんの並び順が昇順になっていません。
なぜ、このような並び順になるかということですが、それは文字コードの仕組みに秘密があります。
以下の表は文字コードを調べられるサイトを使って調べた8つの文字の文字コードです。
数値、アルファベット、カタカナまではCodeが読みの順に並んでいますが、漢字はそうなっていませんね。（もっとも漢字は色々な読み方がありますが…）
というわけで漢字での並べ替えはしない、と思っていただいてよろしいかと思います。
そこで実務では名前など並べ替えの必要のある項目はカタカナやひらがなの列も作るのが一般的です。（今回は研修のため省略しています）
ORDER BY句の例文 抽出条件との組み合わせ
次に、prefが“愛知県”の顧客のcustomer_idを年齢の高い順に並べ替えてみましょう。
- 年齢が高いとは今回のcustomersテーブルの場合どういう風に判断できますか？
| あなたの答え： |
以下6_4.sqlを見てください。
年齢が高いとは即ちbirthdayの値が小さい順ということになります。
このように、ORDER BY句はWHERE句の後に来るということは覚えておきましょう。
＜結果を表示する＞
ORDER BY句の例文 キー列が複数
ORDER BY句に指定された列に同じ値が存在するとそれらのレコードの抽出順は不定となります。
例えば、customersテーブルをprefの昇順に並べ替えた上で、同じ prefの人はbirthday の降順に並べ替える場合はSQL文は以下のようになります。
以下6_5.sqlを見てください。
＜結果の例＞
今回名前や住所では並べ替えを行いませんでした。
なぜなら、並べ替えの基準が文字コードだからです。
アルファベットでしたらabc順に並べ替えることができます。
しかし、日本語の場合はあいうえお順で並んでほしいのが一般的かと思います。
その場合は、別途ひらがな列を設けるなどの工夫が必要になるというのは前述のとおりです。
1.1. ソートの例題
例題1
customers表を（あまり意味はないが）最初にprefの降順で並べ替え、同じ場合はbirthdayの昇順で並べ替えて全ての列を抽出しなさい。
＜解答例＞
2. グループ化
同じ値を持つデータごとにグループ化するにはGROUP BY句を使用します。
グループ化の構文
SELECT 列名1,列名2, ... FROM 表名
WHERE 抽出条件
GROUP BY グループ化の基準となる列名;
GROUP BY句を使ったグループ化
例えば、以下6_7.sqlはcustomersテーブルに存在しているprefをグループ化して抽出します。
※ORDER BY句を指定しないと抽出結果の順番はランダムであることに注意してください。
＜結果を表示する＞
以下6_8.sqlのようにGROUP BY句を指定していない列をSELECTの後の列名に置くと無意味なデータが抽出されるので気をつけてください。
以下に表示される各県に一人のお名前は、たまたま表示されただけで無意味なデータです。
GROUP BY句を使用した場合にSELECT文の後ろの列名に指定できるのは、
- GROUP BY句 でグループ化の対象になっている列
- SUM(列名)のように集計関数の対象になっている列
のいずれかでなければなりません。
WHERE句とGROUP BY句の組み合わせ
以下6_9.sqlは生年月日が2000-01-01以降の人がいる都道府県をグループ化して抽出します。
＜結果を表示する＞
3. 集約関数
合計や平均を求めるには集約関数を使います。
集約関数は1つの列グループに対して演算結果を抽出します。
列グループを指定しない場合はテーブル全体が列グループになります。
| 集約関数 | 意味 |
| SUM(引数列) | 引数列の合計値を取得する |
| AVG (引数列) | 引数列の平均値を取得する |
| MAX (引数列) | 引数列の最大値を取得する |
| MIN (引数列) | 引数列の最小値を取得する |
| COUNT (引数列) | 引数列のデータの件数を取得する ※ |
※引数に「*」を指定するとレコード数を取得する。（NULL値を含む全ての行がカウントされる）
また、引数に列名を指定するとNULL値の行を除くすべての行がカウントされる。
以下6_10.sqlはcars表から、priceの合計、平均、最大値、最小値、件数を抽出します。
＜結果を表示する＞
3.1. 集約関数とグループ化の組み合わせ
集約関数は先に見たグループ化と組み合わせて使用することが良くあります。
グループ化と集約関数を組み合わせることにより「○○ごとの××」といった集計が可能になります。
例えば、商品ごとの売上合計やクラスごとの平均点数といった集計です。
以下6_11.sqlはcustomers表から、都道府県ごとの人数を取得します。
結果はご自身で確認ください。
以下、6_12.sqlはcustomers表から、都道府県ごとの人数を取得し、人数の多い順に並べ替えます。
結果はご自身で確認ください。
例題1
customers表から以下のように都道府県ごとの、人数、一番若い人の誕生日、一番歳をとった人の誕生日を取得し、郵便番号の昇順に並べ替えなさい。
＜解答例＞
3.2. グループ化した結果に抽出条件を加える
GROUP BY句でグループ化した結果に対して抽出条件で絞り込みを行いたい場合はHAVING句を使います。
WHERE句の抽出条件と似ていますが、 1件1件のレコードではなく、グループ化した結果に対して条件で絞り込んで抽出するところがポイントです。
以下の6_14.sqlはcustomers表から、都道府県ごとの人数を取得し、5人以上の prefと人数を抽出します。
このようにGROUP BYと組み合わせて使うのが HAVING句です。
＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞
□ 検索結果のソート、グループ化と集約関数の考え方3ステップ(例:1989年以降に生まれた顧客が2名以上いる都道府県の名前と人数を知りたい)
抽出条件までを記述する
グループ化して集約関数を加える
グループ化した結果に対して抽出条件を加えて完成
□ 問い合わせの結果を並べ替えるにはORDER BY句を使用する
□ 並べ替えには昇順（ASC）と降順（DESC）がある
□ 同じ値を持つデータごとにグループ化するにはGROUP BY句を使用する
□ 集約関数はグループ化と組み合わせて使用することが多い（○○ごとの××）
□ 1件1件のレコードではなく、グループ化した結果に対して抽出条件で絞り込みを行いたい場合はHAVING句を使う
以上、検索結果のソート、グループ化と集約関数について見てきました。
次回は複数テーブルに対する検索について見ていきましょう。