数える、合計する、束ねる

実物: 数を 1 つ返す

返ったのは表ではなく 1 つの数だ。正確には 1 行 1 列の表で、値が 50。COUNT(*) は「行を数える」で、多くの行から 1 つの値を作る関数を集計関数と呼ぶ。

AS 名前 で列に名前を付けられる。付けないと SUM(quantity) がそのまま列名になる。

WHERE は集計の前に効く。「配達済みの行だけ残してから数えた」。

COUNT(*) と COUNT(列)

括弧の中に列名を書くと意味が変わる。行数を予測してから実行する。

全行は 50、発送日ありは 47 のはずだ。COUNT(列) はその列が NULL でない行だけを数える。 SUM と AVG も NULL の行を無視する。AVG(shipped_at までの日数) のような計算をすると、未発送の注文は分母に入らない。 「未発送を 0 日として平均」と「未発送を除いて平均」は業務上まったく違う数字なので、どちらを出しているか意識する。

GROUP BY: 束にして、束ごとに数える

「状態ごとの注文数」を出す。まず結果を見てから、何が起きたかを説明する。

50 行の表が 4 行になった。GROUP BY status は、status が同じ行を 1 つの束にまとめる。束が 4 つでき、 束ごとに COUNT(*) を計算して 1 行ずつ出した。「〜ごとに」は GROUP BY だ。

列そのものでなく、列から計算した値で束ねることもできる。DATE_FORMAT は日時を文字列に整形する関数だ。

最初のラボ「状態ごと」で見た 50 行 → 4 行の間に、何があるのかを描く。

GROUP BY の約束

束にした後は、束の中の個々の行はもう見えない。図の delivered の束には customer_id が 7、1、1 … の行が入っている。だから SELECT に書けるのは「束ねた列」と「集計関数」だけだ。 束ねていない列を書くと、束の中に複数の値があってどれを出せばいいかわからない。エラーになることを確かめる。

HAVING: 束を絞る

WHERE は束にする前に行を絞る。束にした後の結果で絞りたいときは HAVING を使う。

動く順番はこうなる。

FROM      どこから
WHERE     行を絞る(束にする前)
GROUP BY  束にする
HAVING    束を絞る(集計した後)
SELECT    列と集計を計算
ORDER BY  並べる
LIMIT     切る

WHERE には元の列、HAVING には集計結果、と覚えると迷わない。ORDER BY では SELECT で付けた名前(件数)が使える。

驚き: 0 件の顧客が消える

チケットの最後、「顧客ごとの注文数、0 件の顧客も 0 と」を出す。顧客は 10 人いる。行数を予測してから実行する。

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

0 を出すには、顧客の一覧(customers)を土台にして、そこに注文をくっつける必要がある。それが次章の JOIN で、このチケットは次章で閉じる。

集計関数は多くの行から 1 つの値を作り、GROUP BY は同じ値の行を束にして束ごとに集計する。束は「あるもの」からしか作れない。

確認

次の章へ

顧客ごとの注文数で、注文のない顧客が消えた。注文の表には顧客の番号しかなく、名前もない。 次の章では、顧客の表と注文の表をつなぐ。つなぐと行が増えたり消えたりする感覚を身につけ、0 件の顧客を出す。