データの整列と集約関数

今日は、『実践Pythonによるデータベース入門』の第3章 "SQL―基本的な問い合わせ―” で学んだことをアウトプットします。

データの整列

データの整列（ソート）は、問い合わせのSELECT文にORDER BY句を指定して行います。書き方は以下のようになります。

SELECT 列名 FROM テーブル名 WHERE 条件式 ORDER BY 列名 [DESC] [LIMIT [開始位置m,] 件数n];

ここで、角括弧[ ]の中にあるものは、オプションです。

整列の順序については、デフォルトでは昇順となっています。降順にソートをしたいときにはDESCを指定します。

また、LIMITオプションを指定することによって、ソート結果のm番目以降のn件を取り出すことができます。

【例題3.1】都道府県テーブルについて、SQL命令文を用いて以下の要求を実現し、それをMySQL上で実行し、動作確認をせよ。

仕様要求
都道府県データを人口の昇順で並び替え、全列の全件を表示する

mysql> USE todoufukenbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM todoufuken ORDER BY jinkou;
+--------------+-----------------+----------+---------+
| kenmei       | kenchou         | jinkou   | menseki |
+--------------+-----------------+----------+---------+
| 鳥取県       | 鳥取市          |   551402 | 3507.14 |
| 島根県       | 松江市          |   666941 | 6707.89 |
| 高知県       | 高知市          |   689785 | 7103.63 |
| 徳島県       | 徳島市          |   721269 | 4146.75 |
| 福井県       | 福井市          |   762679 | 4190.52 |
| 山梨県       | 甲府市          |   806210 | 4465.27 |
| 佐賀県       | 佐賀市          |   808821 | 2440.69 |
| 和歌山県     | 和歌山市        |   914055 | 4724.65 |
| 香川県       | 高松市          |   948801 | 1876.78 |
| 秋田県       | 秋田市          |   952069 | 11637.5 |
| 富山県       | 富山市          |  1034670 | 4247.58 |
| 宮崎県       | 宮崎市          |  1063759 | 7735.22 |
| 山形県       | 山形市          |  1064954 | 9323.15 |
| 大分県       | 大分市          |  1124983 | 6340.76 |
| 石川県       | 金沢市          |  1130159 | 4186.21 |
| 岩手県       | 盛岡市          |  1212201 |   15275 |
| 青森県       | 青森市          |  1230535 | 9645.64 |
| 長崎県       | 長崎市          |  1310660 | 4130.98 |
| 奈良県       | 奈良市          |  1322970 | 3690.94 |
| 愛媛県       | 松山市          |  1326487 | 5676.19 |
| 山口県       | 山口市          |  1341506 | 6112.54 |
| 滋賀県       | 大津市          |  1412415 | 4017.38 |
| 沖縄県       | 那覇市          |  1458839 | 2282.59 |
| 鹿児島県     | 鹿児島市        |  1587342 | 9187.06 |
| 熊本県       | 熊本市          |  1735901 | 7409.46 |
| 三重県       | 津市            |  1767615 | 5774.49 |
| 福島県       | 福島市          |  1830114 | 13784.1 |
| 岡山県       | 岡山市          |  1882356 | 7114.33 |
| 群馬県       | 前橋市          |  1926370 | 6362.28 |
| 栃木県       | 宇都宮市        |  1932091 | 6408.09 |
| 岐阜県       | 岐阜市          |  1974142 | 10621.3 |
| 長野県       | 長野市          |  2034145 | 13561.6 |
| 新潟県       | 新潟市          |  2199746 |   12584 |
| 宮城県       | 仙台市          |  2292690 | 7282.29 |
| 京都府       | 京都市          |  2568427 |  4612.2 |
| 広島県       | 広島市          |  2794862 | 8479.65 |
| 茨城県       | 水戸市          |  2854131 | 6097.39 |
| 静岡県       | 静岡市          |  3618972 | 7777.35 |
| 福岡県       | 福岡市          |  5106774 | 4986.51 |
| 北海道       | 札幌市          |  5212462 | 83424.4 |
| 兵庫県       | 神戸市          |  5438891 | 8401.02 |
| 千葉県       | 千葉市          |  6281394 | 5157.57 |
| 埼玉県       | さいたま市      |  7343453 | 3797.75 |
| 愛知県       | 名古屋市        |  7541123 | 5173.07 |
| 大阪府       | 大阪市          |  8817372 | 1905.32 |
| 神奈川県     | 横浜市          |  9214151 | 2416.11 |
| 東京都       | 新宿区          | 13971109 | 2194.03 |
+--------------+-----------------+----------+---------+

【例題3.2】都道府県テーブルについて、SQL命令文を用いて以下の要求を実現し、それをMySQL上で実行し、動作確認をせよ。

仕様要求
都道府県データを面積の降順で並び替え、全項目の全件を表示する

mysql> USE todoufukenbase;
mysql> SELECT * FROM todoufuken ORDER BY menseki DESC;
+--------------+-----------------+----------+---------+
| kenmei       | kenchou         | jinkou   | menseki |
+--------------+-----------------+----------+---------+
| 北海道       | 札幌市          |  5212462 | 83424.4 |
| 岩手県       | 盛岡市          |  1212201 |   15275 |
| 福島県       | 福島市          |  1830114 | 13784.1 |
| 長野県       | 長野市          |  2034145 | 13561.6 |
| 新潟県       | 新潟市          |  2199746 |   12584 |
| 秋田県       | 秋田市          |   952069 | 11637.5 |
| 岐阜県       | 岐阜市          |  1974142 | 10621.3 |
| 青森県       | 青森市          |  1230535 | 9645.64 |
| 山形県       | 山形市          |  1064954 | 9323.15 |
| 鹿児島県     | 鹿児島市        |  1587342 | 9187.06 |
| 広島県       | 広島市          |  2794862 | 8479.65 |
| 兵庫県       | 神戸市          |  5438891 | 8401.02 |
| 静岡県       | 静岡市          |  3618972 | 7777.35 |
| 宮崎県       | 宮崎市          |  1063759 | 7735.22 |
| 熊本県       | 熊本市          |  1735901 | 7409.46 |
| 宮城県       | 仙台市          |  2292690 | 7282.29 |
| 岡山県       | 岡山市          |  1882356 | 7114.33 |
| 高知県       | 高知市          |   689785 | 7103.63 |
| 島根県       | 松江市          |   666941 | 6707.89 |
| 栃木県       | 宇都宮市        |  1932091 | 6408.09 |
| 群馬県       | 前橋市          |  1926370 | 6362.28 |
| 大分県       | 大分市          |  1124983 | 6340.76 |
| 山口県       | 山口市          |  1341506 | 6112.54 |
| 茨城県       | 水戸市          |  2854131 | 6097.39 |
| 三重県       | 津市            |  1767615 | 5774.49 |
| 愛媛県       | 松山市          |  1326487 | 5676.19 |
| 愛知県       | 名古屋市        |  7541123 | 5173.07 |
| 千葉県       | 千葉市          |  6281394 | 5157.57 |
| 福岡県       | 福岡市          |  5106774 | 4986.51 |
| 和歌山県     | 和歌山市        |   914055 | 4724.65 |
| 京都府       | 京都市          |  2568427 |  4612.2 |
| 山梨県       | 甲府市          |   806210 | 4465.27 |
| 富山県       | 富山市          |  1034670 | 4247.58 |
| 福井県       | 福井市          |   762679 | 4190.52 |
| 石川県       | 金沢市          |  1130159 | 4186.21 |
| 徳島県       | 徳島市          |   721269 | 4146.75 |
| 長崎県       | 長崎市          |  1310660 | 4130.98 |
| 滋賀県       | 大津市          |  1412415 | 4017.38 |
| 埼玉県       | さいたま市      |  7343453 | 3797.75 |
| 奈良県       | 奈良市          |  1322970 | 3690.94 |
| 鳥取県       | 鳥取市          |   551402 | 3507.14 |
| 佐賀県       | 佐賀市          |   808821 | 2440.69 |
| 神奈川県     | 横浜市          |  9214151 | 2416.11 |
| 沖縄県       | 那覇市          |  1458839 | 2282.59 |
| 東京都       | 新宿区          | 13971109 | 2194.03 |
| 大阪府       | 大阪市          |  8817372 | 1905.32 |
| 香川県       | 高松市          |   948801 | 1876.78 |
+--------------+-----------------+----------+---------+

【例題3.3】都道府県テーブルについて、SQL命令文を用いて以下の要求を実現し、それをMySQL上で実行し、動作確認をせよ。

仕様要求
（1）都道府県データを人口の昇順で並び替え、上位の10件を表示する
（2）都道府県データを人口の昇順で並び替え、4件目から5件を表示する
（3）都道府県データを面積の降順で並び替え、上位の10件を表示する
（4）都道府県データを面積の降順で並び替え、4件目から5件を表示する

（1）都道府県データを人口の昇順で並び替え、上位の10件を表示する

mysql> SELECT * FROM todoufuken ORDER BY jinkou LIMIT 10;
+--------------+--------------+--------+---------+
| kenmei       | kenchou      | jinkou | menseki |
+--------------+--------------+--------+---------+
| 鳥取県       | 鳥取市       | 551402 | 3507.14 |
| 島根県       | 松江市       | 666941 | 6707.89 |
| 高知県       | 高知市       | 689785 | 7103.63 |
| 徳島県       | 徳島市       | 721269 | 4146.75 |
| 福井県       | 福井市       | 762679 | 4190.52 |
| 山梨県       | 甲府市       | 806210 | 4465.27 |
| 佐賀県       | 佐賀市       | 808821 | 2440.69 |
| 和歌山県     | 和歌山市     | 914055 | 4724.65 |
| 香川県       | 高松市       | 948801 | 1876.78 |
| 秋田県       | 秋田市       | 952069 | 11637.5 |
+--------------+--------------+--------+---------+

（2）都道府県データを人口の昇順で並び替え、4件目から5件を表示する

mysql> SELECT * FROM todoufuken ORDER BY jinkou LIMIT 4,5;
+--------------+--------------+--------+---------+
| kenmei       | kenchou      | jinkou | menseki |
+--------------+--------------+--------+---------+
| 福井県       | 福井市       | 762679 | 4190.52 |
| 山梨県       | 甲府市       | 806210 | 4465.27 |
| 佐賀県       | 佐賀市       | 808821 | 2440.69 |
| 和歌山県     | 和歌山市     | 914055 | 4724.65 |
| 香川県       | 高松市       | 948801 | 1876.78 |
+--------------+--------------+--------+---------+

（3）都道府県データを面積の降順で並び替え、上位の10件を表示する

mysql> SELECT * FROM todoufuken ORDER BY menseki DESC LIMIT 10;
+--------------+--------------+---------+---------+
| kenmei       | kenchou      | jinkou  | menseki |
+--------------+--------------+---------+---------+
| 北海道       | 札幌市       | 5212462 | 83424.4 |
| 岩手県       | 盛岡市       | 1212201 |   15275 |
| 福島県       | 福島市       | 1830114 | 13784.1 |
| 長野県       | 長野市       | 2034145 | 13561.6 |
| 新潟県       | 新潟市       | 2199746 |   12584 |
| 秋田県       | 秋田市       |  952069 | 11637.5 |
| 岐阜県       | 岐阜市       | 1974142 | 10621.3 |
| 青森県       | 青森市       | 1230535 | 9645.64 |
| 山形県       | 山形市       | 1064954 | 9323.15 |
| 鹿児島県     | 鹿児島市     | 1587342 | 9187.06 |
+--------------+--------------+---------+---------+

（4）都道府県データを面積の降順で並び替え、4件目から5件を表示する

mysql> SELECT * FROM todoufuken ORDER BY menseki DESC LIMIT 4, 5;
+-----------+-----------+---------+---------+
| kenmei    | kenchou   | jinkou  | menseki |
+-----------+-----------+---------+---------+
| 新潟県    | 新潟市    | 2199746 |   12584 |
| 秋田県    | 秋田市    |  952069 | 11637.5 |
| 岐阜県    | 岐阜市    | 1974142 | 10621.3 |
| 青森県    | 青森市    | 1230535 | 9645.64 |
| 山形県    | 山形市    | 1064954 | 9323.15 |
+-----------+-----------+---------+---------+

集約関数

与えられたテーブルについて、特定のキーで、レコードをまとめて集計を行うことをグループ化と言います。

グループ化はSELECT文にGROUP BY句を付け加えて行います。
また、グループ内の項目に対して、カウント、平均、総和などの集約関数を用いることができます。主な集約関数は以下のとおり。

GROUP BY句を用いたSELECT文の書き方：
→SELECT 集約関数名（列名） FROM 表名 GROUP BY 列名;

GROUP BY句によってグループ化したあとに、さらにHAVING句を付け加えることによって、条件を設定してレコードを抽出することができます。

SELECT 集約関数名（列名） FROM 表名 GROUP BY 列名 HAVING 条件式;

【例題3.4】2章で作成したテーブルmemberlistについて、SQL命令文を用いて以下の要求を実現し、それをMySQL上で実行し、動作確認をせよ。

仕様要求
（1）男女別の会員の件数を表示する
（2）男女別の20代の会員の件数を表示する
（3）男女別の20代の会員の件数が1件のみの性別を表示する

（1）男女別の会員の件数を表示する

mysql> USE testbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT gender, COUNT(name) FROM memberlist GROUP BY gender;
+--------+-------------+
| gender | COUNT(name) |
+--------+-------------+
| 女     |           2 |
| 男     |           2 |
+--------+-------------+
2 rows in set (0.36 sec)

GROUP BY gender句によって、memberlistをグループ化して、そのname列の件数をカウントして表示しました。

（2）男女別の20代の会員の件数を表示する

mysql> SELECT gender, COUNT(name) FROM memberlist WHERE age>=20 AND age<30 GROUP BY gender;
+--------+-------------+
| gender | COUNT(name) |
+--------+-------------+
| 女     |           2 |
| 男     |           1 |
+--------+-------------+
2 rows in set (0.00 sec)

（1）のSQL命令文にWHERE age>=20 AND age<30 句を付け加えて、20代のみを対象とします。テーブルにもともとある列名に関する条件式はWHERE句を用いて記述します。

（3）男女別の20代の会員の件数が1件のみの性別を表示する

mysql> SELECT gender, COUNT(name) FROM memberlist WHERE age>=20 AND age<30 GROUP BY gender HAVING COUNT(name) =1;
+--------+-------------+
| gender | COUNT(name) |
+--------+-------------+
| 男     |           1 |
+--------+-------------+
1 row in set (0.03 sec)

（2）のSQL命令文にさらにHAVING COUNT(name)=1句を付け加えて、件数が1件のみの性別を表示しました。グループ化によって集約後の列名に関する条件式はHAVING句を用いて記述します。