HOW TO TD(User Engagement)Treasure Data User Engagement

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

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

テクニカルサポートエンジニアリングチームの伊藤 一樹です。
今回は、SQLで特定の条件に合致するレコード数をカウントする方法と、その方法のロジックについてご説明いたします。

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

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

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となります。これで晴れて条件に合致するレコード数をカウントすることができるのです。

評価式 sexカラムがmale sexカラムがmaleではない
sex=’male’ TRUE FALSE
sex=’male’ or null TRUE null

さいごに

今回はSQLでカウントする際のTipsとその背景について説明してみましたが、いかがでしたでしょうか? 後で見直したときに、なんでこんなクエリにしたんだっけ?と思わないように理由も合わせて理解できると良いですね!

伊藤 一樹

Technical Support Engineeringチーム

大学院卒業後2012年に日本オラクル株式会社に入社。ITコンサルタントとしてOracle Databaseの設計から運用、チューニング、性能アセスメントなどに従事。また、顧客のDBAチームの立ち上げやスキルトランスファー、短いダウンタイムでのデータベース移行、テストツール・オプションに注力し、コンサルタントのナレッジの訴求のため外部向けの講演なども経験。縁あって2019年2月にトレジャーデータに入社。多くの問い合わせを解決することを喫緊の至上命題とし、最も多くの問い合わせ対応を行っている。

得意領域 : データベース、SQL/付随するアーキテクチャ、トラブルシューティング

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

Back to top button