prestoのmap_aggをbigqueryで表現する方法を教えてください。

Question

prestoで以下の表現をする方法をbigqueryで表現したいのですが、
bigqueryではmap_aggがないため、どのように表現できるか教えて欲しいです。
(足りない情報がありましたらご指摘頂けると幸いです。)

SELECT
dt,
map_agg(item_type, count) list
FROM summary_item
GROUP BY dt

結果
dt 　　　　　　　 list
2021-02-04　　　　　　　　{"0":19393730,"1":26024715}
2021-02-03　　　　　　　　{"0":2154327,"1":3320380}
2021-02-05　　　　　　　　{"0":95638059,"1":45944410}

Answer 1

2つ考えてみました。

文字列でこねくりまわす：

with summary_item as (
select '2021-02-03' as dt,"0" as item_type,"2154327" as count
union all select '2021-02-03' as dt,"1" as item_type,"3320380" as count
union all select '2021-02-04' as dt,"0" as item_type,"19393730" as count
union all select '2021-02-04' as dt,"1" as item_type,"26024715" as count
union all select '2021-02-05' as dt,"0" as item_type,"95638059" as count
union all select '2021-02-05' as dt,"1" as item_type,"45944410"  as count
),
tmp as (
    select dt, '"'||item_type||'":'||count as item_type_count from summary_item
)

select dt, '{'||STRING_AGG(item_type_count)||'}' from tmp
group by dt

結果

2021-02-03 {"0":2154327,"1":3320380}
2021-02-04 {"0":19393730,"1":26024715}
2021-02-05 {"0":95638059,"1":45944410}

struct を array にして json 化：

with summary_item as (
select '2021-02-03' as dt,"0" as item_type,"2154327" as count
union all select '2021-02-03' as dt,"1" as item_type,"3320380" as count
union all select '2021-02-04' as dt,"0" as item_type,"19393730" as count
union all select '2021-02-04' as dt,"1" as item_type,"26024715" as count
union all select '2021-02-05' as dt,"0" as item_type,"95638059" as count
union all select '2021-02-05' as dt,"1" as item_type,"45944410"  as count
)
select dt, TO_JSON_STRING(array_agg(struct(item_type, count))) from summary_item
group by dt

結果

2021-02-03 [{"item_type":"0","count":"2154327"},{"item_type":"1","count":"3320380"}]
2021-02-04 [{"item_type":"0","count":"19393730"},{"item_type":"1","count":"26024715"}]
2021-02-05 [{"item_type":"0","count":"95638059"},{"item_type":"1","count":"45944410"}]

Comments

[68user]

配列にしたくなくて 0・1 固定で、というならよくある CASE 文での縦横変換がよいでしょうね。

[owan0511]

ご回答、しかも２つもありがとうございます！
ベストアンサーを送らせて頂きます！