ARRAY_AGG は並び順を保証していない

配列の加工と並び順

どんな言語であれ、配列に対して何か加工をするタイミングは間違いなくあると思います。

たとえば Python で

nums = [1, 2, 3, 4]

のような配列があったとき、これらを二乗した値の配列をつくりたければ

squares = [x**2 for x in nums]

とか

squares = list(map(lambda x: x**2, nums))

とかで加工することになります。

この場合、いずれもアウトプットは

print(squares)
> [1, 4, 9, 16]

と、そのままもとの配列を二乗した値が綺麗に出力されるのですが、

今回は BigQuery を初めとする SQL で同様の作業をするときは注意が必要という話です。

先ほどのような二乗の値を算出したいとき、 SQL ならどのように書けるかというと…

配列をバラす UNNEST を使うことになります。

SELECT ARRAY_AGG(CAST(POW(x, 2) AS INT64)) AS squared_array
FROM UNNEST([1, 2, 3, 4]) AS x

BigQuery でこのクエリをそのまま叩くと、出力は

このように綺麗に先ほどと同じ形になります。

「なんや、特に問題ないやん」 となるところなのですが、ここが罠です。

こういったスモールなデータの場合は単一ノードで処理可能かつ簡単に処理できてしまうので、並び順が崩れることはないのですが、 ARRAY_AGG では並び順は保証されていません。

ARRAY_AGG() によって返される配列は、この関数では値が連結される順序が保証されていないため不定の順序になります。

慣れている人でなきゃ見落としちゃいますね…

というわけで、並び順を保証するためにはどうすればいいかというと、 ARRAY_AGG の中で ORDER BY を使います。

SELECT ARRAY_AGG(CAST(POW(x, 2) AS INT64) ORDER BY x ASC) AS squared_array
FROM UNNEST([1, 2, 3, 4]) AS x

とすればとりあえず目的のものに固定されます。

しかし、たとえば、元データが [2, 3, 4, 1] のようにランダムな順番になっていた場合はどうでしょう？

ほしいのは、 [4, 9, 16, 1] の順番のアウトプットですが、

先ほどのクエリでは x で並べ替えているので…

SELECT ARRAY_AGG(CAST(POW(x, 2) AS INT64) ORDER BY x ASC) AS squared_array
FROM UNNEST([2, 3, 4, 1]) AS x

とすると、

このように、強制的に [1, 4, 9, 16] の順でのアウトプットになります。

こういったケースに対応するには UNNEST したときに順番の値を出力するための WITH OFFSET を使用します。

SELECT ARRAY_AGG(CAST(POW(x, 2) AS INT64) ORDER BY idx ASC) AS squared_array
FROM UNNEST([2, 3, 4, 1]) AS x WITH OFFSET idx

idx には 0 から順に配列のインデックスが入りますので、その順に並べ替えると…

このように目的通りの並び順で配列を取得出来ます。

今回は ARRAY_AGG では ORDER BY を指定しないと並び順は保証されない、という話でした。

軽く挙動を確認して、大丈夫そうだな〜みたいな感じで本番のクエリに適用したりすると、意図せぬ挙動に繋がりますので、ぜひ知っておきたい話ですね。

かくいう私も何度か忘れた頃にこれで痛い目を見ていますので、忘れないようにしておきたいです…