背景
前回の『SQLとは？』に続き、今回は集計方法についてまとめていこうと思います。
＊本記事は私が学習コミュニティで書籍勉強会を企画したときの発表資料です。
企画概要概要は以下の通り。
・スッキリわかるSQL入門を読み、自分なりの解釈でまとめてみる
・メンバー四人で各１章ずつを担当して読み、Qiita等でまとめて発表
・全12章あるので3日程で1周。（今回は第2回目！！）
・著作権/剽窃等は必ず配慮します。もし該当箇所あれば削除/修正しますのでお気軽にご指摘ください。
集計関数
SQLには集計関数として、以下が用意されている
今回も初学者として、Excelと比較してみましょう。
| SQL集計関数 | Excel関数 | |
|---|---|---|
| 合計 | SUM | SUM |
| 平均 | AVG | AVERAGE |
| MAX | MAX | MAX |
| MIN | MIN | MIN |
AVGが省略表記ということ以外は全部同じですね！
でも書き方は違います。
Excelでは =関数(対象範囲) という書き方が基本だと思います。
=SUM(A1:A5)みたいな感じですね。
それに対し、SQLはこんな感じ
SUM/AVG/MAX/MIN
SELECT
SUM (計算対象) AS 合計(カラム名)
AVG (計算対象) AS 平均(カラム名)
MAX (計算対象) AS 最大値(カラム名)
MIN (計算対象) AS 最小値(カラム名)
FROM テーブル名
`出力してね (SELECT)
何というカラム名として (AS)
計算対象をどのように (SUM / AVG / MAX / MIN)
どこのテーブルから (FROM)`
という感じで出力できる。
一つ一つの意味がわかれば簡単ですね！
実例)
count
COUNTは値の集計というよりも「データのレコード数の集計」という方が伝わりやすいかも。
こんな感じで使います。
SELECT
COUNT( 列 or * ) AS レコード数（カラム名)
FROM テーブル名
WHERE カラム名 = '対象項目'
出力してね(SELECT)
何というカラム名として(AS)
データの数を(COUNT)
( * の場合は ) ただし、NULLも含めてね
どこのテーブルから (FROM)
カラム名の中の対象項目だけに絞り込んでね (WHERE)
実例) *レイアウトわかりづらいけど、右下に小さく２と出ています！
NULLについて
これはExcelにおける 空白の扱い方と似ている。
基本的には計算結果に影響を与えない、つまり集計対象から除外される。
データのグループ化
今度はデータのグループ化を解説。
今回もExcelと比較してみましょう。
わかりづらいものは身近なわかりやすいものに変換。（もちろんその後に正しく理解）
| SQL | Excel | |
|---|---|---|
| グループ集計 | GROUP BY | PIVOTテーブル |
賛否あるかもしれないけど、個人的にはこれが一番近いと思います。
PIVOTテーブルは範囲を指定して、列ごとに集計する使い方が一般的と思います。
（もちろん他にも色々できるが、、）
ではSQLのGROUP BYは？？
SELECT 基準カラム名,
集計関数 (カラム名)
FROM テーブル名
(WHERE カラム名 = '対象項目')
GROUP BY 基準カラム名
基準カラム名を出力するよ
集計関数で集計してね
何というテーブルから
(絞り込みもしたり)
費目ごとにね
という感じ？
実例) 費目ごとに集計
複数のカラムを基準に集計するときはこう。
少し例のデータが少なすぎてわからなくなってしまっているけど、
基準列をカンマで区切ることで、日付をグループ化かつ費目のグループ化された表が表示される。
もっとデータがたくさんあったら以下のような感じで出せるはず。
| 日付 | 費目 | 出金額 |
|---|---|---|
| 2018-01-13 | 食費 | 350 |
| 2018-01-13 | 水道光熱費 | 400 |
| 2018-01-13 | 居住費 | 600 |
ポイント
SELECTとGROUP BYで同じことを書かなきゃいけないのは、
・ SELECTではカラムとして表示するものを指定
・ GROUP BYではグループ化するものを指定
ということだと思われます。
（本当のこと知ってる方、コメントでこっそり教えて下さい。)
集計後の絞り込み
WHERE句の中で集計関数を用いて絞り込むことはできません。
WHERE SUM(カラム名) > 0
という感じはできない。
これは、WHERE句の処理段階ではまだ集計関数が処理されていないからです。
なので、集計後に絞り込みをかけたい場合は HAVING句を使います。
SELECT 基準カラム名,
集計関数 (カラム名)
FROM テーブル名
(WHERE カラム名 = '対象項目')
GROUP BY 基準カラム名
HAVING 集計関数 比較演算子等
例えば以下を
| 日付 | 費目 | 出金額 |
|---|---|---|
| 2018-01-13 | 食費 | 350 |
| 2018-01-13 | 水道光熱費 | 400 |
| 2018-01-13 | 居住費 | 600 |
HAVING SUM (出金額) > 380 とすることで、、、
| 日付 | 費目 | 出金額 |
|---|---|---|
| 2018-01-13 | 水道光熱費 | 400 |
| 2018-01-13 | 居住費 | 600 |
というように絞り込めます。
まとめ
初見できついなと思ったらExcelと比較して考えてみましょう。
集計関数は四則演算をしてくれたり、最大/最小/平均…などいろんなことを計算してくれる。
機械学習/統計など興味がある方は、分散とか標準偏差なども出せるのでぜひ調べてみてください。GROUP BYは集計したものをグループ毎にまとめてくれる。
HAVINGとWHEREは両方絞り込みの機能。よく似ているけど、使う場面が集計前後で異なる。
参考
・スッキリわかるSQL入門
・逆引きSQL構文集
・ITSakura Blog for business and development