【BigQuery】SQLで複雑な条件式が含まれたカウント文を書く、COUNT関数

6336 ワード
SQL データ分析 BigQuery SQL テキストリンク

概要

Queryで集計するときによく使うCOUNT関数の小技をご紹介します。（確認しているのはBigQuery）

テストデータ

event_id user_id prefecture date
1 9214 石川県 2002/4/27
2 949 島根県 1989/9/18
3 9477 青森県 1981/9/23
4 4732 佐賀県 1994/10/29
5 870 神奈川県 1988/10/7
6 5381 長野県 1988/12/17
7 1341 神奈川県 1997/12/18
8 1341 神奈川県 1997/12/18
9 1341 神奈川県 1997/12/18
10 2755 奈良県 2010/12/18

例1 レコードの数を数える

簡単ですね

select count(1) from test

例2 都道府県が何種類あるかを数える

重複なしで数えるパターン

select count(distinct prefecture) from test

例3 神奈川県の数を数える

条件でフィルタしつつ数えるパターン
多分これがよくあるパターン

select count(1) from test where prefecture = '神奈川県'

実はこうかけちゃいます

select count(if(prefecture = '神奈川県'),prefecture) from test

というかcountifがあります

select countif(prefecture = '神奈川県') from test

例4 神奈川県のユーザの数を重複なしで数える

神奈川県でユーザIDがいくつあるかを数えます。where句を使えば簡単なんですが、countとifだけでもかけちゃいます。

select count(distinct if(prefecture = '神奈川県', user_id, null)) from test

例5 神奈川県と青森県がそれぞれ全レコードの何％を占めているのかを数える

異なる条件でそれぞれの出現回数を数えるパターン

select
SAFE_DIVIDE(countif(prefecture = '神奈川県'), count(1)) * 100,
SAFE_DIVIDE(countif(prefecture = '青森県'), count(1)) * 100
from test

まとめ

where句では一行で出すことが難しいパターンを表現できました。
是非ご参考までに！

C言語のバブルソート、クイックソート法、ヒルソート法
Pythonのネットワークワーム