SQL　再帰クエリ

┃使い方

7.8. WITH問い合わせ（共通テーブル式）

うまいことデータを用意できれば、loop処理の代用になる。

┃Ⅰ. 統合元の抽出

１）統合データを用意する

DROP TABLE IF EXISTS TEMP_DATA;
CREATE TEMP TABLE TEMP_DATA AS (

   /*
    ・C000、C002、C005は統合されてないID
    ・C001はC002に統合されたID
    ・C003はC004に、さらにC004はC005に統合されたID
   */
   SELECT 'C000' AS ID, NULL   AS NEXT_ID UNION
   SELECT 'C001' AS ID, 'C002' AS NEXT_ID UNION
   SELECT 'C002' AS ID, NULL   AS NEXT_ID UNION
   SELECT 'C003' AS ID, 'C004' AS NEXT_ID UNION
   SELECT 'C004' AS ID, 'C005' AS NEXT_ID UNION
   SELECT 'C005' AS ID, NULL   AS NEXT_ID

);

２）再帰クエリで統合元のIDを探索する

WITH RECURSIVE TEMP_CTE (ORG_ID, ID, NEXT_ID, SRCH) AS (

   SELECT
       T1.ID AS ORG_ID
       , T1.ID
       , T1.NEXT_ID
       , 1 AS SRCH
   FROM
       TEMP_DATA T1
   WHERE
       T1.NEXT_ID IS NULL
   
   UNION
   
   SELECT
       T1.ORG_ID
       , T2.ID
       , T2.NEXT_ID
       , T1.SRCH + 1 AS SRCH
   FROM
       TEMP_CTE T1
       , TEMP_DATA T2
   WHERE
       T1.ID = T2.NEXT_ID

)
, TEMP_SRCH_RSLT AS (

   SELECT DISTINCT
       T1.ORG_ID
       , T1.ID
       , T1.NEXT_ID
       , T1.SRCH
       , MAX(T1.SRCH) OVER (PARTITION BY T1.ORG_ID) AS MAX_SRCH
   FROM
       TEMP_CTE T1

)
SELECT
   T1.ORG_ID
   , T1.ID
FROM
   TEMP_SRCH_RSLT T1
WHERE
   T1.SRCH = T1.MAX_SRCH
ORDER BY
   T1.ORG_ID
;

３）結果

org_id	id
-----------------
C000	C000
C002	C001
C005	C003

┃Ⅱ. ｎ日間隔の抽出

１）時系列データを用意する

DROP TABLE IF EXISTS TEMP_YMD;
CREATE TEMP TABLE TEMP_YMD AS (

    SELECT  20210901 AS YMD UNION
    SELECT  20210907 AS YMD UNION
    SELECT  20210908 AS YMD UNION
    SELECT  20210913 AS YMD UNION
    SELECT  20210914 AS YMD UNION
    SELECT  20210918 AS YMD UNION
    SELECT  20210925 AS YMD UNION
    SELECT  20210926 AS YMD UNION
    SELECT  20210928 AS YMD UNION
    SELECT  20210930 AS YMD

);

２）各データの日付からｎ日間隔の最小を導出する

DROP TABLE IF EXISTS TEMP_YMD_WITH_NEXT;
CREATE TEMP TABLE TEMP_YMD_WITH_NEXT AS (

   SELECT
       T1.YMD
       , MIN(T2.YMD) AS NEXT_YMD
   FROM
       TEMP_YMD T1
       
       LEFT OUTER JOIN
           TEMP_YMD T2
           ON
               TO_DATE(CAST(T1.YMD AS VARCHAR), 'YYYYMMDD') + 4
                    < TO_DATE(CAST(T2.YMD AS VARCHAR), 'YYYYMMDD')
   GROUP BY
       T1.YMD
);

３）再帰クエリで初回と初回以降のデータを抽出する

WITH RECURSIVE TEMP_CTE (YMD, NEXT_YMD) AS (

   -- 初回
   SELECT
       T1.YMD
       , T1.NEXT_YMD
   FROM
       TEMP_YMD_WITH_NEXT T1
   WHERE
       T1.YMD = 20210901
   
   UNION 
   
   -- 初回以降
   SELECT
       T2.YMD
       , T2.NEXT_YMD
   FROM
       TEMP_CTE T1
       , TEMP_YMD_WITH_NEXT T2
   WHERE
       T1.NEXT_YMD = T2.YMD

)
SELECT DISTINCT
   T1.YMD
FROM
   TEMP_CTE T1
ORDER BY
   T1.YMD
;

４）結果

ymd
---------
20210901
20210907
20210913
20210918
20210925
20210930