SQLにおけるGROUP BYを完全に理解する

はじめに

SQLのGROUP　BYについてあまり理解できていなかったので改めて勉強してまとめました

環境

今回はSQLの確認だけですので
paiza.ioを使用していきます
以下リンクからpaiza.ioにアクセスして左上の緑の言語選択のところからMySQLを選択します

コード入力欄に以下をそのままコピペします。
データの中身は適当です。WBC仕様にしましたが名前以外は適当です。

テーブル作成

CREATE TABLE members
    (
        id     INTEGER NOT NULL,
        name   CHAR(32),
        height REAL,
        weight REAL,
        age INTEGER,
        position_id INTEGER,
        PRIMARY KEY (id)
    )
;

CREATE TABLE positions
    (
        id     INTEGER NOT NULL,
        name   CHAR(32),
        salary INTEGER,
        PRIMARY KEY (id)
    );

INSERT
    INTO members(id, name, height, weight, age, position_id)
    VALUES
        (1, 'ヌートバー', 190.2, 65.2, 60, 1),
        (2, '近藤', 170.5, 50.3, 35, 1),
        (3, '大谷', 193.1, 98.1, 28, 2),
        (4, '村上', 183.5, 70.6, 36, 3),
        (5, '吉田', 178.8, 55.8, 62, 1),
        (6, '岡本', 177.0, 65.3, 75, 3),
        (7, '山田', 181.4, 49.1, 25, 3),
        (8, '中野', 179.1, 56.9, 45, 3),
        (9, '甲斐', 173.7, 90.1, 38, 4),
        (10, '山本', 180.6, 78.5, 26, 5);
        
INSERT
    INTO positions(id, name)
    VALUES
        (1, '外野', 1232),
        (2, 'DH', 3000),
        (3, '内野', 515),
        (4, 'キャッチャー', 1024),
        (5, 'ピッチャー', 542);

membersテーブルとjobsテーブルをそれぞれ作成して初期データを流し込んでいます。
membersテーブルのjob_idとjobsテーブルのidは紐付いています。

このテーブルをもとにGROUP　BYを完全に理解していきましょう

GROUP BYの基本

基本的な書き方です

GROUP BY

SELECT カラム名　FROM テーブル名　GROUP BY 集約キー；

※集約キーとはテーブルの切り分け方を指定する列

　例　position_idごとの人数をカウントする

SELECT position_id,COUNT(*) FROM members GROUP BY position_id;

このSQLの流れは以下のイメージ

1. membersテーブルを、position_idが共通のもので切り分けてグループ化する
   └position_idが１のグループ（ヌートバー、近藤、吉田）、２のグループ(大谷)、３のグループ（村上、岡本、山田、中野）...って感じで

２. その分割したグループごとにCOUNT(＊)でレコード数をカウントする

GROUP　BYの間違えやすいポイント

１. SELECT句に書くことができるカラムは　定数、GROUP　BY句で指定したカラム名、集約関数　に制限される。
　　例えば以下のようなSQLは書けない

NG

SELECT position_id,name FROM members GROUP BY position_id;

例えばposition_idが１のとき、nameカラムの値にはヌートバー、近藤、吉田の３つあるのでどれを表示していいかわからなくなるから禁止されている。

２.GROUP　BY句にASで命名した別名は指定できない
例えば以下みたいな感じ

NG

SELECT position_id　AS "ポジション"　FROM members GROUP BY "ポジション";

SQLにおいてはSELECT句がGROUP　BY句よりあと実行されるので
GROUP　BY実行時にはまだASによる命名がされておらずエラーになる。

3.並び替えはしてくれない
　並び替えるにはORDER BYを使用すること。

集計関数のおさらい

GROUP　BYと一緒に使われる集計関数の基本５つを復習しておきます。
・COUNT関数
指定した項目の件数を返す

　例　position_idごとの人数をカウントする

SELECT position_id,COUNT(*) FROM members GROUP BY position_id;

・SUM関数
指定された項目の合計を返す
値がNULLの場合は除外して集計します。

　例　position_idごとの体重合計を表示する

SELECT position_id,SUM(weight) FROM members GROUP BY position_id;

・AVG関数
指定された項目の平均値を返す
値がNULLの場合は除外して集計します。

　例　position_idごとの体重平均を表示する

SELECT position_id,AVG(weight) FROM members GROUP BY position_id;

・MAX関数
指定された項目の最大値を返す
値がNULLの場合は除外して集計します。

　例　position_idごとの体重最大値を表示する

SELECT position_id,MAX(weight) FROM members GROUP BY position_id;

・MIN関数
指定された項目の最小値を返す
値がNULLの場合は除外して集計します。

　例　position_idごとの体重最小値を表示する

SELECT position_id,MIN(weight) FROM members GROUP BY position_id;

INNER JOINも一緒につかってみる

positionsテーブルをJOINさせて名前とお給料を平均体重をそれぞれGROUPごとに表示するSQL

INNER　JOIN

SELECT positions.name, salary ,AVG(weight) AS "平均体重"  FROM members INNER JOIN positions ON members.position_id = positions.id GROUP BY position_id;

まとめ

改めて勉強すると理解が深まってよかったです。

参考

わかりやすかった。（宣伝じゃないよ）