Print Friendly, PDF & Email

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

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

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

検索結果のソート

以下のような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句の例文 昇順

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順に並べ替えることができます。

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

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

ソートの例題

例題1

customers表を最初にprefecture_idの降順で並べ替え、同じ場合は誕生日の昇順で並べ替えて全ての列を抽出しなさい。

<解答例>

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

グループ化

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

グループ化の構文

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

WHERE 抽出条件

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

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

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

SELECT 
    prefecture_id
FROM
    sip_a.customers
GROUP BY prefecture_id;

<結果を表示する>

新人エンジニア研修

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

以下のようにGROUP BY句を指定していない列をSELECTの後の列名に置くと無意味なデータが抽出されるので気をつけてください。

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つの列グループに対して演算結果を抽出します。

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

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

※引数に「*」を指定するとレコード数を取得する。(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)へ戻る