なにこれ
BigQueryで集計する際に、手元の事前データを条件等で使うために入れたくなる時があります。
わざわざテーブルを作るまでではないとBigquery(SQL)で祝日判定する - 夕蛙のなく頃にで扱ったようにarrayをunnestすることで実現させていました。
これが2列になったとき(顧客番号とそれに紐づく日付データなど)、少してこずったので残しておきます。
方向性
例えばuser_id, user_created_atというデータを考えます。
user_idを全て詰め込んだarrayとuser_created_atを全て詰め込んだarrayを2列用意し、row_numberでそれらにアクセスすることを考えました。
方法
WITH array_data AS ( # arrayでデータを準備, これは1行のデータ SELECT ['a', 'b', 'c', 'd', 'e'] AS user_id, ['2019-10-01', '2019-10-02', '2019-10-03', '2019-10-04', '2019-10-05'] AS user_created_at ), base_data AS ( SELECT # 行番号でarrayにアクセスする user_id[ORDINAL(ROW_NUMBER() OVER())] AS user_id, user_created_at[ORDINAL(ROW_NUMBER() OVER())] AS user_created_at # 1行のデータとarrayの長さ行のデータがクロスジョインされる FROM array_data, UNNEST(GENERATE_ARRAY(1, ARRAY_LENGTH(array_data.user_id))) ) SELECT * FROM base_data
2つもWITH句を介していて、ちょっと長いです。 実現方法はたくさんありそうで、もっとスマートな方法がある場合は教えていただきたいです。