クロス集計時のGROUP_CONCATについて

Question

TABLE_X

id	name	tag
1	A	tag-hoge
2	A	tag-piyo
3	A	tag-piyo
4	B	tag-huga
5	C	tag-hoge
6	C	tag-piyo
7	C	tag-huga

上記のようなテーブルに対し、下記のようなSQL文を発行すると”結果”に記載している内容が得られると思いますが、
countでまとめられたNameに紐づくTagの情報を同時に取得したい場合、どのような手段が取れるでしょうか。
（GROUP_CONCAT ような関数で取得するイメージです）
やはり、SQLだけでは限界でしょうか。
SQLについて未熟であるためアドバイスいただけると幸いです。
なお、実行環境はBigQueryです。

select 
  count((name='A') or null) as A_COUNT,
  count((name='B') or null) as B_COUNT,
  count((name='C') or null) as C_COUNT
from TABLE_X

結果

A_COUNT	B_COUNT	C_COUNT
3	1	3

Answer 1

ARRAY_AGGやARRAY_TO_STRINGなどの配列関数を使用すると可能です。

with test as (
  select name, count(*) cnt
       , array_to_string(array_agg(distinct tag order by tag),',') tag_list
  from TABLE_X
  group by name
)
select 
    (select cnt from test where name='A') A_COUNT
   ,(select tag_list from test where name='A') A_tag_list
   ,(select cnt from test where name='B')　B_COUNT
   ,(select tag_list from test where name='B')　B_tag_list
   ,(select cnt from test where name='C')　C_COUNT
   ,(select tag_list from test where name='C')　C_tag_list

※パターンを集約せず列挙する場合は、distinctを外して下さい

Comments

[januarycom]

この方法でバッチリでした！ありがとうございました。