LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@takumi_links(Takumi Kuroiwa)

SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第4回）

Last updated at Posted at 2024-08-28

概要

集約関数

  • SQLには集計用の関数が多く用意されている

    • 集約：複数行を1つの行にまとめること
    • 集約関数：複数行のデータを1つのデータにまとめる関数

  • 集約関数の種類は以下（テーブルで）

関数 意味
COUNT テーブルのレコード数（行数）を数える
SUM テーブルの数値列の合計値を求める
AVG テーブルの数値列の平均値を求める
MAX テーブルの任意の列のデータの最大値を求める
MIN テーブルの任意の列のデータの最小値を求める

COUNT

  • COUNTの注意点
    • テーブル全行を数える=NULLを含む行数：COUNT(*)
    • NULL以外の行を数える：COUNT(列名) 
mysql> SELECT * FROM Shohin;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM Shohin;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(shiire_tanka) FROM Shohin;
+---------------------+
| COUNT(shiire_tanka) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)


mysql> SELECT shohin_mei, shohin_bunrui FROM Shohin WHERE hanbai_tanka <> 500;
+-----------------+------------------+
| shohin_mei      | shohin_bunrui    |
+-----------------+------------------+
| T-shirt         | Clothing         |
| Dress Shirt     | Clothing         |
| Kitchen Knife   | Kitchen Supplies |
| Pressure Cooker | Kitchen Supplies |
| Grater          | Kitchen Supplies |
| Ballpoint Pen   | Office Supplies  |
+-----------------+------------------+
6 rows in set (0.01 sec)

mysql> SELECT shohin_mei, shohin_bunrui, hanbai_tanka FROM Shohin WHERE hanbai_tanka >= 1000;
+-----------------+------------------+--------------+
| shohin_mei      | shohin_bunrui    | hanbai_tanka |
+-----------------+------------------+--------------+
| T-shirt         | Clothing         |         1000 |
| Dress Shirt     | Clothing         |         4000 |
| Kitchen Knife   | Kitchen Supplies |         3000 |
| Pressure Cooker | Kitchen Supplies |         6800 |
+-----------------+------------------+--------------+
4 rows in set (0.00 sec)

SUM

  • SUMの注意点
    • NULLを含む行は、合計値には含まれない
    • NULLは何個あっても全て無視される
mysql> SELECT SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin;
+-------------------+-------------------+
| SUM(hanbai_tanka) | SUM(shiire_tanka) |
+-------------------+-------------------+
|             16780 |             12210 |
+-------------------+-------------------+
1 row in set (0.01 sec)

AVG

  • AVG = (値の合計) / (値の個数)
  • AVGの注意点
    • NULLを含む行は、平均値には含まれない
    • NULLは何個あっても全て無視される
mysql> SELECT AVG(hanbai_tanka), AVG(shiire_tanka) FROM Shohin;
+-------------------+-------------------+
| AVG(hanbai_tanka) | AVG(shiire_tanka) |
+-------------------+-------------------+
|         2097.5000 |         2035.0000 |
+-------------------+-------------------+
1 row in set (0.01 sec)

MAX/MIN

  • MAX：最大値を求める, maximum(最大値)

  • MIN：最小値を求める, minimum(最小値)

  • 順序がつけられるデータであれば、最大値と最小値を求めることができる

    • 数値型、文字列型、日付型など
    • SUMとAVGは数値型の列にしか使えないが、MAXとMINは数値型以外の列にも使える 
mysql> SELECT MAX(hanbai_tanka), MIN(shiire_tanka) FROM Shohin;
+-------------------+-------------------+
| MAX(hanbai_tanka) | MIN(shiire_tanka) |
+-------------------+-------------------+
|              6800 |               320 |
+-------------------+-------------------+
1 row in set (0.01 sec)
             
mysql> SELECT MAX(torokubi), MIN(torokubi) FROM Shohin;
+---------------+---------------+
| MAX(torokubi) | MIN(torokubi) |
+---------------+---------------+
| 2009-11-11    | 2008-04-28    |
+---------------+---------------+
1 row in set (0.00 sec)

DISTINCT

  • distinct：重複するデータを除いて、異なるデータのみを取り出す

  • ex) 商品分類の個数を数える

    • 値の種類を数えたい時は、COUNT(DISTINCT 列名)を使う
mysql> SELECT COUNT(DISTINCT shohin_bunrui) FROM Shohin;
+-------------------------------+
| COUNT(DISTINCT shohin_bunrui) |
+-------------------------------+
|                             3 |
+-------------------------------+
1 row in set (0.02 sec)

GROUP BY句

  • めちゃ重要！！

    • GROUP BY句：テーブルをカットするナイフである
    • テーブルをいくつかのグループに切り分けて集約する
    • 句の記述順序：SELECT句→FROM句→WHERE句→GROUP BY句

  • ex) 商品分類ごとの個数を数える

    • 商品分類ごとにグループ分けして、グループごとの個数を数える

  • GROUP BY句に指定する列のことを集約キーやグループ化列と呼ぶ

    • テーブルをどう切り分けるか指定するための重要な列
mysql> SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui;
+------------------+----------+
| shohin_bunrui    | COUNT(*) |
+------------------+----------+
| Clothing         |        2 |
| Office Supplies  |        2 |
| Kitchen Supplies |        4 |
+------------------+----------+
3 rows in set (0.00 sec)
  • 集約キーにNULLが含まれる場合、「NULL」というグループに分類される
mysql> SELECT shiire_tanka, COUNT(*) FROM Shohin GROUP BY shiire_tanka;
+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|          500 |        1 |
|          320 |        1 |
|         2800 |        2 |
|         5000 |        1 |
|         NULL |        2 |
|          790 |        1 |
+--------------+----------+
6 rows in set (0.00 sec)
  • WHERE句で条件を指定してからGROUP BY句を使う
    • WHERE句で指定した条件で先にデータが絞り込まれてから、GROUP BY句でグループ化する
    • FROM→WHERE→GROUP BY→SELECTの順で実行される
mysql> SELECT shiire_tanka, COUNT(*) FROM Shohin WHERE shohin_bunrui = 'Clothing' GROUP BY shiire_tanka;
+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|          500 |        1 |
|         2800 |        1 |
+--------------+----------+
2 rows in set (0.00 sec)

集約関数とGROUP BY句の組み合わせで気を付けること

  • 注意点①：SELECT句には余計な列を書かない！
    • 集約関数を使った場合、SELECTに指定できるのは以下3つ
    • 定数、集約関数、GROUP BY句に指定した列（集約キー）
mysql> SELECT shohin_mei, shiire_tanka  FROM Shohin WHERE shohin_bunrui = 'Clothing';
+-------------+--------------+
| shohin_mei  | shiire_tanka |
+-------------+--------------+
| T-shirt     |          500 |
| Dress Shirt |         2800 |
+-------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT shohin_mei, shiire_tanka, COUNT(*) FROM Shohin WHERE shohin_bunrui = 'Clothing' GROUP BY shiire_tanka;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sample_db.Shohin.shohin_mei' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  • 注意点②：GROUP BY句に別名の列名は指定しない！
    • SELECT句では、列に別名をつけることができるが、GROUP BY句では別名を使うことは推奨されない
    • MySQL8.0以降では、GROUP BY句に別名の列名を使うことができる！
mysql> SELECT shiire_tanka AS 'tanka', COUNT(*) FROM Shohin WHERE shohin_bunrui = 'Clothing' GROUP BY tanka;
+-------+----------+
| tanka | COUNT(*) |
+-------+----------+
|   500 |        1 |
|  2800 |        1 |
+-------+----------+
2 rows in set (0.00 sec)
  • 注意点③：GROUP BY句は結果の順序を保証しない
    • GROUP BY句を使うと、結果の順序が保証されない、ランダムに並び替えられる
    • 並び順を指定したいときは、ORDER BY句を使う
mysql> SELECT shiire_tanka AS 'tanka', COUNT(*) FROM Shohin WHERE shohin_bunrui = 'Clothing' GROUP BY tanka;
+-------+----------+
| tanka | COUNT(*) |
+-------+----------+
|   500 |        1 |
|  2800 |        1 |
+-------+----------+
2 rows in set (0.00 sec)
  • 注意点④：集約関数を書く場所はSELECT句とHAVING句とORDER BY句のみ
    • WHERE句では集約関数を使うことができない
mysql> SELECT shiire_tanka, COUNT(*) FROM Shohin GROUP BY shiire_tanka Having COUNT(*) > 1;
+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|         2800 |        2 |
|         NULL |        2 |
+--------------+----------+
2 rows in set (0.00 sec)

mysql> SELECT shiire_tanka, COUNT(*) FROM Shohin GROUP BY shiire_tanka ORDER BY COUNT(*) desc;
+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|         2800 |        2 |
|         NULL |        2 |
|          500 |        1 |
|          320 |        1 |
|         5000 |        1 |
|          790 |        1 |
+--------------+----------+
6 rows in set (0.01 sec)

DISTINCTとGROUP BY句で同じ結果を得る

mysql> SELECT shohin_bunrui FROM Shohin GROUP BY shohin_bunrui;
+------------------+
| shohin_bunrui    |
+------------------+
| Clothing         |
| Office Supplies  |
| Kitchen Supplies |
+------------------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT shohin_bunrui FROM Shohin;
+------------------+
| shohin_bunrui    |
+------------------+
| Clothing         |
| Office Supplies  |
| Kitchen Supplies |
+------------------+
3 rows in set (0.00 sec)

まとめ

  • 本記事では「第3章：集約と並び替え」3-1: テーブルを集約して検索する, 3-2: テーブルをグループに切り分ける についてまとめました！

参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?