SQL学習②〜グループ化〜

SQLについて学ぶため、
『集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析 できるDigital Camp』で書かれている内容を自分用にまとめたもの第二弾です。
このnoteは、データのグループ化についての基本の内容になります。
詳しく知りたい方は、この本を買われるか（Kindle Unlimitedでは0円です）、他の書籍なども参考にしてみてください^^

SQLでの分析は、分類して、比較すること。
分類＝グループ化で行える
比較＝関数を利用した合計や平均などの集計値で行える

グループ化の概念

グループ化とは
・同じ値が入った複数のレコードをまとめて1行にすること
・量的なデータが格納されているフィールドの場合、合計や平均などの集計値を取り出すことができる

グループ化する基準により結果は異なる。また、複数のフィールドを基準としてグループ化することもできる。

GROUP BY構文

select フィールド名, 集計関数
from テーブル名
group by フィールド名

• フィールド名：グループ化に利用するフィールドを指定。select句とgroup by句には、同じフィールドを指定する。

• 集計関数：データの個数を数える「count」や合計する「sum」などの集計関数を指定

• テーブル名：SQLの実行対象とあるテーブルを指定

注意点
・グループ化の対象にしないフィールドをselect句に指定してはいけない
・量的なデータ（販売金額や個数など）のフィールドは集計関数で集計しなくてはいけない

---

集計関数

グループ化と組み合わせて分析を行うために必須な機能。
いずれも関数名に続けて、引数を半角カッコ「()」で囲んで指定する。

〜基本的な集計関数〜
・COUNT(*)　・・・グループ内のレコード数を数える
・COUNT(フィールド名)　・・・グループ内の値の個数を数える
・COUNT(DISTINCT フィールド名)　・・・グループ内の固有な値の個数を数える
・SUM(フィールド名)　・・・該当フィールドのグループ内の値を合計する
・AVG(フィールド名)　・・・該当フィールドのグループ内の値の平均を返す
・MAX(フィールド名)　・・・該当フィールドのグループ内の最大値を取得する
・MIN(フィールド名)　・・・該当フィールドのグループ内の最小値を取得する

データの個数を数えるCOUNT関数

COUNT関数は「数える」ための関数。
構文：「COUNT()」
→半角カッコに内に記述する引数は、「*」「フィールド名」「DISTINCT フィールド名」の3つのうちいずれかのパターン

①COUNT(*)　→"レコード数"
指定したフィールド名を基準にグループ化し、それぞれのレコード数を取得する。別フィールドに「null」があってもレコード数を返せる。

②COUNT(フィールド名)　→”データの個数”
指定したフィールド名ごとに、関数の引数として指定したフィールド名が何個かを取得する。

③COUNT(DISTINCT フィールド名)　→”ユニークな値の個数”
「DISTINCT」は英語で「別個の」という意味。
指定したフィールド名ごとに、何種類の固有な、関数の引数として指定したフィールド名があるかを取得する

GROUP BYを使わず、特定のフィールドでグループ化せずに、テーブル全体を対象として集計関数を使って各種集計値を取得することもできる。

集計結果の絞り込み

集計結果の絞り込みにはHAVING句を使う

グループ化したフィールドの絞り込みにWHEREは使えないため、HAVINGという命令を利用する。
ちなみに、WHERE句を利用できない理由としては、
WHERE句を記述した場合、SQLの内部的な処理の順序として、WHERE句で指定した命令が、GROUP BY句に夜グループ化より先に行われるため、WHERE句が実行されたときにはまだフィールドが存在せずエラーとなるからである。

select prefecture, count(distinct user_id) as users
from sample.customers
group by prefecture
having users >= 20
order by 2 desc

WHERE句とHAVING句は同時に使うこともでき、
WHERE句で絞り込んだ後にグループ化し、その集計結果をHAVING句で絞り込むという処理は成立する。

select prefecture, count(distinct user_id) as users
from sample.customers
where gender = 2
group by prefecture
having users >= 20
order by 2 desc

（4行目のグループ化実行の前に、3行目のWHERE句で「gender」フィールドの値が「2」に合致するレコードだけに絞り込みを実行している）

参考：各句の実行順序

1. FROM　：SQLを実行するテーブルを特定する

2. WHERE　：条件に照らし、どのレコードを対象とするかを決める

3. GROUP BY　：グループ化が指定されていれば、グループ化する

4. HAVING　：条件に照らし、どのグループを取得対象とするかを決める

5. SELECT　：取得する列を決める

6. ORDER BY　：表示する順序を決める

7. LIMIT　：表示するレコード数を決める

値そのものではなく、任意の分類でグループ化する

自由なグループ化のためには「IF」「CASE」の構文を利用する。

・IF文は「条件に当てはまればA、当てはまらなければB」
→2グループでの分類
・CASE文は「条件1に当てはまればA、条件2に当てはまればB、条件3に当てはまればC、どれにも当てはまらなければD」
→3グループ以上での分類

IFの構文

IF (条件式, TRUEの場合の値, FALSEの場合の値)

IF文とGROUP BY句、集計関数を組み合わせることで、任意のグループで集計した値を結果テーブルとして取得し、比較できるようになる。

select 
if (birthday >= "1989-01-08"
, "平成以降生まれ", "昭和以前生まれ") as era
, count(distinct user_id) as users
from sample.customers
group by era

IF文の条件式には、演算子AND（かつ）とOR（または）も利用できる。

select 
if (prefecture = "東京"
or prefecture = "神奈川"
or prefecture = "埼玉"
or prefecture = "千葉", "一都三県", "一都三県以外")
as pref_group
, count(distinct user_id) as users
from sample.customers
group by pref_group

（[prefecture]フィールドを対象とした条件式をORで繋げることで、「東京」「神奈川」「埼玉」「千葉」のいずれかに合致するなら「一都三県」、それ以外に合致するなら「一都三県以外」へのグループ化指定を実行している）

上記の場合、IN演算子を利用し、「IF ((prefecture IN ("東京", "神奈川", "埼玉", "千葉")) is true, "一都三県", "一都三県以外")」としても良い。

また、IS NULL演算子もIFの条件式に利用することができる。

CASEの構文

①条件を判定したいフィールド名をCASEに続けて記述する場合

CASE グループ化するフィールド名
WHEN 条件を判定したい値 THEN 該当した場合の値
WHEN 条件を判定したい値 THEN 該当した場合の値
WHEN 条件を判定したい値 THEN 該当した場合の値
ELSE いずれにも該当しない場合の値
END

②フィールド名を使った条件式をWHENの後に記述する場合

CASE
WHEN 判定したい条件式 THEN 該当した場合の値
WHEN 判定したい条件式 THEN 該当した場合の値
WHEN 判定したい条件式 THEN 該当した場合の値
ELSE いずれにも該当しない場合の値
END

①と②の使い分けは「どのような条件式にするか」によって決まる。
単純な「等しい」以外の条件では構文②が適切となる。

・特定の値と等しい
→構文①を利用する

・特定の値より大きい・小さい
・複数の特定の値に合致する（IN演算子で記述）
・特定の2つの値の間にある（BETWEEN演算子で記述）
→構文②を利用する

CASE文の条件式は順序も重要であるため、WHENに続く条件式は、古い順、または新しい順で記述するようにする。

---

グループ化を習得したことで、自分でデータを操っている感覚を感じ始めました。演習ドリルの難易度は前回に比べ上がっており、前回までの内容を基礎として、その上に築いていっているので、今回の内容もしっかり復習しようと思います。

ちょっとしたパズルのようで、楽しく勉強できています。

演習ドリルの実行内容