ロックとチュウーハイとこりんがるな日々

日々のインプットした事をアウトプットする場所

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
  1. map_agg関数でマップ型の構造をつくる
  2. 1で作ったマップを参照する

参照: http://qiita.com/k24d/items/79bc4828c918dfeeac34