SQL Scale Lab｜3,200万行で学ぶMySQL・SQL実践教材

この章で答える問い

行を数える・合計する・平均するにはどう書くか
「〜ごとに」を SQL でどう言うか
なぜ「顧客ごとの注文数」で 0 件の顧客が消えるのか

表を集計する

注文は何件あるか

期待: 1 行 1 列、50

SELECT COUNT(*) FROM orders

50という数字が返ってきましたね。これは、現在の orders（オーダー）全てを集計した値です。式にある COUNT(*) は、対象の行数を数える関数です。このように多数の行から 1 つの集約された値を算出する関数を集計関数（集約関数）と呼びます。

続いて、過去の注文明細から、合計・平均・最小・最大を求めてみましょう。

合計・平均・最小・最大

期待: 1 行。数量の合計 381、平均 1.91

SELECT COUNT(*)        AS 明細数,
       SUM(quantity)   AS 数量合計,
       AVG(quantity)   AS 数量平均,
       MIN(unit_price) AS 最安,
       MAX(unit_price) AS 最高
FROM order_items

AS 別名 を使うと、計算結果の列にわかりやすい名前を付けられます。指定しない場合は SUM(quantity) などの式がそのまま列名になります。

絞ってから数える

期待: 40

SELECT COUNT(*) FROM orders WHERE status = 'delivered'

WHERE 句は集計関数が計算される前に適用されます。「まず配達済みの注文だけに絞り込み、残った行をカウントした」という順序で処理されています。

COUNT(*) と COUNT(列名) の違い

括弧の中にアスタリスクではなく列名を指定すると、集計の挙動が変化します。実行してどのような結果になるか確かめてみてください。

COUNT(shipped_at)

期待: 1 行

SELECT COUNT(*) AS 全行, COUNT(shipped_at) AS 発送日あり FROM orders

全行は 50 件、発送日ありは 47 件となったはずです。COUNT(列名) は、その列の値が NULL でない行だけ をカウントします。同様に、SUM や AVG も列名を指定した場合は NULL の値を計算対象から除外します。たとえば AVG(注文から発送までの日数) を計算した場合、未発送の注文は平均の分母にも含まれません。そのため、「未発送を 0 日として含めた平均」と「発送済みの注文だけを対象にした平均」では、結果が同じでもビジネス上の意味は大きく異なります。集計するときは、NULL がどのように扱われているかを意識することが重要です。

GROUP BY: グループに束ねて、グループごとに集計する

「注文ステータスごとの注文件数」を集計してみましょう。実行結果から何が起きているかを確認します。

状態ごと

期待: 4 行。paid 1、delivered 40、shipped 7、cancelled 2

SELECT status, COUNT(*) AS 件数
FROM orders
GROUP BY status

全 50 行の注文データが、ステータスごとの 4 行にまとまりました。GROUP BY status は、status 列の値が同じ行同士を 1 つのグループ（束）にまとめます。生成された 4 つのグループそれぞれに対して COUNT(*) が計算され、グループごとに 1 行ずつ結果が出力されます。 SQL で「〜ごとに集計したい」という要件が出てきたら、GROUP BY の出番です。

50 行のデータがどのように 4 行に集約されたかを図で見てみましょう。

GROUP BY status は status が同一の行を 1 つのグループにまとめ、各グループに対して集計関数を適用して 1 行ずつ結果を返します。

図の左側は注文表 orders の 50 行です。status が同じ行同士が矢印で中央の 4 つの束に集められます。各束に対して COUNT(*) が 1 回ずつ計算され、右側の結果表に束ごとの件数が 1 行ずつ出力されます。

続いて月ごとの集計も見てみましょう。

月ごと(式で束ねる)

期待: 3 行。6 月 19、7 月 15、8 月 16

SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS 月, COUNT(*) AS 件数
FROM orders
GROUP BY 月
ORDER BY 月

テーブルの列そのものだけでなく、関数で計算した値をもとにグループ化することも可能です。DATE_FORMAT は日時データを指定した書式の文字列に変換する関数です。書式の '%Y-%m' は年-月を表し、同じ年月になった行同士が 1 つの束にまとまります。

次に、月毎の状態別の注文件数をカウントしてみましょう。

2 つの軸で束ねる

期待: 月 × 状態の組み合わせごとに 1 行

SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS 月, status, COUNT(*) AS 件数
FROM orders
GROUP BY 月, status
ORDER BY 月, status

GROUP BY 月, status は、データをどの単位でまとめるかを指定しています。今回は「月」と「状態」の 2 つを指定しているため、「月 × 状態」の組み合わせごとに行がまとめられ、そのグループごとの件数が COUNT(*) で計算されます。

GROUP BY を使う際の必須ルール

GROUP BY は、グループごとに結果を 1 行作ります。その 1 行の各セルには、1 つの値しか入りません。たとえば下の図の「delivered」グループには、顧客 ID が 7、1、1 … と異なる複数の行が混ざっています。この 1 行の customer_id のセルに何を入れるべきか、1 つに定まりません。そのため SELECT 句に書けるのは、値が 1 つに定まるものだけです。「GROUP BY に指定した列」(束のキーなので全行同じ)か「集計関数」(束全体から 1 つの値を計算する)のどちらかです。定まらない列を書くとエラーになります。実際に試してみましょう。

GROUP BY はグループごとに結果を 1 行作ります。その 1 行の各セルには 1 つの値しか入りません。status と COUNT(*) は 1 つに決まるので入りますが、customer_id は 7、1、1 … と複数残るため何を入れるか決まらず、エラーになります。

束ねていない列を出そうとする

期待: エラー。not in GROUP BY clause

SELECT status, customer_id, COUNT(*)
FROM orders
GROUP BY status

HAVING: 集計後のグループを絞り込む

WHERE 句はグループ化する前の「元の行」を絞り込みますが、グループ化して集計した「集計結果」をもとに絞り込みたい場合は HAVING 句を使用します。

注文が 8 件以上ある顧客

期待: 3 行。顧客 1、3、7

SELECT customer_id, COUNT(*) AS 件数
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 8
ORDER BY 件数 DESC

2 つを組み合わせることもできます。次のクエリは「配達済みの注文が 6 件以上ある顧客」を探しています。WHERE で配達済みに絞ってから顧客ごとに束ね、HAVING で 6 件以上の束だけ残します。

WHERE と HAVING を両方

期待: 3 行。顧客 1、3、7(配達済み 11、7、7 件)

SELECT customer_id, COUNT(*) AS 配達済み
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 6

データベース内部の実行順序は以下のようになります。

FROM      対象テーブルを特定
WHERE     行を絞り込む（グループ化の前処理）
GROUP BY  グループにまとめる
HAVING    集計結果でグループを絞り込む
SELECT    出力する列と集計値を計算
ORDER BY  結果を並び替える
LIMIT     指定件数で切り出す

WHERE はグループ化の前に行を絞り込み(50行 → 40行)、HAVING は集計が完了した後にグループを絞り込みます(8件 → 3件)。

「元のテーブルにある列での絞り込みは WHERE、集計関数の結果による絞り込みは HAVING」と整理しておくと判断に迷いません。なお、ORDER BY では SELECT 句で定義した別名（件数 など）が利用できます。

注意すべき挙動: 0 件のデータが結果から消える

チケットの要件「顧客ごとの注文数を集計し、注文が 0 件の顧客も 0 件として表示する」を試してみましょう。顧客は全部で 10 人登録されています。何行返ってくるか予想してから実行してください。

顧客ごとの注文数

期待: 予測してから実行

実行する前に予測する返る行数

SELECT customer_id, COUNT(*) AS 件数
FROM orders
GROUP BY customer_id
ORDER BY customer_id

結果は 10 行ではなく、8 行になったはずです。顧客 ID が 8 と 10 の顧客が表示されていません。 orders（注文）テーブルには「これまでに注文したことがある人」のレコードしか存在しないため、注文実績が一度もない顧客はグループを作ることができず、結果行として現れないのです。GROUP BY は「テーブル内に実在するデータ」をグループ化する命令であり、「存在しないデータ」を自動的に 0 件として補完して出力することはできません。

注文が 0 件の顧客も含めて漏れなく出力するには、顧客マスタ（customers）を起点として、そこに注文テーブルを結合（JOIN）する必要があります。この手法は次章で詳しく解説します。

集計関数は多数の行を 1 つの値に集約し、GROUP BY は同じ値を持つ行をグループ化して集計します。実在しないデータはグループ化の対象になりません。

確認

WHERE と HAVING は、どちらも絞り込みなのに何が違うか。

WHERE は束にする前に行を絞り、HAVING は束にして集計した後に束を絞る。だから WHERE には元の列を、HAVING には集計結果を書く。

GROUP BY を書いたクエリの SELECT に、束ねていない列を書けないのはなぜか。

束の中には複数の行が入っていて、その列の値が 1 つに決まらないから。書けるのは束ねた列と集計関数だけである。

顧客ごとの注文数を GROUP BY で出すと、注文が 0 件の顧客が消えるのはなぜか。

注文のテーブルには注文した人の行しかなく、0 件の顧客は束を作れないから。GROUP BY は「あるもの」を束ねる道具で、「ないもの」を 0 として出すことはできない。

次の章へ

GROUP BY を使った集計の基本と、注文が 0 件の顧客が表示されない課題を確認しました。注文テーブルには顧客 ID の数値しかなく、顧客の氏名やメールアドレスは別のテーブルにあります。次の章では、複数のテーブルを結合して 1 つの結果にまとめる JOIN の仕組みを学び、0 件の顧客も含めて正しく集計する方法をマスターしましょう。