条件に合致するレコード数を求めるには

2021年6月8日

色々なケースでレコード数をカウントする

下記テーブルがあるとします。このテーブルのレコード数と性別ごとのレコード数を同時に抽出したい場合に皆さんならどうしますか？

use_id	sex	value
1	male	110
2	male	120
3	female	210
4	female	220
5	female	230
5	female	240

テーブル自体のレコード数は count(*) がすぐに思い浮かぶと思います。性別ごとに集計するには、例えば下記を挙げることはできるでしょう。

WITH dataset AS (
  SELECT *
    FROM (VALUES
      (1, 'male',   110),
      (2, 'male',   120),
      (3, 'female', 210),
      (4, 'female', 220),
      (5, 'female', 230),
      (6, 'female', 240)
         ) AS t(user_id, sex, value)
)
-- 上記はサンプルデータ定義部分なので無視
SELECT count(*) AS male_cnt
  FROM dataset
 WHERE sex='male'
;

上記は期待していた”2″という結果を返しますが、問題はテーブル全体のレコード数”6″や、他の条件に合致するレコード数をどうやって同時に算出するのか、という点です。悩んで出てくるようなものではないので、まずは方法について紹介します。

条件に合致するレコードのみカウントする方法

方法はいざ見てみると簡単で、count(<条件> or null) と記載することで条件に合致するレコード数を算出することができます。

WITH dataset AS (
  SELECT *
    FROM (VALUES
      (1, 'male',   110),
      (2, 'male',   120),
      (3, 'female', 210),
      (4, 'female', 220),
      (5, 'female', 230),
      (6, 'female', 240)
         ) AS t(user_id, sex, value)
)
-- 上記はサンプルデータ定義部分なので無視
SELECT count(*) AS all_cnt,                         -- テーブルのレコード数
       count(sex='male' or null) AS male_cnt,       -- maleのレコード数
       count(sex='female' or null) AS female_cnt    -- femaleのレコード数
  FROM dataset;

実際に Treasure Data CDP(Presto) で実行してみると、下記のようになります。期待した結果が抽出できていますね。

条件に合致するレコード数を求める

or null は必要？

count(<条件> or null) で条件に合致する件数を抽出できることは理解いただけたかと思います。ここで気になるのが、or nullという部分です。どうして必要なのか説明していきます。

count関数の仕様

まず最初に、count関数の仕様について理解する必要があります。下記Prestoのドキュメントからの引用です。

count(x) → bigint

Returns the number of non-null input values.

書いてあるとおりなのですが、引数が * ではない場合、nullではないレコードの数をカウントするという関数になっています。実際に動作を確認してみましょう。下記のように null が格納されているレコードが1行あるテーブルで確認します。

col1	col2
1	100
2	null
3	300

カウントするSQLを実行します。想定される結果は下記ですね。

count(*) –> 3行
count(col1) –> col1カラムにnullが含まれていないので3行
count(col2) –> col2カラムにnullが1行含まれているので2行

WITH dataset AS (
SELECT *
  FROM (VALUES
    (1, 100),
    (1, null),
    (3, 300)
       ) AS t(col1, col2)
)
-- 上記はサンプルデータ定義
SELECT count(*) as cnt_a,
       count(col1) as cnt_col1,
       count(col2) as cnt_col2
  FROM dataset
;

想定している結果が取得できました。

count関数の仕様を確認する

条件に合致する場合カウントするには

上記検証でわかったことは、count関数でカウントする際にnullの場合はカウントの対象外となる、ということです。そのため、ある条件に合致するレコードのみカウントしたい場合は下記のようにしてあげれば実現できると考えられますよね。

条件に合致する場合はnullではない値
条件に合致しない場合はnull

感覚的には、条件に合致するレコード数を抽出したい場合count(sex=’male’) だったらいいのではないかと思ってしまうでしょう。ですが、これではcount(*) と同じ結果になってしまいます。count関数の引数である sex=’male’ は、TRUEかFALSEを返します。条件に合致するかどうかの評価自体は行われますが、合致した場合もそうでない場合も結果はnullではないのでカウント対象となってしまうからです。そこでor nullを追加するという発想になります。追加することで、条件に合致しない場合はFALSEではなくてnullとなります。これで晴れて条件に合致するレコード数をカウントすることができるのです。