select句の相関サブクエリ
※以後取得されるsqlはpostgresqlで実証
親テーブルのカラムが含まれるサブクエリ
例えばtable t1(f1,f2,f3),t2(f1,f2,f3)を考える。
テーブル名 :t1
| f1 | f2 | f3 |
|---|---|---|
| 1 | 20 | 30 |
| 1 | 200 | 300 |
| 1 | 2000 | 3000 |
テーブル名 :t2
| f1 | f2 | f3 |
|---|---|---|
| 1 | 20 | 30 |
| 1 | 222 | 333 |
| 1 | 2222 | 3333 |
select tt1.f1
, ( select tt2.f1
from t2 tt2
where tt2.f2 = tt1.f2)
from t1 tt1
これはごく一般的な相関サブクエリですが、このとき検索条件が同じでかつほかのカラムも返したいときにはどうするのか？
返したいカラムの分だけ相関サブクエリを記述しなければいけません。
select tt1.f1
, ( select tt2.f1
from t2 tt2
where tt2.f2 = tt1.f2 )
, ( select tt2.f3
from t2 tt2
where tt2.f2 = tt1.f2 )
from t1 tt1
これは、正直かなり冗長だと多くの人が感じてるところです。
10カラムを返したい場合は、10の相関サブクエリが必要になります。
関数化でまとめることもできますが、SQL外の処理になります。
また、返すカラム値を文字列結合してインラインビューにし、再度分割処理を入れることも可能です。また、postgresqlでは配列関数を利用することも可能です。
select
split_part( ttt1.tt2_f1f2, '#',1)
, split_part( ttt1.tt2_f1f2, '#',2)
from (
select tt1.f1
, ( select tt2.f1 || '#' || tt2.f2
from t2 tt2
where tt2.f2 = tt1.f2 ) tt2_f1f2
from t1 tt1
) ttt1
文字列結合の場合はサブクエリが一つになるのはとてもシンプルですが、すべて文字列結合⇒再度、カラム別に分割になるところがイマイチだと思います。
カラムの文字列結合なしに複数カラムを一括取得する方法とは。
SQLの再帰処理をうまく利用すればできます。
サンプルとして以下を考えます。
テーブル：cmain
| n | d | key1 | key2 |
|---|---|---|---|
| 1 | 11 | 111 | 1111 |
| 2 | 22 | 222 | 2222 |
| 3 | 33 | 333 | 3333 |
テーブル：csubt
| d | dd | key1 | key2 |
|---|---|---|---|
| 1100 | 101 | 111 | 1111 |
| 2200 | 202 | 222 | 2222 |
| 3300 | 303 | 333 | 3333 |
with RECURSIVE cmain AS
( SELECT 1 as n, 11 as d, 111 as key1 ,1111 as key2
UNION ALL
SELECT 2 as n, 22 as d ,222 as key1, 2222 as key2
UNION ALL
SELECT 3 as n, 33 as d,333 as key1,3333 as key2
)
, csubt AS
(
SELECT 1100 as d,101 as dd, 111 as key1 ,1111 as key2
UNION ALL
SELECT 2200 as d,202 as dd, 222 as key1 ,2222 as key2
UNION ALL
SELECT 3300 as d,303 as dd, 333 as key1 ,3333 as key2
)
, csub AS
(
SELECT 0 as cnt
, 0 as n
, 0 as d
, 0 as dd
, 0 as key1
, 0 as key2
UNION ALL
SELECT 1 as cnt
, cb.n + 1 as n
, ct.d as d
, ct.dd as dd
, ct.key1 as key1
, ct.key2 as key2
FROM csubt ct
LEFT JOIN cmain cm
ON cm.key1 = ct.key1
AND cm.key2 = ct.key2
right JOIN csub cb
ON cb.n + 1 = cm.n
where cb.n < ( SELECT COUNT(*) FROM cmain )
)
SELECT cm.n as cm_n
, cm.d as cm_d
, cm.key1 as cm_key1
, cm.key2 as cm_key2
, cb.d as cb_d
, cb.dd as cb_dd
FROM cmain cm
left join csub cb
on cm.key1 = cb.key1
and cm.key2 = cb.key2
where cb.cnt > 0
| cm_n | cm_d | cm_key1 | cm_key2 | cb_d | cb_dd |
|---|---|---|---|---|---|
| 1 | 11 | 111 | 1111 | 1100 | 101 |
| 2 | 22 | 222 | 2222 | 2200 | 202 |
| 3 | 33 | 333 | 3333 | 3300 | 303 |
解説
まず、with rcursiveで再帰処理を宣言します。再帰処理の細かい仕様については、同じqiitaにも説明がありますので割愛します。また時間があるときに、別途追記するかもしれません。
最初の２つcmain ,csubtは、以後使用するテーブルデータを作成しているだけです。
次にcsubで実際の再帰処理をしています。
union allより前は再帰処理前の処理になります。
-- union all 前の処理
select 0 as cnt
, 0 as n
, 0 as d
, 0 as dd
ここでやっていることは、なにもありません。最終的に cnt > 0で削除されます。
再帰処理の一回目のクエリになりますが、こちらにもunion all以降と同等の処理を書いて cnt >0を行わないようにすることも可能ですが、sqlが冗長になるためあえてこの記述にしました。
そして結論からいうと、select句のサブクエリを複数カラム取得する方法は、、、テーブル結合に置き換えることにほかなりません。union all以降の再帰処理で一行づつ複数カラムを取得しています。
最後に、ユニークな行番号でサブクエリの結果を結合します。
また、結合には、key1,key2でも問題ないはずです。
相関サブクエリにcount, max, limit, order by などが使用されている場合
union all の再帰項で同時に処理するのは文法違反です。
この場合、csubの結果を使用してwindow関数などで処理を置き換えれば可能です。
※また別途記述したいと思います。
まとめ
文字列結合で相関サブクエリを一括取得するのはシンプルです。
取得するカラムが2,3個の場合検索条件が相当複雑にならない限りは、それぞれ別々で取得でもいいのかなと思いますが、5，6個、、、と増えていくと一括取得も考えてみてはと思います。
postgresqlでは、配列関数で取得し、再度配列指定で取得できます。ただしこちらも、配列値の型は同一なもの一つだけになります。なので、数値、文字列混在するカラムの一括取得の場合はどれか一つの型に寄せる必要があります。
今回のように相関サブクエリ(correlated subqueries)で条件(where句)は同一なのにカラム名だけ違うsqlをべた書きするのは冗長すぎると思ってる人は多いと思われます。その解決方法として再帰SQLを使用した結合処理への置き換えによって複数カラムの一括取得は可能であることは示せました。
新発見
その後、まさに相関サブクエリのカラムを一括で取得するためにlateral(cross apply,outer apply)句というのがありました。(_)