[SQL]再帰クエリを使い、兄弟要素の並びが指定されているテーブルのデータを正しい並び順で取得する

先日、仕事で、親のIDという項目があるテーブルを使う要件があったので、その時の知見をメモ。

用は下記のような木構造データが、RDBのテーブル内に含まれていたわけです。

親１
∟子１
∟∟孫１
∟∟孫２
∟子２
∟∟孫３

この値を、上記の並び(親１,子１,孫１,孫２,子２,孫３)という並びで取得したかったのですが、テーブルに入っているのは兄弟要素ごとの並び順のみ。SQL一発で取得したいけど難しいかなと思って調べてみると、再帰クエリという方法があると知りました(SQLはそれなりに勉強してきたつもりですが、こんな技法があるなんて知らなかったです…。)。
再帰クエリ – Wikipedia

というわけで、以下、簡単なサンプル例。DBはPostgreSQLを利用しています。
用意したテーブルは、「ID」「タイトル」「親のID」「並び順」のカラム。データはWikipediaの野菜の一覧を元に作成しました。
以下が、用意したデータの画像です(分かる人には分かると思いますが、A5:SQL Mk-2のキャプチャです)。

再帰クエリの使用例が下記。

WITH RECURSIVE main AS ( 
    SELECT
        id
        , title
        , parent_id
        , lpad(cast(sort as character varying), 2, '0') AS sort 
    FROM
        tree_list
) 
, rec AS ( 
    SELECT
        0 AS depth
        , id
        , title
        , sort 
    FROM
        main 
    WHERE
        parent_id IS NULL 
    UNION ALL 
    SELECT
        rec.depth + 1 AS depth
        , main.id
        , main.title
        , (rec.sort || main.sort) AS sort 
    FROM
        main
        , rec 
    WHERE
        main.parent_id = rec.id
) 
SELECT
    id
    , REPEAT('∟', depth) || title AS title
    , sort 
FROM
    rec 
ORDER BY
    sort

今回の例でいえば、「rec AS」の後のカッコ内が再帰クエリです。
この程度だと最初のWITH句の「main」は必要ないですが、JOINだとかGROUP BYの集計とかしたあとで再帰クエリを利用することもあると思うので、再帰するデータはWITH句にまとめました。PostgreSQLで再帰クエリを使う場合は、「RECURSIVE」というキーワードが必要ですが、これは最初の「WITH」の後に書かないといけないようです(てっきり、再帰クエリのWITH句の前に記載するものだと思っていたので、最初「RECURSIVE rec AS」と書いてうまく動きませんでした)。

並び順の項目は0埋め2桁に変更したうえで、連結しています。このようにして並べ替えることで、親の並びをもとに子も並ぶようにできます。念のためお伝えすると、兄弟要素が100個以上(というよりも、sortの値が3桁)ある場合は、0埋め3桁以上にしたうえで連結する必要があります。

以下、結果のキャプチャ画像です。

うまく並んでいることが分かります。

ただ、再帰クエリは最近知ったばかりなのでよく分かってないところもあるのですが、コストはまあまあ大きいのではないのかなと思います。なので、データ量が多い場合は、再帰クエリを使わずに、プログラム側で並べ替えるようにしたほうがいいかもしれないです。