prestoで縦持ちのテーブルを横持ちのテーブルに変換する
prestoで縦のテーブルを横のテーブルに変換する方法です
with o as (
select
id, age_range, count(td_url) as count
from
m
group by
id, age_range
),
p as (
select
id,
MAP_AGG(age_range, count) as kv
from
o
group by
id
)
select
id,
IF(kv[20] is null, 0, kv[20]) as arv20,
IF(kv[25] is null, 0, kv[25]) as arv25,
IF(kv[30] is null, 0, kv[30]) as arv30,
IF(kv[35] is null, 0, kv[35]) as arv35,
IF(kv[40] is null, 0, kv[40]) as arv40,
IF(kv[45] is null, 0, kv[45]) as arv45,
IF(kv[50] is null, 0, kv[50]) as arv50,
IF(kv[55] is null, 0, kv[55]) as arv55,
IF(kv[60] is null, 0, kv[60]) as arv60
from
p
- map_agg関数でマップ型の構造をつくる
- 1で作ったマップを参照する