【BigQuery】文字列を集計関数(array_agg)で扱い、特定の1つを抜き出す

背景

集計関数で文字列を扱えるととても便利だなあと思ったので、備忘録としてここに記しておきます。

今回使用するデータ

下記の 人の名前、教科、点数のテーブルを使用します。

name	subject	score
A	math	60
B	math	43
C	math	97
A	english	75
B	english	56
C	english	74
A	science	68
B	science	62
C	science	31

• データセット名は test
• テーブル名は sample_score_data

今回やりたいこと

これを人、その人の得意教科(一番得点の高い教科)、合計得点 という結果に集計するとします。

Answer

合計得点が sum(score) なのは誰でも容易に想像がつくと思いますが、subjectは文字列で集計できないためどう処理するかは好みの分かれるところです。

様々やり方はあるのですが、一発でスマートに出せるやり方があります。

それは、 『文字列を配列にする集計関数 array_agg で順序を定めて(order by)配列を作成した後、配列内の特定の要素を取り出す』 です。

クエリ

select
  name,
  array_agg(subject order by score desc limit 1)[offset(0)] as favorite_subject,
  sum(score) as total_acore

from test.sample_score_data
group by 1
order by 1

結果

name	favorite_subject	total_score
A	english	203
B	science	161
C	math	202

となります。

文字列columnを使って、「最も〜な○○」を抽出したい時におすすめです。