はじめに
今回、SQLの集計とグループ化について記事を書いていきたいと思います。
理由としては、SQLを学習し始めた際にいろんな知識を頭に詰め込んだせいで、どういう時にグループ化をしたほうがいいのか、また、ここで用いるのはWHERE句なのかHAVING句なのかなど結構迷うことがありました。そんな時に本記事に戻って復習できたらいいなと思い書き起こしました！
集計関数とは
まず、集計関数についてまとめていきたいと思います。
以下に、代表的な集計関数とその説明を表形式でまとめました。
| 集計関数 | 説明 |
|---|---|
| SUM | 指定された数値列の合計値を返します。 |
| AVG | 指定された数値列の平均値を返します。 |
| MIN | 指定された列の最小値を返します。 |
| MAX | 指定された列の最大値を返します。 |
| COUNT | 指定された列の非NULL値の数を返します。 |
ただし、特定のデータベース管理システムによっては、他のさまざまな集計関数も利用可能な場合があります。データベースの仕様やバージョンによって違いがあるため、具体的な使用方法やサポートされている関数を確認する際には、該当するデータベースのドキュメントやリファレンスを参照してください。
これらの集計関数は、データベースクエリの実行時に使用され、データの集約、グループ化、フィルタリング、結果の加工などを行うために役立ちます。
5つの関数のうち、COUNT関数だけは他の4つと少し特性が違います。
まずは使い方もほぼ同じである4つの集計関数について見ていきましょう。
集計関数の使い方
検索結果のある列に対して、合計、最大値、最小値、平均値を求めたい場合、 SUM、MAX、MIN、AVG関数を使います。
今回は例として以下の家計簿テーブルを用意しました。
| 日付 | カテゴリ | 内容 | 収入 | 支出 |
|---|---|---|---|---|
| 2018-02-03 | 食費 | コーヒーを購入 | 0 | 380 |
| 2018-02-10 | 給料 | 1月の給料 | 280000 | 0 |
| 2018-02-11 | 教養娯楽費 | 書籍を購入 | 0 | 2800 |
| 2018-02-14 | 交際費 | 同期会の会費 | 0 | 5000 |
| 2018-02-18 | 水道光熱費 | 1月の電気代 | 0 | 7560 |
このテーブルでは、各行が家計簿のエントリを表しており、日付、カテゴリ、内容、収入、支出の列があります。日付は支出や収入が発生した日付を示し、カテゴリはそのエントリの分類、内容は詳細な説明、収入は受け取った金額、支出は支払った金額を表します。
それでは集計関数を使って集計してみましょう。
SELECT
SUM(支出) AS 合計支出額,
AVG(支出) AS 平均支出額,
MAX(支出) AS 最も大きな散財,
MIN(支出) AS 最も少額の支払い
FROM 家計簿
<結果>
| 合計支出額 | 平均支出額 | 最も大きな散財 | 最も少額の支払い |
|---|---|---|---|
| 15740 | 3148 | 7560 | 0 |
このように、集計関数を使用することで、家計簿データの合計、平均、最大値、最小値などの統計情報を取得することができます。これにより、家計の収支状況や支出の傾向を把握することができます。
次にCOUNT関数についてみていきます。
COUNT関数は検索結果の行数を教えてくれる集計関数です。
それでは、先ほどの家計簿テーブルの食費の行数を取得してみましょう。
SELECT COUNT(*) AS 食費の行数
FROM 家計簿
WHERE 費目 = '食費'
<結果>
| 食費の行数 |
|---|
| 1 |
このように、特定の条件に合致する行の数や集計を行うことができます。
集計関数の注意点として、SELECT文でしか利用できないという点が挙げられます。集計関数は便利な機能ですが、いつでもどこでも自由に使えるわけではありません。
また、「検索結果」に対して集計を行う集計関数は、UPDATE文、DELETE文、INSERT文で利用することはできません。
グループ化とは
集計関数にも慣れてきたところで、続いてグループ化についてまとめていきます。
これまでの集計関数では、検索結果を全部ひとまとめして1つの結果を得ることができていました。
しかし、これでは家計簿テーブルのすべての行を合計しています。
どういうことかというと、支出の合計はまとめて出せていましたが、費目ごとの出費がいくらなのかはわかっていません。
表で見ると以下のような感じです。
| 費目 | 支出額 |
|---|---|
| 食費 | 380 |
| 給料 | 0 |
| 教養娯楽費 | 2800 |
| 交際費 | 5000 |
| 水道光熱費 | 7560 |
では上記のような表を作成するにはどうしたらいいのでしょうか？
SELECT '食費' AS 費目, SUM(支出額) AS 費目別の支出額の合計
FROM 家計簿
WHERE 費目 = '食費'
SELECT '給料' AS 費目, SUM(支出額) AS 費目別の支出額の合計
FROM 家計簿
WHERE 費目 = '給料'
・
・
・
こんな感じでSELECT文を何回も行使すれば正解には辿り着けますが、なんとなく力技（バスター）って感じがしますね。。。パワー、、、
こういう場面で使えるのがグループ化、つまりGROUP BY句です。
GROUP BY句を使うことで集計がグループごと(上記の場合は費目ごと)に行われ集計結果が表の形で得られます。
GUOUP BY句の使い方
ではGROUP BY句を使って費目ごとの合計を出していきましょう。
SELECT 費目, SUM(支出額) AS 費目ごとの支出合計
FROM 家計簿
GROUP BY 費目
<結果>
| 費目 | 費目ごとの支出合計 |
|---|---|
| 食費 | 380 |
| 給料 | 0 |
| 教養娯楽費 | 2800 |
| 交際費 | 5000 |
| 水道光熱費 | 7560 |
狙った通りの表が完成されました。
ここで一度、グループ化するにあたって何が起こっているのかをまとめます。
- 元の表に対してWHERE句による検索処理が行われ、行が絞り込まれます。
- GROUP BY句によって列に同じ値を持つ行ごとに分類されます。
- 最後に各グループに対して集計関数の処理が行われ、 SELECT句の選択列によって絞り込まれ表となります。
以上がグループ化の大まかな流れになります。
グループ集計後の絞り込み
上記で求められた「費目ごとの支出合計」では支出額に0円の行が紛れていますね。しかし、家計簿での支出で「給料」ってのもおかしな話なので消したいと思います。
(汗水垂らして稼いだお金なのに・・・)
SELECT 費目, SUM(支出額) AS 費目ごとの支出合計
FROM 家計簿
WHERE SUM(支出額) > 0
GROUP BY 費目
おそらくGROUP BY句を学習してすぐの頃はこういうふうなSQL文を作成して沼る型多いのではないでしょうか？
このSQL文ではWHERE SUM(支出額) > 0の部分でエラーになってしまいます。
先ほどのグループ化の流れをきちんと理解した方ならすぐにわかるはず！
・・・
そうです！
WHERE句が処理される1.検索の段階では、3.集計で初めて計算されるはずのSUM(支出額)は計算されていないので未確定だからです！！
つまり、集計関数では、WHERE句が利用できないということがわかります。
じゃあどうすればいいのか・・・？
こういう場面で必要とされるのがHAVING句になります。
HAVING句は集計処理を行った後に、結果の表に対して絞り込みを行いたい場合に用います。
まあ簡単にいうとHAVING句は集計関数版のWHERE句と思ってもらえれば大丈夫だと思います。
実際、WHERE句と同じようにORやANDなどの論理演算子で複数の条件式と組み合わせて使用することもできますし、、、
WHERE句と唯一違う点は絞り込みのタイミングで、HAVING句は集計結果がすべて出揃ったタイミングで実行されます。
HAVING句の使い方
では、実際に支出額0円の給料を除いた集計行を取り出すSELECT文を作っていきましょう。
SELECT 費目, SUM(支出額) AS 費目ごとの支出合計
FROM 家計簿
GROUP BY 費目
HAVING SUM(支出額) > 0
<結果>
| 費目 | 費目ごとの支出合計 |
|---|---|
| 食費 | 380 |
| 教養娯楽費 | 2800 |
| 交際費 | 5000 |
| 水道光熱費 | 7560 |
狙った通りの表が完成しました！
WHERE句とHAVING句の使い方を暗記するのではなく、仕組みから理解するとなぜ使えない場面が出てくるのか納得できたはずです。
ここで一度構文の記載順を整理しましょう。
SELECT 選択列1,選択列2・・・
FROM テーブル名
[WHERE 条件式]
[GROUP BY グループ化列名]
[HAVING 集計の結果に対する条件式]
[ORDER BY 並び替え列名]
[ ]に書いてあるものは必要に応じて記述してください。
また、それぞれの修飾語は記載できる場所が限られているため注意してください。
まとめ
では、まとめに入りましょう。
- 集計関数
- まとめたグループごとに1つの結果を算出することができる。
- SELECT文のみ使用することができる。
- グループ化
- グループ分けをする際に基準となる列を指定することで、グループごとに集計を行うことができる。
- 集計値を元日て特定のグループのみを算出する際はHAVING句を用いて算出する。
最後に
まずはここまで本記事を読んでいただきありがとうございました！
今回記載した内容のほとんどはスッキリわかるSQL入門とゼロからはじめるデータベース操作にて書かれていることを自分なりに解釈し書き起こしたものなので、どっかでみたことあるぞ？っていう方はいい復習になったのではないでしょうか？笑
自分はSQLを理解するためにこの本たちを一周しましたが、いざ人に伝えようとすると言葉が出てこないことに大変ショックを感じ再度学習し直しました。
やはり自分の理解度を試す場をこうして儲けるのは本当に大事ですね・・・
今後もアプリ制作に入る前段階で必要な知識について日々アウトプットして行けたらいいなと思います。
あと、話変わって自分はRubyエンジニアを目指していて、もし現場で働いている方目線でRubyのおすすめ学習法などがあればコメント欄で教えていただけると助かります🙇
長くなってしまってすみません。
ありがとうございました！！
参考文献