検索結果のソート、グループ化と集約関数

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

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

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

検索結果のソート

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

なお、データベース検索の結果の行の表示には特定の順序がない事は知っておきましょう。

この例では、たまたま主キーであるcustomer_idの昇順に並べ替えがなされています。

しかし、この並び順は保証されていません。

確実に意図した順番で表示したいときは並べ替えを行う必要があります。

なお、並べ替えのことをソートとも言います。

※データは以前の研修で作成したものを使ってください。

問い合わせの結果を並べ替えるにはORDER BY句を利用します。

また、並べ替えには昇順（ASC）と降順（DESC）があります。

それぞれ、英語の「ascending」と「descending」の略です。

このとき、 並べ替えキー列1 が同じ時には 並べ替えキー列2 で並べ替えが行われます。

また、ASC,DESCを指定しなかった場合は ASCが指定されているものとみなされます。

ORDER BY句の例文　昇順

SELECT 
    *
FROM
    sip_a.customers
ORDER BY prefecture_id;

＜結果を表示する＞

（中略）

ORDER BY句の例文　降順

SELECT 
    *
FROM
    sip_a.customers
ORDER BY prefecture_id DESC;

＜結果を表示する＞

（中略）

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

例えば、customersテーブルに対して以下のSQL文を発行したとします。

SELECT 
    *
FROM
    sip_a.customers
ORDER BY customer_name;

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

“いもと”さんと“まるた”さんの並び順が昇順になっていません。

そこで実務では名前など並べ替えの必要のある項目はカタカナやひらがなの列も作るのが一般的です。（今回は研修のため省略しています）

なぜ、このような並び順になるかということですが、それは文字コードの仕組みに秘密があります。

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

数値、アルファベット、カタカナまではCodeが読みの順に並んでいますが、漢字はそうなっていませんね。（もっとも漢字は色々な読み方がありますが…）

というわけで漢字での並べ替えはしない、と思っていただいてよろしいかと思います。

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

次に、prefecture_idが23（愛知県）の人のcustomer_idを年齢の高い順に並べ替えてみましょう。年齢が高いとは即ちcustomers_birthdayの値が小さい順ということになります。

SELECT 
    *
FROM
    sip_a.customers
WHERE
    prefecture_id = 23
ORDER BY customers_birthday;

＜結果を表示する＞

（中略）

このように、ORDER BY句はWHERE句の後に来ます。

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

ORDER BY句に指定された列に同じ値が存在するとそれらのレコードの抽出順は不定となります。

例えば、customersテーブルをprefecture_idの昇順に並べ替えた上で、同じ prefecture_id の人は customers_birthday の降順に並べ替える場合はSQL文は以下のようになります。

SELECT 
    *
FROM
    sip_a.customers
ORDER BY prefecture_id , customers_birthday DESC;

＜結果の例＞

今回名前や住所では並べ替えを行いませんでした。

なぜなら、並べ替えの基準が文字コードだからです。

アルファベットでしたらabc順に並べ替えることができます。

しかし、日本語の場合はあいうえお順で並んでほしいのが一般的かと思います。

その場合は、別途ひらがな列を設けるなどの工夫が必要になるというのは前述のとおりです。

ソートの例題

SELECT 
    *
FROM
    sip_a.customers
ORDER BY prefecture_id DESC , customers_birthday;

グループ化

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

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

例えば、customersテーブルに存在しているprefecture_idをグループ化して抽出してみます。

SELECT 
    prefecture_id
FROM
    sip_a.customers
GROUP BY prefecture_id;

＜結果を表示する＞

※ORDER BY句を指定しないと抽出結果の順番はランダムであることに注意してください。

SELECT 
    prefecture_id, customer_name
FROM
    sip_a.customers
GROUP BY prefecture_id;

ここに表示された3名の名前はたまたま表示されただけで無意味なデータです。

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

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

のいずれかでなければなりません。

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

生年月日が2000-01-01以降の人がいる都道府県番号をグループ化して抽出します。

SELECT 
    prefecture_id
FROM
    sip_a.customers
WHERE
    customers_birthday >= '2001-01-01'
GROUP BY prefecture_id;

＜結果を表示する＞

集約関数

合計や平均を求めるには集約関数を使います。

集約関数は1つの列グループに対して演算結果を抽出します。

列グループを指定しない場合はテーブル全体が列グループになります。

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

引数に列名を指定するとNULL値の行を除くすべての行がカウントされる。

cars表から、priceの合計、平均、最大値、最小値、件数を抽出する。

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

＜結果を表示する＞

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

集約関数は先に見たグループ化と組み合わせて使用することが良くあります。

グループ化と集約関数を組み合わせることにより、例えば、商品ごとの売上合計やクラスごとの平均点数といった集計が可能になります。

customers表から、都道府県ごとの人数を取得する。

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

結果はご自身で確認ください。

customers表から、都道府県ごとの人数を取得し、都道府県コードの昇順に並べ替える。

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

結果はご自身で確認ください。

例題1

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

SELECT 
    prefecture_id, COUNT(*) as '人数',min(customers_birthday) as '一番若い人の誕生日' ,max(customers_birthday) as '一番歳をとった人の誕生日'
FROM
    sip_a.customers
GROUP BY prefecture_id
ORDER BY prefecture_id;

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

GROUP BY句でグループ化した結果に対して抽出条件で絞り込みを行いたい場合はHAVING句を使います。

WHERE句の抽出条件と似ていますが、 1件1件のレコードではなく、グループ化した結果に対して抽出するところがポイントです。

例えば、 customers表から、都道府県ごとの人数を取得し、30人以上の prefecture_idと人数を抽出する。

SELECT 
    prefecture_id, COUNT(*)
FROM
    sip_a.customers
GROUP BY prefecture_id
HAVING COUNT(*) > 30;

このようにGROUP BYと組み合わせて使うのが HAVING句です。

まとめ

• 検索結果のソート、グループ化と集約関数の考え方3ステップ

1.抽出条件までを記述する

SELECT
　prefecture_id
FROM
　sip_a.customers
WHERE
　customers_birthday >= ' 2001-01-01 ';

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

SELECT
　prefecture_id,count(*)
FROM
　sip_a.customers
WHERE
　customers_birthday >= '2001-01-01'
GROUP BY prefecture_id;

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

SELECT
　prefecture_id,count()
FROM
　sip_a.customers
WHERE
　customers_birthday >= '2001-01-01'
GROUP BY prefecture_id
HAVING COUNT(*) > 1;

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

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

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