BigQueryでstring_aggを利用するときは結果の再現性に注意しよう

概要

string_aggを利用した際に集計の再現性が取れずに詰まってしまったので、備忘のため掲載。

本題

string_aggは文字列値を結合して返す集計関数である。
グループ集計で利用する場合は以下のように扱う。

srting_aggの利用例

select
    corpus,
    word_count,
    string_agg(distinct word) as word_set
from `bigquery-public-data.samples.shakespeare`
group by 1, 2

ただし上記の場合、実行ごとに結合する値の順序が異なる。
それによって、結果が再現できず、後続の処理があると問題が生じる場合もある。

そのため、結果の再現性を持たせるため、order byで結合順を指定すると良い。

好ましい例

select
    corpus,
    word_count,
    string_agg(distinct word order by word) as word_set
from `bigquery-public-data.samples.shakespeare`
group by 1, 2

参考

• STRING_AGGの仕様