この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。

前回は検索を解説しました。

今回は、検索結果のソート、グループ化と集約関数を解説します。

1. 検索結果のソート

下図のようなcustomersテーブルに対して並べ替えを行ってみましょう。

customers_table
customersテーブル

本章で学ぶORDER BYを使わない限り、取得結果の並び順は保証されません。上記の例では、主キーである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文です。

SELECT 
    *
FROM
    sip_a.customers
ORDER BY postalCode;

＜結果＞

order_by
実行結果の例（中略）

ORDER BY句の例文　降順

以下6_2.sqlは郵便番号の降順で並べ替えるSQL文です。

SELECT 
    *
FROM
    sip_a.customers
ORDER BY postalCode DESC;

＜結果の例＞

desc
実行結果の例

並べ替えでは照合順序が基準になるのですが、漢字の場合は意図しない結果になりますので気をつけましょう。

例えば、customersテーブルに対して以下6_3.sqlを発行したとします。

SELECT 
    *
FROM
    customers
ORDER BY customers.name

すると下図の結果が得られました。

name_order
漢字の名前で並べ替えた例（抜粋）

“いもと”さんと“まるた”さんの並び順が昇順になっていません。なぜ、このような並び順になるかということですが、それは文字コードの仕組みに秘密があります。

以下の表は文字コードを調べられるサイトを使って調べた8つの文字の文字コードです。

8つの文字の文字コード

数値、アルファベット、カタカナまではCodeが読みの順に並んでいますが、漢字はそうなっていませんね。（もっとも漢字は色々な読み方がありますが…）というわけで漢字での並べ替えはしない、と思っていただいてよろしいかと思います。そこで実務では名前など並べ替えの必要のある項目はカタカナやひらがなのフィールドも作るのが一般的です。（今回は研修のため省略しています）

ORDER BY句の例文　抽出条件との組み合わせ

次に、prefが“愛知県”の顧客のcustomer_idを年齢の高い順に並べ替えてみましょう。

  • 年齢が高いとは今回のcustomersテーブルの場合どういう風に判断できますか？
あなたの答え：

以下6_4.sqlを見てください。

SELECT 
    *
FROM
    sip_a.customers
WHERE
    pref = '愛知県'
ORDER BY birthday;

年齢が高い人ほど生年月日（birthday）の値が小さくなります。したがって、ORDER BY birthday ASC で並べると若い順、ORDER BY birthday DESC で並べると年齢が高い順になります。

このように、ORDER BY句はWHERE句の後に来るということは覚えておきましょう。

＜結果の例＞

order by birthday
実行結果の例

ORDER BY句の例文　キー列が複数

ORDER BYの第一条件の値が同じ場合、第二条件でさらに並べ替えられます。例えば、郵便番号で並べた後、同じ郵便番号内で誕生日が新しい順に並べるには以下のように記述します。

以下6_5.sqlを見てください。

SELECT 
    *
FROM
    sip_a.customers
ORDER BY postalCode , birthday DESC;

＜結果の例＞

order by two columns
実行結果の例

今回名前や住所では並べ替えを行いませんでした。なぜなら、並べ替えの基準が文字コードだからです。アルファベットでしたらabc順に並べ替えることができます。しかし、日本語の場合はあいうえお順で並んでほしいのが一般的かと思います。その場合は、別途ひらがなフィールドを設けるなどの工夫が必要になるというのは前述のとおりです。

1.1. ソートの例題

例題

customers表を最初にprefの降順で並べ替え、同じ場合はbirthdayの昇順で並べ替えて全てのフィールドを抽出しなさい。（あまり意味はないが、、、）

＜解答例＞

2. グループ化

同じ値を持つデータごとにグループ化するにはGROUP BY句を使用します。

グループ化の構文

SELECT 列名1,列名2, ... FROM 表名

WHERE 抽出条件

GROUP BY グループ化の基準となる列名;

GROUP BY句を使ったグループ化

例えば、以下6_7.sqlはcustomersテーブルに存在しているprefをグループ化して抽出します。

※GROUP BYは、特定の列ごとにデータをグループ化するためのSQL句です。ORDER BYを指定しない場合、結果の並び順は保証されません。

SELECT 
    pref
FROM
    sip_a.customers
GROUP BY pref;

＜結果の例＞

pref
実行結果の例

以下6_8.sqlのようにGROUP BY句に指定していないフィールド（例：name）をSELECTの後のフィールド名に置くとエラーになるので気をつけてください。

SELECT 
    pref, name
FROM
    sip_a.customers
GROUP BY pref;
6_8
実行結果の例

GROUP BY句を使用した場合にSELECT文の後ろのフィールド名に指定できるのは、

  1. GROUP BY句 でグループ化の対象になっているフィールド
  2. SUM(フィールド名)のように集計関数の対象になっているフィールド

のいずれかでなければなりません。なぜなら、表示の整合性が保たれないからです。

MySQLの例外

SQL標準では、GROUP BYを使う際に、SELECTで指定するカラムは集約関数でない限り、GROUP BY句に含めなければなりませんすべてGROUP BY句に含めことになっています。しかし、MySQLの場合GROUP BY句に主キーを含んでいる場合は例外で、主キーにより一意性が保証されるため、MySQLの仕様上エラーになりません。ただし、これはONLY_FULL_GROUP_BYという設定が無効になっている場合に限られます。

SQL標準とは異なる挙動のため、誤解やバグを防ぐためにも、原則として明示的に表示するすべての列名をGROUP BYに含める習慣を持つことが大切です。

WHERE句とGROUP BY句の組み合わせ

以下6_9.sqlは生年月日が2000-01-01以降の人がいる都道府県をグループ化して抽出します。

SELECT 
    pref
FROM
    sip_a.customers
WHERE
    birthday >= '2001-01-01'
GROUP BY pref;

＜結果の例＞

実行結果の例

3. 集約関数

合計や平均を求めるには集約関数を使います。集約関数は1つの列グループに対して演算結果を抽出します。列グループを指定しない場合はテーブル全体が列グループになります。

集約関数意味
SUM(引数列)引数列の合計値を取得する
AVG (引数列) 引数列の平均値を取得する
MAX (引数列) 引数列の最大値を取得する
MIN (引数列) 引数列の最小値を取得する
COUNT (引数列) 引数列のデータの件数を取得する
表6.1

以下の例を見てください。

以下6_10.sqlはcars表から、priceの合計、平均、最大値、最小値、件数を抽出します。

SELECT 
    SUM(price), AVG(price), MAX(price), MIN(price), COUNT(price)
FROM
    sip_a.cars;

＜結果の例＞

実行結果の例

COUNTは引数に「*」を指定するとレコード数を取得します。（NULL値を含む全ての行がカウントされる）

また、COUNT引数にフィールド名を指定するとNULL値の行を除くすべての行がカウントされます。ちなみに、SUM, AVG などの集約関数も NULL を無視します。

以下6_11.sqlはcars表から、cars表のレコード件数とdeleted_atの件数を抽出します。

SELECT 
    COUNT(*), COUNT(deleted_at)
FROM
    sip_a.cars;

＜結果の例＞

実行結果の例

例題

cars表から「現在販売中の車の数」を取得しなさい。

実行結果の例

3.1. 集約関数とグループ化の組み合わせ

集約関数は先に見たグループ化と組み合わせて使用することが良くあります。グループ化と集約関数を組み合わせることにより「○○ごとの××」といった集計が可能になります。例えば、「商品ごとの売上合計」や「クラスごとの平均点数」といった集計です。

以下6_12.sqlはcustomers表から、都道府県ごとの人数を取得します。

SELECT 
   pref, count(*)
FROM
    sip_a.customers
GROUP BY pref;

＜結果の例＞

実行結果の例

グループ化した結果を並べ替えることもできます。

以下、6_13.sqlはcustomers表から、都道府県ごとの人数を取得し、人数が多い順に並べ替えています。

SELECT 
    pref, COUNT(*)
FROM
    sip_a.customers
GROUP BY pref
ORDER BY COUNT(*) DESC;

＜結果の例＞

実行結果の例

例題

customers表から以下のように都道府県ごとの、人数、一番若い人の誕生日、一番歳をとった人の誕生日を取得し、人数の降順に並べ替えなさい。

実行結果の例

3.2. グループ化した結果に抽出条件を加える

GROUP BY句でグループ化した結果に対して抽出条件で絞り込みを行いたい場合はHAVING句を使います。WHERE句の抽出条件と似ていますが、 1件1件のレコードではなく、グループ化した結果に対して条件で絞り込んで抽出するところがポイントです。

以下の6_14.sqlはcustomers表から、都道府県ごとの人数を取得し、5人超の prefと人数を抽出します。

SELECT 
    pref, COUNT(*)
FROM
    sip_a.customers
GROUP BY pref
HAVING COUNT(*) > 5;

＜結果の例＞

実行結果の例

このようにGROUP BYと組み合わせて使うのが HAVING句です。WHEREは1行ごとのデータを対象に条件を指定し、HAVINGはグループ化されたデータに条件を適用します。

＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞

□ 検索結果のソート、グループ化と集約関数の考え方4ステップ(例:1989年以降に生まれた顧客が2名以上いる都道府県の名前と人数を人数の降順に並べ替えたい)

抽出条件までを記述する

SELECT 
    pref
FROM
    sip_a.customers
WHERE
    birthday >= '1989-01-01'
STEP
1

グループ化して集約関数を加える

SELECT 
    pref, COUNT(*)
FROM
    sip_a.customers
WHERE
    birthday >= '1989-01-01'
GROUP BY pref
STEP
2

グループ化した結果に対してHAVING句で抽出条件を加える

SELECT 
    pref, COUNT(*)
FROM
    sip_a.customers
WHERE
    birthday >= '1989-01-01'
GROUP BY pref
HAVING COUNT(*) > 2
STEP
3

最後にORDER BY句で並べ替える

SELECT 
    pref, COUNT(*)
FROM
    sip_a.customers
WHERE
    birthday >= '1989-01-01'
GROUP BY pref
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;
STEP
4

□ 問い合わせの結果を並べ替えるにはORDER BY句を使用する

□ 並べ替えには昇順（ASC）と降順（DESC）がある

□ 同じ値を持つデータごとにグループ化するにはGROUP BY句を使用する

□ 集約関数はグループ化と組み合わせて使用することが多い（○○ごとの××）

□ 1件1件のレコードではなく、グループ化した結果に対して抽出条件で絞り込みを行いたい場合はHAVING句を使う

Success

何度でも受けられるテストへのリンク

以上、検索結果のソート、グループ化と集約関数について見てきました。

次回は複数テーブルに対する検索について見ていきましょう。

データベース（MySQL）へ戻る