[SQL] 集計する(GROUP BY)

テーブルの値を集計する

SQLで集計が使えるようになるとできることの幅がかなり広がる。集計が正しく出来るかがSQL学ぶ上でまず最初の試練になる。

ExcelのピボットテーブルをイメージできればそれをSQLでどう表現するかというだけでやっていることは同じで、さらに集計のSQLが書ければBIツールもよりうまく使えるようになる(と思う)。

〇歳以上の人は何人いるかなど、ある条件をみたすようなレコードの数を調べたいということはよくある。また、売上や人数などを月や日、あるいは店舗など単位を変えて集計することも頻繁に行われる。

集計に必要なのは、集計関数とGROUP BYの2つだ。

SELECT 集計関数(集計対象のカラム) FROM テーブル GROUP BY まとめるカラム

集計関数は必ず必要でそうでないと集計にならない。GROUP BYはある単位でまとめたい場合には必要。なければテーブル全体を1つの単位として集計する。サンプルデータを使って試してみる。

サンプルデータ

次のファイルをDLしてコンソールにコピーする。

レコード数を数える(COUNT)

レコード数を数えるにはCOUNTを使う。なお集計関数の結果には自動で名前が付くが、f0_といった通し番号になりカラム数が多いとわけがわからなくなるのでasで名前を付けることを強くお勧めする。

SELECT count(カラム) as count from di_sql_sample01

この時カラムにどういった設定をするかによって結果が変わることがある。

関数 結果 説明
COUNT(*) 7 全ての行数
COUNT(1) 7 各レコードに値が1のカラムを足して行数えている= COUNT(*) と同じ
COUNT(id) 7 idにはNULLが無いのでCOUNT(*)と同じ
COUNT(installed_at) 6 NULLは数えないので違いが出る

重要なのはCOUNTはNULLがあると数えないということ。これを利用すると各カラムのCOUNTを取って全体と合わなければNULLが入っている、というチェックに使える。

またCOUNT(*)、COUNT(1)は両方ともテーブルの行数が返ってくるのだがどちらが良いのかはよくわからない。個人的にはCOUNT(1)を使っているがさして理由もないのでもし指定があるならそれを使えばよいだろう(言われたことは無い)。

カラムごとにグループ化してレコード数を数える(COUNT,GROUP BY)

FROM テーブルの後にGROUP BY カラムとすると、そのカラム単位での集計ができる。例えば性別の人数を数えるならば

SELECT sex,COUNT(1) as count from di_sql_sample01 GROUP BY sex

sex count
2 3
1 4

GROUP BYのカラム指定に番号を使う

GROUP BYではカラムを指定するが、SELECTに書いた順番に対応して番号で書くこともできる。

SELECT sex,COUNT(1) as count from di_sql_sample01 GROUP BY 1

これでsexを書いた場合と同じ結果が得られる。1カラムだけだと大したことは無いが、複数カラムでGROUP BYしたり、次に説明する結果を並び替える際に重宝する。

結果を並び替える(GROUP BY,ORDER BY)

GROUP BYで集計した結果は順番で並んでいないので、ORDER BYを使うことでどのカラムで並び替えるか指定する必要がある。使い方はGROUP BYの後にORDER BYが続く。

SELECT sex,COUNT(1) as count from di_sql_sample01 GROUP BY 1 ORDER BY カラム

カラムの指定方法は集計するカラム、集計結果のカラム、列番号がある。複数のカラムを指定することも可能。

ORDER BYの後 結果
sex sexで降順に並び替え
COUNT(1) COUNT(1)の結果を降順に並び替え
1 1列目で降順に並び替え
2 DESC 2列目で昇順に並び替えなのでCOUNT(1)の結果が大きい順

ユニークな値の集計

COUNTの中で「DISTINCT カラム名」を指定すると、ユニークな値の数が取れる。

SELECT COUNT(DISTINCT name) as count from di_sql_sample01

全部で7行に対して”佐藤”が2レコードあるのでnameのユニークな値は6になる。

SELECT COUNT(name) as count1,COUNT(DISTINCT name) as count2 from di_sql_sample01

1つのカラムに対してCOUNTとCOUNT DISTINCTの両方を集計することで、重複のあるなしを確認できる。もちろんGROUP BYやORDER BYと同時に使える。

集計した結果で絞り込む(HAVING)

集計した結果が〇〇より大きい、などが欲しい場合WHEREは使えない。これはWHEREで絞り込んだ結果のテーブルに対してGROUP BYが適用されるからで、その結果をさらに絞り込むにはHAVINGという別の方法が必要になる。

書く場所はGROUP BYとORDER BYの間で、HAVINGで絞り込んだ結果をさらに並び替えることもできる。「性別ごとのレコード数をカウントし、結果が3より大きいレコードをカウントの値が小さい順に並び替える」だと

SELECT sex,COUNT(1) as count from di_sql_sample01 GROUP BY 1 HAVING COUNT(1)>3 ORDER BY 2

合計する(SUM)

ここまではレコード数を数えるCOUNTのみを使っていたが、集計関数には他にもいろいろある。代表的なのが合計を取るSUMだ。

使い方は基本的にCOUNTと同じだが、合計を取るので数値型のカラムを指定しなければならない。例えば「性別の年齢の合計」を取るにはSUM(age)を使う。

SELECT sex,SUM(age) as sum from di_sql_sample01 GROUP BY 1

複数の集計が同時にできる

同じグループに対して複数の種類の集計も同時にできる。性別のレコード数と年齢の合計はそれぞれ計算したが、1つにまとめて書いてみると以下のようになる。

SELECT sex,COUNT(1) as count , SUM(age) as sum from di_sql_sample01 GROUP BY 1

集計同士の計算ができる

性別の年齢の合計だけではあまり意味が無い。平均年齢や1人当たりの売上などを考えるのが普通だ。複数の集計が同時にできることはすぐ前に書いたが、集計同士の計算も同時にできる。

性別のレコード数と年齢の合計に加えて、平均年齢(つまり年齢の合計/人数=レコード数)も一緒に計算してみよう。

SELECT sex,COUNT(1) as count , SUM(age) as sum , SUM(age)/COUNT(1) as avg from di_sql_sample01 GROUP BY 1

このように、同じグループに対する集計についてはいろいろ組み合わせた集計もできる。

その他の集計関数

COUNTとSUMが使えれば最初は十分。それ以外にもあるが

最大値を取る(MAX)

グループ内での最大値はMAX(カラム)。日付の場合は一番新しい日付になるので最終ログイン日を出す時などに使う。文字列でもエラーにはならないが何が返ってくるか正確に把握していないなら使わない方が良い(ので自分は使わない)。

最小値を取る(MIN)

グループ内での最小値はMIN(カラム)。日付の場合は一番古い日付になるので初回利用日を出す時などに使う。文字列でもエラーにはならないが何が返ってくるか正確に把握していないなら使わない方が良い(ので自分は使わない)。

平均値を計算する(AVG)

グループ内での平均値はAVG(数値型)。「集計同士の計算ができる」で見たSUM()/COUNT()と同じ結果になる。日付型や文字列型ではエラーになる。

SQL

Posted by 管理人