先日、teratailの質問に回答するために一生懸命考えたWITH RECURSIVEを使用したSQL。もったいないので、メモとして残しておきたいと思う。
仕様
- お客様TBLと変更情報TBLがある。
- 変更情報TBLには変更の都度データが作成される。このため、お客様TBLのIDを変更情報TBLの旧IDで検索、ヒットした新しいIDをさらに変更情報TBLの旧IDで検索、、、としていくことで最新の変更情報を辿ることができる。
- お客様TBLを変更情報TBLの最新の情報で更新したい。
- 変更情報TBLに存在しないIDは更新しない。
クエリ
WITH RECURSIVE OKYAKUSAMA AS ( --お客様TBL
SELECT '0001' AS ID, 1 AS RENNBANN, '0001001' AS CARDNO
UNION
SELECT '0001' AS ID, 3 AS RENNBANN, '0001001' AS CARDNO
UNION
SELECT '0005' AS ID, 4 AS RENNBANN, '0001010' AS CARDNO
)
, HENNKOU AS ( --変更情報TBL
SELECT '1001' AS ID, 1 AS RENNBANN, '1001003' AS CARDNO, '1001' AS KYUUID, 2 AS KYUURENNBANN, '1001003' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/20 12:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
UNION
SELECT '1001' AS ID, 2 AS RENNBANN, '1001003' AS CARDNO, '0001' AS KYUUID, 3 AS KYUURENNBANN, '0001001' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/01 11:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
UNION
SELECT '1005' AS ID, 5 AS RENNBANN, '1001005' AS CARDNO, '0005' AS KYUUID, 4 AS KYUURENNBANN, '0001010' AS KYUUCARDNO, TO_TIMESTAMP('2018/08/20 13:00:00', 'YYYY/MM/DD HH24:MI:SS') AS HENKOUBI
)
, TAISYO(ID, RENNBANN, CARDNO, MOTOID, MOTORENNBANN, MOTOCARDNO, DEPTH) AS (
SELECT CHANGES.ID, CHANGES.RENNBANN, CHANGES.CARDNO, CUSTOMER.ID AS MOTOID, CUSTOMER.RENNBANN AS MOTORENNBANN, CUSTOMER.CARDNO AS MOTOCARDNO, 1 AS DEPTH
FROM OKYAKUSAMA AS CUSTOMER
INNER JOIN HENNKOU AS CHANGES
ON CUSTOMER.ID = CHANGES.KYUUID
AND CUSTOMER.RENNBANN = CHANGES.KYUURENNBANN
AND CUSTOMER.CARDNO = CHANGES.KYUUCARDNO
UNION ALL
SELECT CHANGES.ID, CHANGES.RENNBANN, CHANGES.CARDNO, TAISYO.MOTOID, TAISYO.MOTORENNBANN, TAISYO.MOTOCARDNO, DEPTH + 1
FROM HENNKOU AS CHANGES, TAISYO
WHERE TAISYO.ID = CHANGES.KYUUID
AND TAISYO.RENNBANN = CHANGES.KYUURENNBANN
AND TAISYO.CARDNO = CHANGES.KYUUCARDNO
)
SELECT SUB_TAISYO.ID, SUB_TAISYO.RENNBANN, SUB_TAISYO.CARDNO
FROM OKYAKUSAMA, (
SELECT SUB.*
FROM (SELECT TAISYO.*, MAX(TAISYO.DEPTH) OVER(PARTITION BY TAISYO.MOTOID, TAISYO.MOTORENNBANN, TAISYO.MOTOCARDNO) MAX_DEPTH FROM TAISYO) SUB
WHERE SUB.DEPTH = SUB.MAX_DEPTH
) SUB_TAISYO
WHERE OKYAKUSAMA.ID = SUB_TAISYO.MOTOID
AND OKYAKUSAMA.RENNBANN = SUB_TAISYO.MOTORENNBANN
AND OKYAKUSAMA.CARDNO = SUB_TAISYO.MOTOCARDNO
説明
TAISYOの部分がWITH RECURSIVEの構文を使用している部分です。
まず、非再帰的表現部分（TAISYOのASの後の最初のSELECT句）でお客様TBLのキーと変更情報TBLの旧キーが同じデータを抽出しています。ここで、お客様情報のキーを元キー(MOTOID, MOTORENNBANN, MOTOCARDNO)として保持しています。さらに、深さを表すDEPTHを1として定義しています。
再帰的表現部分（UNION ALLの後の最初のSELECT句）では、非再帰的表現部分で取得できた結果の変更情報TBLのキーが変更情報TBLの旧キーと同じデータを抽出しており、これを繰り返します。繰り返すたびDEPTHは加算されます。
その結果は以下のようになります。
ID;RENNBANN;CARDNO;MOTOID;MOTORENNBANN;MOTOCARDNO;DEPTH
"1001";2;"1001003";"0001";3;"0001001";1
"1001";1;"1001003";"0001";3;"0001001";2
"1005";5;"1001005";"0005";4;"0001010";1
WITH以降のSELECT句でこの結果からお客様TBLのキーと元キーが同じ、かつ、深さが最大のデータを取得すると更新対象（MOTOIDが0001と0005）とその更新値（最新の変更情報）が取得できます。
最後に
WITH RECURSIVEを使用する場面は多くはなさそうですが、知っていると複雑なクエリを容易に組むことができるかもです。
以下、参考サイト
https://www.postgresql.jp/document/9.5/html/queries-with.html