はじめに

こんにちは、Treasure Data サポートの伊藤です。

今回は、SQLで特定の条件に合致するレコード数をカウントする方法と、その方法のロジックについて説明します。

はじめに
色々なケースでレコード数をカウントする
条件に合致するレコードのみカウントする方法
or null は必要？
- count関数の仕様
- 条件に合致する場合カウントするには
さいごに

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

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

use_id	sex	value
1	male	110
2	male	120
3	female	210
4	female	220
5	female	230
6	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(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
;