Prestoで2つの配列を同時に行に変換する

最近、Treasure Dataに触る機会が増えた。 その中で1行に2つのカラムにそれぞれコンマ区切りのデータがあり、それを行展開するのにハマったポイントがあったのでメモしておく。 そして長くなるので、最初に結論を載せておく。

どういうデータか

以下のようなデータ形式を想定している。

column1	column2
a,b,c	1,2,3

そして、このデータを以下のデータ形式に展開する。

column1	column2
a	1
b	2
c	3

前提としては、column1もcolumn2も同数の要素数が含まれていることとする。

どうするか

以下のように CROSS JOIN UNNEST の中に複数の split 関数を入れる。

SELECT 
  clmA,
  clmB
FROM
  tbl1
CROSS JOIN
  UNNEST(
    split(
      tbl1.column1,
      ','
    ),
    split(
      tbl1.column2,
      ','
    )
  ) AS t(clmA, clmB);

これにより上記のような展開ができます。

どういうことをしたか

ググってみると、Prestoでカンマ区切りのデータを展開した記事はよく出てくる。

qiita.com

これを見たときは、『そっか、じゃあ CROSS JOIN UNNEST をもう１段挟めばいいのか』
と安易に考えてしまいました。 ざっと以下のような感じ。（）

SELECT 
  t1.clmA,
  t2.clmB
FROM
  tbl1
CROSS JOIN
  UNNEST(
    split(
      tbl1.column1,
      ','
    ) AS t1(clmA)
CROSS JOIN
  UNNEST(
    split(
      tbl1.column2,
      ','
    )
  ) AS t2(clmB);

そうすると以下のような結果が得られた。

column1	column2
a	1
a	2
a	3
b	1
b	2
b	3
c	1
c	2
c	3

まさに CROSS JOIN。

なので、次は、『そっか、じゃあWITH句をつかって、一回テーブルとして展開してから、ID付きで展開すればうまくいくんじゃね？』 と思い、やりました。 が、やはり上記のようなクエリの結果になってしまいました。 なかなか奥深いですね…