SQL上で1つのレコードを特定の値区切りに分割しレコードを複製したい

概要

タイトル通りなんだけど、語彙力が無さすぎて言葉で詳しく説明するのが難しかったので例で説明。

例

下記のようなデータがある

これを下のように100毎に区切って分解したい。
また、余りはそのまま余りを出力したい。

果たしてどうやるのがいいのか？

自分なりにたどり着いた答え

WITH data AS (
  SELECT
    *
  FROM 
    UNNEST([STRUCT(1 AS user_id, 232 AS amount), (2, 310), (3, 100), (4, 1)])
)

SELECT
  user_id,
  amount
FROM (
  SELECT
    user_id,
    ARRAY_CONCAT(ARRAY(SELECT 100 FROM UNNEST(GENERATE_ARRAY(1, CAST(TRUNC(amount / 100) AS INT64)))), [MOD(amount, 100)]) AS amount_array
  FROM
    data
),
UNNEST(amount_array) AS amount
WHERE
  amount != 0

解説

簡単に説明すると、元になる値を分割して分割数を求めてその分割数分の要素が100の配列を作成してそれに対して、元になる値を分割した際の余りを加えてUNNEST CROSS JOINで展開している。

と言ってもよくわからないので一つずつ分解

1

GENERATE_ARRAY(1, CAST(TRUNC(amount / 100) AS INT64))

元になる値(amount)を分割したい数字(100)で割って小数点を切り捨てる。
例えば378だったら3が返ってくるし、1だったら０が返ってくる。
GENERATE_ARRAY(1,n) は1からnまでの連番の配列を作ってくれるのでここで要素数分の配列を作成する。
378→[1,2,3]

2

ARRAY(SELECT 100 FROM UNNEST([1,2,3,4]))

その配列の各要素を全て分割したい数字(100)に置き換えてしまう。
[1,2,3] → [100,100,100]

3

ARRAY_CONCAT([100,100,100], [MOD(amount, 100)])

その配列に元になる値(amount)を分割したい数字(100)で割ったときの余りを追加する
[100,100,100]→[100,100,100,78]

4

後は配列をUNNESTしたやつと元のデータを CROSS JOIN してあげれば求めたかった物が出来上がる。

まとめ

もっとスマートなやり方はあるかもしれない、というかあるきがする。
ただ、UDFを使うやり方はパフォーマンス落ちるのであまりやりたくなかった。