with recursive を使ってみた

with recursive って？

PostgreSQLにはwith句という便利なものがあります

mysqlにも8から実装されたって記事があったんで徐々に使って行けるようになってるんですかね？

んで、ちょうどやりたかったことがあったんで調べてみたら with句にはrecursiveってオプションがあって、再帰的に問い合わせができることがわかりました。

やりたかったこと

とあるプロジェクトがあってそのプロジェクトは、終了後次のプロジェクトを作成します。

知りたかった情報としては、そのプロジェクトは何代目のプロジェクトかということでした。 (実際はもう少し複雑)

データ構造例

フィールド名	型
id	integer
pre_project_id	integer
titile	text

データ例

こちらのデータだと例えば

project_1 はpre_project_idがないので1代目
project_2 はpre_project_idが1なので2代目
project_3 はpre_project_idが2なので3代目

といった感じです。

pre_projectはいくらでもさかのぼれる可能性があるため、普通にSQLを書くだけだとさかのぼってカウントをかけるのは難しそうです。

実際にやってみたこと

with recursiveを実際に書いてみます。

例

SQL

WITH RECURSIVE r AS (
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        projects.title as title,
        projects.id as base_project_id -- 集計元となるプロジェクトIDを取得する
    FROM
        projects
    WHERE
        projects.id <= 3 -- 結果が分かりやすいよう件数を制限
    UNION ALL
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        projects.title as title,
        r.base_project_id as base_project_id -- WITH RECURSIVEのデータからデータを引き継ぐ
    FROM
        projects,r
    WHERE
       projects.id = r.pre_project_id -- ここで再帰的にデータを紐づけ
)

SELECT
    *
FROM
    r
ORDER BY base_project_id ASC

結果

こんな形で、pre_project_idがnullになるまで再帰的にデータを取得できます。

今回やりたかったこと

今回やりたかったことはプロジェクトごとに何代目のプロジェクトかを取得したかったのでもう少しSQLを書き加えます

SQL

WITH RECURSIVE r AS (
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        projects.id as base_project_id -- 集計元となるプロジェクトIDを取得する
    FROM
        projects
    UNION ALL
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        r.base_project_id as base_project_id -- WITH RECURSIVEのデータからデータを引き継ぐ
    FROM
        projects,r
    WHERE
       projects.id = r.pre_project_id -- ここで再帰的にデータを紐づけ
)

SELECT
    r.base_project_id,
    count(r.project_id)
FROM
    r
GROUP BY r.base_project_id
ORDER BY base_project_id ASC

結果

オマケ

今回は、子から親を調べていきましたが、親から見てのリストを出すみたいなことも可能です。

SQL

WITH RECURSIVE r AS (
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        projects.title as title,
        projects.id as base_project_id -- 集計元となるプロジェクトIDを取得する
    FROM
        projects
    WHERE
       projects.id = 1
    UNION ALL
    SELECT
        projects.id as project_id,
        projects.pre_project_id as pre_project_id,
        projects.title as title,
        r.base_project_id as base_project_id -- WITH RECURSIVEのデータからデータを引き継ぐ
    FROM
        projects,r
    WHERE
       projects.pre_project_id  = r.project_id -- ここで再帰的にデータを紐づけ
)

SELECT
    *
FROM
    r
ORDER BY base_project_id ASC,project_id ASC

結果

Fusic Tech Blog

# sql

PostgreSQLでwith recursive を使ってみた