
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;

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