BigQueryのグループ集計時にfirst_valueを使いたい

概要

BigQueryのデータ加工時に、グループ集計時に特定の条件でソートした際の最初の行の値を取得したいといったことがある。
分析関数としてfirst_valueが用意されているが、処理の目的によってはグループ集計で実現できることで便利なこともあるため、その方法を備忘録としてまとめる。

やり方

方法は複数あるが、BigQueryの場合、array_aggを活用するのが一番スマート。

例えば、student_idに対して、試験日(exam_date)が最新のscoreを取得する場合は以下の通り。

サンプル

select
    student_id,
    array_agg(score order by exam_date desc limit 1)[offset(0)] as last_score
from
    exam_resuts
group by
    student_id

複数の値を取得したい場合は、structを活用すると良い

サンプル2

select
    student_id,
    array_agg(struct(exam_date as last_exam_date, score as last_score) order by exam_date desc limit 1)[offset(0)].*
from
    exam_resuts
group by
    student_id