SQLのGROUP BYとHAVINGの違いとは?基本情報の集計問題を解説
WHEREとHAVINGはどちらも条件を書く場所なのに、どう使い分ければいいのか分からない。
集計のSQLを見て、条件がどちらに書いてあるのかで手が止まったことはありませんか?
先に答えをお伝えします。GROUP BYは、行を同じ値ごとのグループに分ける句です。
HAVINGは、分けたあとのグループを条件で絞り込みます。WHEREは、グループに分ける前の1行ずつを絞り込みます。
つまり、WHEREとHAVINGの違いは、絞り込むタイミングが集計の前か後かにあります。この記事では、この記事のために作った小さな売上データを使い、集計関数の数え方からWHEREとHAVINGの使い分けまで、順番に確かめていきます。
基本情報技術者試験の全体像を先に知りたい人は、こちらの親記事が参考になります。
【関連記事】基本情報技術者試験とは?試験内容・難易度・勉強法を初心者向けに解説
集計関数とGROUP BYの基本¶
集計とは、いくつもの行をまとめて、件数や合計といった1つの値にすることです。SQLでは、そのための専用の関数が用意されています。
関数とは、値を渡すと計算した結果を返してくれる仕組みのことです。まずは、よく使う5つの集計関数を表で確かめておきましょう。
| 集計関数 | 求めるもの | 例 |
|---|---|---|
| COUNT | 行の数 | 売上の件数 |
| SUM | 合計 | 売上金額の合計 |
| AVG | 平均 | 1件あたりの平均金額 |
| MAX | 最大値 | いちばん高い売上 |
| MIN | 最小値 | いちばん安い売上 |
どの関数も、たくさんの行を受け取って、1つの値を返す点は同じです。この性質が、あとで出てくるHAVINGの理解につながります。
この記事で使う売上表¶
ここからは、次の売上表を使います。3つの店舗で、ノート・ペン・ファイルが売れた記録です。
| 売上番号 | 店舗 | 商品 | 金額 |
|---|---|---|---|
| 1 | 東店 | ノート | 300 |
| 2 | 西店 | ペン | 200 |
| 3 | 東店 | ペン | 200 |
| 4 | 南店 | ノート | 300 |
| 5 | 西店 | ノート | 600 |
| 6 | 東店 | ファイル | 400 |
| 7 | 南店 | ペン | 100 |
計算しやすいように、金額はすべて100円単位にしてあります。手元の紙にこの表を写しておくと、このあとの例を一緒に追いやすくなります。
GROUP BYなしで集計すると表全体が1つにまとまる¶
GROUP BYを使わずに集計関数を書くと、表全体が1つのグループとして扱われます。たとえば次のSQLです。
SELECT COUNT(*), SUM(金額) FROM 売上
COUNT(*)は行の数を数えるので7になります。SUM(金額)は7行の金額を足すので、300+200+200+300+600+400+100=2100です。
結果は1行だけになります。
GROUP BYで店舗ごとに分けて集計する¶
店舗ごとの売上を知りたいときは、GROUP BYで店舗を指定します。GROUP BYに書いた列の値が同じ行どうしが、1つのグループにまとめられます。
SELECT 店舗, COUNT(*), SUM(金額), AVG(金額)
FROM 売上
GROUP BY 店舗
このSQLを読むときは、先に表をグループに分けて書き出すのが近道です。東店は売上番号1・3・6、西店は2・5、南店は4・7の行が集まります。
グループに分けたら、グループごとに集計関数を計算します。結果は次のとおりです。
| 店舗 | COUNT(*) | SUM(金額) | AVG(金額) |
|---|---|---|---|
| 東店 | 3 | 900 | 300 |
| 西店 | 2 | 800 | 400 |
| 南店 | 2 | 400 | 200 |
東店の平均は、900を3件で割って300です。結果の行数は、もとの7行ではなく、グループの数と同じ3行になります。
SELECTに書ける列には決まりがある¶
GROUP BYを使ったSQLでは、SELECTに書ける列が限られます。書けるのは、GROUP BYに指定した列と、集計関数で包んだ列だけです。
たとえば、GROUP BY 店舗としたまま、SELECTに商品をそのまま書くとどうなるでしょうか。東店のグループにはノート・ペン・ファイルの3つがあり、どれを1行に出せばよいか決まりません。
そのため、標準のSQLではこの書き方は誤りになります。
この決まりは、選択肢の中から誤ったSQLを選ぶ問題でよく狙われます。SELECTの列を1つずつ見て、グループの列か集計関数のどちらかになっているかを確かめましょう。
WHEREとHAVINGの違いは絞り込むタイミング¶
ここからが本題です。WHEREとHAVINGは、どちらも条件を書く場所ですが、条件を当てる相手が違います。
WHEREは、もとの表の1行ずつに条件を当てます。HAVINGは、GROUP BYでまとめたグループに条件を当てます。
違いを表にまとめると次のようになります。
| 比べる点 | WHERE | HAVING |
|---|---|---|
| 絞り込む相手 | もとの表の1行ずつ | まとめたあとのグループ |
| 絞り込むタイミング | グループに分ける前 | 集計したあと |
| 集計関数を条件に使えるか | 使えない | 使える |
| 書く位置 | FROMの後、GROUP BYの前 | GROUP BYの後 |
WHEREの時点では、まだ集計をしていません。だから、WHEREにSUM(金額)のような集計関数を書くことはできないのです。
SQLが処理される順番¶
WHEREとHAVINGの違いは、SQLが処理される順番を知ると納得しやすくなります。SQLは、書いた順ではなく、次の順番で処理されると考えると読みやすくなります。
| 順番 | 句 | やること |
|---|---|---|
| 1 | FROM | 使う表を決める |
| 2 | WHERE | 1行ずつ条件で絞り込む |
| 3 | GROUP BY | 残った行をグループに分ける |
| 4 | HAVING | グループを条件で絞り込む |
| 5 | SELECT | 表示する列や集計結果を決める |
| 6 | ORDER BY | 並べ替える |
WHEREは、グループに分ける前の手順2にあります。HAVINGは、グループに分けた後の手順4にあります。
この順番を覚えておけば、どちらに書くべき条件かを自分で判断できます。
HAVINGでグループを絞り込む¶
まずはHAVINGだけを使う例です。売上の合計が800以上の店舗を求めます。
SELECT 店舗, SUM(金額)
FROM 売上
GROUP BY 店舗
HAVING SUM(金額) >= 800
先ほどの店舗ごとの集計結果を見ると、東店は900、西店は800、南店は400でした。条件に当てはまるのは東店と西店の2つで、南店のグループは結果から外れます。
このように、集計した値で絞り込みたいときはHAVINGを使います。ここをWHERE SUM(金額) >= 800と書くと、集計の前に集計関数を使うことになり、誤りになります。
WHEREとHAVINGを両方使う¶
次は、WHEREとHAVINGを両方使う例です。金額が300以上の売上だけを対象にして、その件数が2件以上の店舗を求めます。
SELECT 店舗, COUNT(*), SUM(金額)
FROM 売上
WHERE 金額 >= 300
GROUP BY 店舗
HAVING COUNT(*) >= 2
処理の順番どおりに追っていきます。手順2のWHEREで、金額が300以上の行だけが残ります。
残るのは売上番号1・4・5・6の4行です。
手順3で、残った4行を店舗ごとに分けます。東店は1と6の2件で合計700、西店は5の1件で600、南店は4の1件で300になります。
| 店舗 | COUNT(*) | SUM(金額) | HAVINGの判定 |
|---|---|---|---|
| 東店 | 2 | 700 | 残る |
| 西店 | 1 | 600 | 外れる |
| 南店 | 1 | 300 | 外れる |
手順4のHAVINGで、件数が2件以上のグループだけが残ります。結果は東店の1行だけです。
もしWHEREを書かなければ、東店は3件、西店と南店は2件なので、3つの店舗がすべて残ります。WHEREで先に行を減らしたかどうかで、HAVINGの結果が変わる点に注目してください。
集計関数とNULLの関係¶
集計問題で間違えやすいのが、NULLが混ざったときの数え方です。NULLとは、値が入っていない状態を表す特別な印のことです。
集計関数は、基本的にNULLを無視して計算します。ただしCOUNT(*)だけは、行そのものを数えるので、NULLの行も数に入ります。
次の3行の表で確かめてみましょう。
| 番号 | 点数 |
|---|---|
| 1 | 10 |
| 2 | NULL |
| 3 | 20 |
この表に集計関数を当てた結果を表にすると、次のようになります。
| 書き方 | 結果 | 理由 |
|---|---|---|
| COUNT(*) | 3 | 行の数を数える |
| COUNT(点数) | 2 | NULLの行は数えない |
| SUM(点数) | 30 | NULLを無視して10+20 |
| AVG(点数) | 15 | 30を、NULLを除いた2件で割る |
注意したいのはAVGです。NULLを0として扱うと30÷3=10になりますが、実際には30÷2=15になります。
NULLの扱いをもっと詳しく知りたい人は、こちらの記事で比較のしかたまで確かめられます。
【関連記事】SQLのNULLは0や空文字と何が違う?IS NULLと三値論理を例題で解説
例題で集計問題を解いてみよう¶
ここまでの内容を使って、自作の例題を1つ解いてみます。売上表は、この記事の最初に示したものを使います。
次のSQLを実行したとき、結果として正しいものはどれでしょうか。
SELECT 商品, SUM(金額) AS 合計
FROM 売上
WHERE 店舗 <> '南店'
GROUP BY 商品
HAVING COUNT(*) >= 2
ORDER BY 合計 DESC
AS 合計は、集計結果の列に合計という名前を付ける書き方です。<> は、等しくないことを表す記号です。
処理の順番どおりに追う¶
まず手順2のWHEREで、南店の行を取り除きます。売上番号4と7が外れ、1・2・3・5・6の5行が残ります。
次に手順3で、残った5行を商品ごとに分けます。ノートは1と5、ペンは2と3、ファイルは6だけです。
| 商品 | 含まれる売上番号 | COUNT(*) | SUM(金額) |
|---|---|---|---|
| ノート | 1、5 | 2 | 900 |
| ペン | 2、3 | 2 | 400 |
| ファイル | 6 | 1 | 400 |
手順4のHAVINGで、件数が2件以上のグループだけを残します。ファイルは1件なので外れます。
最後に、手順6のORDER BYで合計の大きい順に並べます。DESCは大きい順を表すので、結果は1行目がノートの900、2行目がペンの400になります。
よくある間違い¶
この例題でよくある間違いを、表にまとめておきます。どれも処理の順番を飛ばしたときに起きやすいものです。
| 間違え方 | 起きること | 防ぎ方 |
|---|---|---|
| WHEREを忘れて南店も集計する | ノートが3件1200、ペンが3件500になる | 最初にWHEREで行を減らす |
| HAVINGを最初に当てる | どの行を数えるのか分からなくなる | グループを書き出してから判定する |
| ファイルを残してしまう | 合計400の行が1つ多くなる | COUNT(*)を必ず書き出す |
| DESCを小さい順と読む | 並び順が逆になる | DESCは大きい順、ASCは小さい順と覚える |
集計の問題では、途中の表を紙に書き出すだけで間違いの多くを防げます。頭の中で一気に計算しようとせず、手順ごとに表を作るのがおすすめです。
SELECT・WHERE・ORDER BYといった基本の句から復習したい人は、こちらの記事も参考になります。
【関連記事】基本情報のSQL問題の解き方|SELECT・WHERE・ORDER BYを例題で解説
HAVINGの条件に、平均値など別のSELECTの結果を使う問題もあります。その場合は、副問い合わせの読み方を知っておくと落ち着いて解けます。
【関連記事】SQLの副問い合わせ(サブクエリ)の読み方|IN・EXISTSを例題で理解
集計の考え方は科目Bでも役に立つ¶
GROUP BYで店舗ごとに合計を出す処理は、プログラムで書くと、配列を先頭から順に見て、店舗ごとの合計に金額を足していく処理になります。科目Bで出題される擬似言語の問題でも、こうした集計の処理はよく登場します。
擬似言語とは、試験のために決められた書き方でプログラムを表したものです。SQLで「グループに分けて合計する」と考えた経験は、配列を使った集計のプログラムを読むときにもそのまま生きてきます。
科目Bの出題内容を知りたい人は、こちらの記事を読んでみてください。
【関連記事】基本情報技術者試験の科目Bとは?擬似言語・アルゴリズム対策を初心者向けに解説
私たちの Giji Academy では、擬似言語の講座を記法の基礎から条件分岐・繰返し・配列・アルゴリズム、科目B対策まで段階的に用意しています。擬似言語シミュレーターを使えば、プログラムをブラウザ上で1行ずつ実行し、合計を入れる変数の値が変わる様子を確かめられます。
一部の教材と入門レベルの練習問題は、無料会員のまま使えます。講座は擬似言語の講座一覧から始められます。
まとめ¶
GROUP BYは、指定した列の値が同じ行どうしを1つのグループにまとめる句です。COUNT・SUM・AVGなどの集計関数は、グループごとに1つの値を返します。
SELECTに書けるのは、グループの列と集計関数で包んだ列だけです。
WHEREとHAVINGの違いは、絞り込むタイミングにあります。WHEREはグループに分ける前の1行ずつを、HAVINGは集計したあとのグループを絞り込みます。
集計関数を使った条件は、HAVINGにしか書けません。
集計関数はNULLを無視しますが、COUNT(*)だけは行そのものを数えます。処理の順番どおりに途中の表を書き出せば、集計問題は確実に解けるようになります。
今日の例題を、自分の手でもう一度追ってみてください。