概要
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句では一行で出すことが難しいパターンを表現できました。
是非ご参考までに！