count(*) するときに気をつけたい NULL

create table contents (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY)
comment='コンテンツ';
insert into contents values (), (), (), (), (), ();
create table view_logs (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, content_id BIGINT NOT NULL)
comment='コンテンツに対する閲覧ログ';
insert into view_logs (content_id) values (1), (2), (3);
select * from contents;
select * from view_logs;

今回は簡便さを優先して、外部キー制約は付けていません。

コンテンツ	閲覧ログ
6レコード	3レコード。コンテンツIDが4,5,6は閲覧ログがありません。

各コンテンツの閲覧回数は何回でしょうか？

次のようになるはずです。

コンテンツID	閲覧回数
1	1
2	1
3	1
4	0
5	0
6	0

次にSQLを書いてみます。

失敗例

内部結合を使ってカウント

INNER JOINで閲覧ログとコンテンツを結合してしまってはいけません。閲覧ログの中にすべてのコンテンツIDが現れないためです。

閲覧ログにあるコンテンツIDしか閲覧数をカウントすることができません。

SELECT
	ct.id AS content_id,
	count(*) AS view_count
FROM
	contents AS ct
	JOIN view_logs AS vl ON ct.id = vl.content_id
GROUP BY
	content_id

外部結合を使ってカウント

閲覧ログとコンテンツIDを内部結合すると、閲覧ログに存在しないコンテンツIDがカウントできませんでした。

それなら、コンテンツIDを基準に外部結合をすれば、すべてのコンテンツIDが網羅できます。
しかし、安直に count(*) を使うと罠にハマります。

SELECT
	ct.id as content_id
	,count(*) as view_count
FROM
	contents AS ct
	LEFT OUTER JOIN view_logs AS vl ON ct.id = vl.content_id
group by
	ct.id

閲覧ログがないはずのコンテンツID(4,5,6)が閲覧回数が1になっています。

これは、 count(*) によって行単位でカウントされてしまっていることが原因です。

COUNT(*) は行数をカウントするため、ペットの数をカウントするクエリーは次のようになります。

出典：https://dev.mysql.com/doc/refman/5.6/ja/counting-rows.html

COUNT 関数には NULL を件数に含めるために特別な アスタリスク（*）を使用する構文が用意されており NULL を含めて行数を取得することができる。

出典：https://www.shift-the-oracle.com/sql/aggregate-functions/count.html

count する前のレコードを観察する

countする前のレコードを見ると、レコードの数は6で、content_idが4,5,6にはlog_idがNULLになっています。

SELECT
	ct.id AS content_id,
	vl.id AS log_id
FROM
	contents AS ct
	LEFT OUTER JOIN view_logs AS vl ON ct.id = vl.content_id

count(*) はレコードが存在すればカウントするため、すべてのコンテンツIDについて 1 とカウントします。

成功例

カウントしたいカラムを指定して外部結合を使う

view_logs の content_id カラムか id カラムを count() に与えれば、求めていた結果が得られます。

count() には式が入るようになっていて、 NULL を与えた場合 0 とカウント するため、 * ではなく NULL になりうるカラム名を与えれば、正しくカウントできます。

SELECT
	ct.id as content_id,
	count(vl.content_id) as view_count
FROM
	contents AS ct
	LEFT OUTER JOIN view_logs AS vl ON ct.id = vl.content_id
group by
	ct.id

実験後の後始末

不要になったテーブルはサクッと消して終了です。

drop table view_logs;
drop table contents;