同じテーブルを複数条件で集計する方法6つ
導入
同一テーブルを、複数の条件で集計したいケースは多々あるかと思います。
たとえば「アクセスログテーブルを、アクセス日毎、URL毎にPV数とユニークユーザー数のそれぞれを集計する」みたいなケースです。つまり以下です↓
- ディメンション：
- アクセス日
- URL
- メトリクス：
- PV数
- ユニークユーザー数
これは以下↓のように書けば算出できます。簡単ですね
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users
from
accesslogs
group by
1, 2
では、以下↓のケースはどうでしょうか？
- ディメンション：
- 日
- URL
- メトリクス：
- PV数
- ユニークユーザー数
- 20歳以上のユーザーのPV数 ←
New - 19歳以下のユーザーのPV数 ←
New
この集計方法をいくつか紹介します
方法1：カラム毎にgroup byしてjoin
「全ユーザー」「20歳以上のユーザー」「19歳以下のユーザー」のそれぞれで集計し、最後に1つにまとめる方法です。
それぞれで集計する際は、サブクエリやCTE（Common Table Expression）を使います。
「別テーブルを、同じディメンションで集計して1つにしたい」となった際はこの集計方法が有効です（なんならこの方法しかありません）。たとえば「日とURLをディメンションにして、別テーブルを集計して一つのテーブルに結合したい」というケースです。
そのため「同じ記法で揃えて書きっぷりを統一したい」という場合にはこちらの方法が良さそうです。
+with aaa as (
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users
from
accesslogs
group by
1, 2
+ ),
+bbb as (
+ -- 20歳以上のユーザーのアクセス数をカウントします
+ select
+ アクセス日,
+ URL,
+ count(*) as xxx
+ from
+ accesslogs
+ where
+ age >= 20
+ group by
+ 1, 2
+),
+ccc as (
+ -- 20歳未満のユーザーのアクセス数をカウントします
+ select
+ アクセス日,
+ URL,
+ count(*) as yyy
+ from
+ accesslogs
+ where
+ age < 20
+ group by
+ 1, 2
+)
+select
+ *
+from
+ aaa
+ inner join bbb using(アクセス日, URL)
+ inner join ccc using(アクセス日, URL)
しかしこちらの記法ですと、SQLエンジンによっては、複数回テーブルを読み込みます。たとえばPrestoでは、各CTEで同一テーブルをスキャンする場合、CTEの数だけスキャンします。キャッシュしたりはしません。そのため大きなテーブルではこちらは避けたほうが良さそうです。
方法2：count(case式)
count関数はnullをカウントしません。そのため、対象外のレコードはnullを、対象のレコードは非nullを引数に入れれば、意図通りの集計ができます。
null, 非nullを返す際は、case式やif関数を使えば良いです。
特にcase式を使う方法はSQL標準の書き方なので、汎用性があります。
またsum関数を使い、引数を1または0で集計させるテクニックもあります。
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users,
+ count(case when age >= 20 then 1 else null end) as xxx,
+ count(case when age < 20 then 1 else null end) as yyy,
+ -- count関数とif関数を使う方法↓
+ count(if(age >= 20, 1 null)) as xxx,
+ -- sum関数とcase式を使う方法↓
+ sum(case when age >= 20 then 1 else 0 end) as xxx
from
accesslogs
group by
1, 2
方法3：countif関数、count_if関数
BigQuery・Prestoには、countif関数またはcount_if関数があります。
これは「引数がtrueのものをカウントする」関数です。先程の、count(case式)のエイリアスみたいな関数です。
以下↓のように書けます。非常にシンプルです
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users,
+ count_if(age >= 20) as xxx,
+ count_if(age < 20) as yyy
from
accesslogs
group by
1, 2
方法4：filter句
PostgreSQL・Prestoには、filter句があります。
こちらも非常にシンプルに記述できます。この構文はPostgreSQLも対応してるので、「基盤DBの一部（もちろん全部でも）にPostgreSQLを使っていたり、PostgreSQLを採用しているアプリケーションエンジニアもSQLを読み書きする」といったチーム状況の際はこちらを採用するのが良さそうです。
countif関数やcount_if関数に無い利点として、こちらはdistinctも使用できます。つまり「アクセスした20歳以上のユーザーのユニークユーザー数」は、filter句を使うのが一番シンプルです。その理由だけでこちらに統一するのもアリかと思います。
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users,
+ count(*) filter(where age >= 20) as xxx,
+ count(*) filter(where age < 20) as yyy,
+ -- アクセスした20歳以上のユーザーのユニークユーザー数↓
+ count(distinct uid) filter(where age >= 20) as xxx_2
from
accesslogs
group by
1, 2
方法5：スカラーサブクエリ
単一の値（1行1列）のみを返却するサブクエリを「スカラーサブクエリ」といいます。
〈スカラーサブクエリ内のクエリのwhere条件〉と〈外側のクエリのディメンション〉を同じにすることで、今回の要件を満たせます。
これはSQL標準で書けますが、余計なスキャンも走るのであまりおすすめしないです。
select
アクセス日,
URL,
count(*) as number_of_page_views,
count(distinct uid) as number_of_unique_users,
+ (select count(*) from accesslogs as sub where main.URL = sub.URL and main.アクセス日 = sub.アクセス日 and age >= 20) as xxx,
+ (select count(*) from accesslogs as sub where main.URL = sub.URL and main.アクセス日 = sub.アクセス日 and age < 20) as yyy
from
- accesslogs
+ accesslogs as main
group by
1, 2
方法6：ディメンションとしてgroup by してピボット
ディメンションとして集計することで縦持ちテーブルを作り、それを横持ちテーブルにするテクニックです。
縦持ちのテーブルをBIツールに流し込んで横持ちに可視化する際、内部ではこれと同じ操作を行っているはずです（違ったらごめんなさい）。
また、非常に大きなテーブルを集計する際に、「一度中間テーブルとして縦持ちに集計・保存し、その後各ディメンションで再集計する」というテクニックはあります。
しかしSQL単体（なおかつ1つのSQL）でこんな事をする人はいません。
（余力があったらサンプルクエリ書きます）
結局どれを使う？
私見ですが、
- SQL標準に則りたいなら〈方法2：count(case式)〉
- 何でも良いなら〈方法4：filter句〉
- アドホックなクエリで使いやすいのは〈方法3：countif関数、count_if関数〉
が良いかと思います
以上です。良い分析クエリを！
Discussion