複数カラムのOUTER JOINを楽に行う方法メモ。
ONの条件に使うカラムを文字列で連結してからOUTER JOINする
ONの条件に複数カラムを使うと挙動がややこしくなる。そういうときは、一旦文字列にして連結することで、ONの条件をシンプルにすれば色々と簡単になる。
ONの条件が複雑で分かりづらい例。
use abc_db;
SELECT
...
FROM
(
SELECT
a.device a_device,
b.device b_device,
c.device c_device,
...
FROM
(...) -- a, b, c テーブルをJOINするクエリ
WHERE
...
GROUP BY
...
) hey FULL OUTER JOIN (
SELECT
a.device a_device,
b.device b_device,
c.device c_device,
...
FROM
(...) -- a, b, c テーブルをJOINするクエリ
WHERE
...
GROUP BY
...
) heyhey ON (hey.a_device = heyhey.b_device
AND hey.b_device = heyhey.b_device
AND hey.c_device = heyhey.c_device)
上記のクエリでは、FULL OUTER JOINのONに複数条件が使われており、どういう挙動をするのかパッと分からない。
文字列連結を使うことで上記の分かりづらさを解消した例。
use abc_db;
SELECT
...
FROM
(
SELECT
a.device a_device,
b.device b_device,
c.device c_device,
concat(if(a.device IS NULL, 'NULL', a.device),
if(b.device IS NULL, 'NULL', b.device),
if(c.device IS NULL, 'NULL', c.device)) devices,
...
FROM
(...) -- a, b, c テーブルをJOINするクエリ
WHERE
...
GROUP BY
...
) hey FULL OUTER JOIN (
SELECT
a.device a_device,
b.device b_device,
c.device c_device,
concat(if(a.device IS NULL, 'NULL', a.device),
if(b.device IS NULL, 'NULL', b.device),
if(c.device IS NULL, 'NULL', c.device)) devices,
...
FROM
(...) -- a, b, c テーブルをJOINするクエリ
WHERE
...
GROUP BY
...
) heyhey ON (hey.devices = heyhey.devices)
上記のクエリのように文字列連結した結果をONに使うことで、ONの中身がすっきりするし、挙動も明確になる。たぶんバッドノウハウ的なものなんでしょうけども。
そもそもJOINし過ぎでは？みたいな指摘がMySQLの人からきそうですが、JOINが最も少なくなるようにしてもこんな感じです。
あと、このクエリはそもそも数十分かかることが全体のHiveクエリなので、これはこれでOKなんです。バッチみたいなものだと思ってください。
