【トレジャーデータ：Presto】SQLでのクロス集計は？caseとmap_aggならどっち？表頭項目を指定しない方法は？

SQLでのクロス集計は？

SQL（Presto）では、CASE式やmap_agg関数を利用してクロス集計をする方法がある。

CASE式の例はこちらを参照（【トレジャーデータ：Presto】各ユーザの曜日別アクセス率を集計してライフスタイルに合ったアプローチをする）。

また、CASE式以外にもmap_agg関数を使う方法もある。
例えば、下記のようなテーブル（access_log）があるとする。timeはタイムスタンプ。

table:access_log

time	category	user_id
1488508906	経済記事	id2140001
1488508907	経済記事	id2140001
1488508908	物理記事	id2140002
1488508909	医学記事	id2140002
1488508910	医学記事	id2140001
1488508911	文学記事	id2140003
1488508912	物理記事	id2140002
.	.	.
.	.	.
.	.	.

そして、下記のようなコードを書く。

SELECT 

  days,

  --集計したいcategoryの項目を指定する

  COUNT(kv['経済記事']) AS "経済記事",

  COUNT(kv['医学記事']) AS "医学記事",

  COUNT(kv['物理記事']) AS "物理記事",

  COUNT(kv['文学記事']) AS "文学記事"

FROM (

    SELECT 

      TD_TIME_FORMAT(time,

        'yyyy-MM-dd','jst') AS days,

      --map集計：キーと値の重複なしペアをつくる

      map_agg(

        category,

        user_id

      ) kv

    FROM

      access_log

    GROUP BY

      TD_TIME_FORMAT(time,

        'yyyy-MM-dd','jst'),

      category,

      user_id

  )

GROUP BY

  days

このコードを実行すると、表側を日次、表頭を各記事カテゴリとするUU（ユニークユーザ数）を集計することができる。（ここで、ユニークユーザ数となる理由は、map_aggでキーと値で重複なしのペアをつくり、そのデータをもとに集計しているため）
 

time	経済記事	医学記事	物理記事	文学記事
2017-03-01	2	5	2	7
2017-03-02	67	5	36	3
2017-03-03	2	1	6	74
.	.	.	.	.
.	.	.	.	.
.	.	.	.	.

ただ、map_aggでの集計はメモリを多く使うためデータが大きい場合には適さない。実際、数千万レコード以上のデータを対象に集計をした際、メモリ不足となり集計できなかった（エラーをみると80GB以上のメモリを使っていた）。一方、CASE式で同様の集計したところ問題なくできた。

caseとmap_aggならどっち？

結論としては、CASE式。理由は、計算が速く、メモリ消費も抑えることができるため。一方、map_aggはあまり大規模データのクロス集計に適さないと言える（と、思いますがmap_aggを有効に使う方法があれば教えて頂きたいです）。

表頭項目を指定しない方法は？

case、map_aggも共に表頭項目の指定を一つ一つ記載する必要がある。そのため、項目が未知の場合や項目が膨大にある場合には対応が難しい。そこで、表頭項目を指定しなくてもクロス集計できる方法をいろいろ調べた（ネットで2日くらい探索したりTDの中の人に聞いてみた）が、残念ながら見つからなかった。どうやら、SQL以外の方法で集計するしかなさそう。。

Rでは表頭項目を指定しなくてもクロス集計できる

Rではacast（R クロス集計（acast））やspread（R　高速に大規模データのクロス集計をおこなう（tally, spread））を使えば、一つ一つ表頭項目を指定せずにクロス集計できる。特に、spreadは大規模データでも高速に処理できるため大変オススメ。


。。。SQL上で表頭項目の指定を必要としないクロス集計が簡単にできたら良いのに…
一応、TDの中の人にお願いしておきました。

map_aggの参考サイト

• 6.14. Aggregate Functions