created: 2019-06-26T08:40:01.000Z

BigQueryで UNNEST のやりかた

UNNEST は配列をフラットなレコードに直すための関数

こんなログデーブルがあったとして、post_arrayの配列をほぐしてフラットなレコードにしたいとする

+---------------------+--------------------------+---------------------------------------------------------+
|        time         |  login_id      |                           post_array                 |
+---------------------+--------------------------+---------------------------------------------------------+
| 2012-05-14 07:30:52 | NULL           |                                              ["122174"] |
| 2012-06-11 06:01:50 | NULL           |                            ["122225","122226","122227"] |
| 2012-05-02 07:25:12 | bar@test.com   |                                     ["126773","126203"] |
| 2012-05-13 06:23:36 | bar@test.com   |                   ["122320","122322","122323","122326"] |
| 2012-05-24 05:10:44 | dummy@test.jp  | ["122210","122272","122227","122200","122201","122222"] |
| 2012-05-22 07:22:52 | dummy@test.jp  |                                              ["123427"] |
| 2012-06-20 02:21:52 | foo@testap.com |                                              ["113110"] |
| 2012-05-02 07:10:02 | foo@test.com   |                                              ["126005"] |
+---------------------+--------------------------+---------------------------------------------------------+

こんなクエリでそれができる

SELECT
  time,
  login_id,
  post_id
FROM
  `proj.log.post`,
  UNNEST(post_array) AS post_id

結果はこんな感じ

+---------------------+------------------------------+
|        time         |      login_id  | creative_id |
+---------------------+------------------------------+
| 2012-06-20 02:21:52 | foo@testap.com |      113110 |
| 2012-05-14 07:30:52 | NULL           |      122174 |
| 2012-06-11 06:01:50 | NULL           |      122225 |
| 2012-06-11 06:01:50 | NULL           |      122226 |
| 2012-06-11 06:01:50 | NULL           |      122227 |
| 2012-05-02 07:25:12 | bar@test.com   |      126773 |
| 2012-05-02 07:25:12 | bar@test.com   |      126203 |
| 2012-05-13 06:23:36 | bar@test.com   |      122320 |
| 2012-05-13 06:23:36 | bar@test.com   |      122322 |
| 2012-05-13 06:23:36 | bar@test.com   |      122323 |
| 2012-05-13 06:23:36 | bar@test.com   |      122326 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122210 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122272 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122227 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122200 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122201 |
| 2012-05-24 05:10:44 | dummy@test.jp  |      122222 |
| 2012-05-22 07:22:52 | dummy@test.jp  |      123427 |
| 2012-05-02 07:10:02 | foo@test.com   |      126005 |
+---------------------+--------------------------+---+

FROM句の後ろに書くのがポイントなのだが、これってCROSS JOINになっちゃうんじゃないの、と思うけど結果は当該行とその行内の配列のJOINという風になる

これは相関交差結合というらしい

これは相関交差結合です。UNNEST 演算子は、以前に FROM 句に指定したソーステーブルの各行から ARRAY の列を参照します。ソーステーブルの N 行ごとに、UNNEST は、ARRAY の行 N を ARRAY 要素を含む一連の行にフラット化し、CROSS JOIN がこの新しい行のセットをソーステーブルの単一行 N と結合します。

参考

SQL標準とかはどうなのかな、と思ったがpostgres,prestoにもUNNEST関数があるようだ