複数カラムの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なんです。バッチみたいなものだと思ってください。