BigQueryにて特定のカラムの値毎にデータを集める

背景

BigQueryにて特定のカラムの値毎にデータを集めたい時がある。普通のSQLなら、その特定のカラムでソートしてやれば良い。 ところが、BigQueryで扱うような大規模なデータだと、ソートしようとすると、"Resources Exceeded during query execution" というエラーが表示される。 その特定のカラムで GROUP BY の計算はできるので、集めるところまでは問題なく計算できるらしい。 なんとか集計する前に出力できないものだろうか…。MapReduceなら partitionで何とか出来そうなんだが…。

PARTITION BYを使う

PARTITION BYを使う。ただし、ORDER BYやGROUP BYの代わりにPARTITION BYを使うのではなく、window関数のダミー変数使って実現する。

例

SELECT
 aaa,
 bbb,
 ccc,
FROM
 [table1]

aaaのカラムの値でデータをまとめたい時は、

SELECT
 aaa,
 bbb,
 ccc,
 LAG(aaa) OVER (PARTITION BY aaa)
FROM
 [table1]

とする。LAG は一つ前の行の値を表示するwindow関数。基本なんでも良いが、数値を扱うwindow関数で、文字列だったりすると問題があるかもしれない。LAGはそのような問題がない。

さらに、aaaの値でまとめた内部にて、bbb の順にソートしたい場合には、

SELECT
 aaa,
 bbb,
 ccc,
 LAG(aaa) OVER (PARTITION BY aaa ORDER BY bbb)
FROM
 [table1]

とすればよい。

結論

window関数のPARTITION BYを使えば実現できる。

追記

2017/02/16: 先程確認したところこの技ができなくなっていた…。