COUNTでの条件NULLの話 #SQL

システム改修の効果測定や新事業の展開拡大の効果予測など、KPI確認のために最近叩く回数も増えてきているので今更感を感じながらもメモがてら書いていきます。

一定条件のレコードのみをCOUNTしようとしてうまく行かなかった話

このようなsalesテーブルが存在すると仮定する。

dates	number	res	mvp
2017-01-01 00:00:00	2562	false	NULL
2017-02-01 00:00:00	2250	false	NULL
2017-03-01 00:00:00	2768	false	NULL
2017-04-01 00:00:00	2468	false	NULL
2017-05-01 00:00:00	1637	false	NULL
2017-06-01 00:00:00	1672	false	NULL
2017-07-01 00:00:00	2339	false	NULL
2017-08-01 00:00:00	2585	false	NULL
2017-09-01 00:00:00	2200	false	NULL
2017-10-01 00:00:00	2600	false	NULL
2017-11-01 00:00:00	3020	true	A
2017-12-01 00:00:00	2869	true	B

resカラムの値を利用し、目標を達成した数をCOUNTするため、
以下のようなSQLを叩く。

SELECT COUNT(res='true') AS "success",
       COUNT(res='false') AS "failed"
  FROM sales

結果

success	failed
12	12

条件問わず全部カウントされている...

公式ページ
https://www.postgresql.jp/document/7.4/html/functions-aggregate.html

expressionが非NULL値を持つ入力値の個数

null以外全てを数える模様。
ならば、条件を通す時に指定した値以外はNULLでCOUNTに渡すようにすれば良い。

SELECT COUNT(
         (CASE
            WHEN res='true' THEN res
            ELSE NULL
         END)
       ) AS "success",
       COUNT(
         (CASE
            WHEN res='false' THEN res
            ELSE NULL
         END)
       ) AS "failed"
  FROM sales

success	failed
2	10

では、今度は逆にNULLの数をカウントしてみる。
ということで、今度は以下SQLを実行

SELECT COUNT(
        (CASE
            WHEN mvp=NULL THEN mvp
            ELSE 'true'
        END)
      ) AS "NOT NULL",
      COUNT(
        (CASE
            WHEN mvp!=NULL THEN mvp
            ELSE 'true'
        END)
      ) AS "IS NULL"
  FROM sales;

結果

NOT NULL	IS NULL
12	12

ん？やはり何かおかしい...?

ググってみると、SQLでは3値論理を採用し、NULLを第3の真理値として扱っているらしい。
よって、条件などで利用する際にはIS NULLを利用する必要があるとのこと。

ということで条件式を一部変更

SELECT COUNT(
        (CASE
            WHEN mvp IS NULL THEN mvp
            ELSE 'true'
        END)
      ) AS "NOT NULL",
      COUNT(
        (CASE
            WHEN mvp IS NOT NULL THEN NULL
            ELSE 'true'
        END)
      ) AS "IS NULL"
  FROM sales;

結果

NOT NULL	IS NULL
2	10

まとめ

月並みですがNULLの扱いに気をつけましょう