📘

【MySQL8.0】0件のCOUNT結果を取得するには？〜GROUP BYの挙動から考える〜

2023/06/25に公開

MySQL

SQL

初心者向け

tech

前置き

この記事は、MySql8.0でGROUP BYを使用した際に0件のCOUNT結果を取得する方法について解説しています。
通常、COUNTが0件となる結果は取得できません。GROUP BYの挙動としては当たり前になるんですが、

なぜそうなるのか
0件の結果も表示したいときにどうすればいいのか

ということについて調べてみたので紹介したいと思います。

`GROUP BY`の標準的な挙動

GROUP BY句は集計を行う際に使用します。COUNTと組み合わせることで、あるカテゴリ毎の件数を集計することができます。
しかし、カテゴリに該当するデータがない場合、そのカテゴリについてのCOUNT結果は通常は表示されません。

-- カテゴリ毎に商品数を集計する
SELECT category, COUNT(*) 
FROM Products 
GROUP BY category;

ここでは、Productsテーブルからカテゴリごとに商品数を数えています。しかし、あるカテゴリに一つも商品がない場合、そのカテゴリに関する行は結果に含まれません。

これはなぜでしょうか？

COUNTが0件になる結果を取得できないのはなぜ？〜存在しないものを数える〜

これは実は、数学の集合でいう"空集合"の問題に関連しています。

空集合とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

つまり、データが存在しないカテゴリをカウントするというのは、存在しないプロ野球チームに何人の選手がいるかを尋ねるのと似ています。

実際に想像してみてください。
知人から「東北楽天ゴールデンイーグルスに所属する野手の数は？」と聞かれたら回答できますよね（知っていれば）。
しかし「MySQLハイパーパフォーマンスに所属する野手の数は？」と聞かれたらどうでしょうか？
回答は「0人だよ」ではなく 「そんなチームねえよ！」 となります。

つまりチームそのものが存在しないので、選手数は0ではなくむしろ未定義となります。

SQLがデータを集約する挙動も同じで、何もないものを数えようとすると、SQLはそれを未定義と見なすため、そのカテゴリは集約結果から省略されてしまいます。

それでも実際にカテゴリが存在がする場合、0件で表示したいケースは往々にしてあります。
どうすればいいのでしょうか？

それでも0件の結果を表示したい！〜定義済みマスタと外部結合する〜

では、あえてそのカテゴリが存在しないことを示す行を結果に含めたいときはどうすればよいのでしょうか？

その答えは、全ての可能なカテゴリを事前に知っている"マスタテーブル"を用意し、そのテーブルと元のテーブルを外部結合することです。

-- マスタテーブルと左外部結合して0件の結果も表示する
SELECT m.category, COUNT(p.product_id) 
FROM Master_Categories m 
LEFT JOIN Products p ON m.category = p.category 
GROUP BY m.category;

このクエリでは、マスターテーブルMaster_CategoriesとProductsテーブルを左外部結合しています。
これにより、全てのマスターカテゴリに対して商品数がカウントされ、商品が存在しないカテゴリでもその行は結果セットに含まれ、商品数は0と表示されます。

マスタテーブルとかないんだけど...〜サブクエリを使う〜

もちろん、マスタテーブルが存在しない場合も多々あります（この記事を書いたきっかけはむしろこっち）
そんな時はサブクエリを使う方法もあります。

-- サブクエリを使って0件の結果も表示する
-- 4. IFNULLでNULLを0に変換
SELECT categories.category, IFNULL(p.count, 0) 
FROM 
    -- 1. ユニークなカテゴリを取得
    (SELECT DISTINCT category FROM Products) as categories 
LEFT JOIN 
    -- 2. カテゴリごとの商品数を取得
    (SELECT category, COUNT(*) as count FROM Products GROUP BY category) as p 
-- 3. 1と2を結合
ON categories.category = p.category;

この例を解説します。

Productsテーブルからすべてのユニークなカテゴリを取得し、それをcategoriesと名付けます。
ここでは、各カテゴリの商品数をカウントするテーブルpを作成します。
そして、これら2つのサブクエリを左外部結合します。
左外部結合を行うと、右表の結果が存在しない場合はNULLが入ります。ここでIFNULL()関数を使用することで、NULLを0に変換し、COUNTが0件の結果を取得することができます。

このようにすることで、カウント結果が0となるカテゴリも表示することができます。
ただし、この方法は上記の例で全てのカテゴリがProductsテーブルに少なくとも1つは存在する場合にのみ機能します。

そうではない場合、存在しないカテゴリはやはり結果に表示することができないため、サブクエリで自分でテーブルを定義するなどの対応が必要になります。

終わりに

GROUP BY句の挙動について調べてみると当たり前なんですが、今後も遭遇しそうな問題だったので記事として整理してみました。
今回紹介した原理と対策の例を知っておけば、WHERE句で対応したりもできます。参考になれば幸いです。
ここまで読んでいただきありがとうございました！

参考

MySQL公式（GROUP BYの挙動関係）

指定したカラム（GROUP BY <指定したカラム>）に基づいてデータをグループ化する
集計結果をグループ毎に単一の行で返す
結果セットの行は対象カラムの組み合わせによって一意に識別されるため組み合わせが存在しない場合、その行は出力されない。

参考にした記事/投稿