【SQL】階層構造を持つデータから枝番付きの連番を取得

前提

Webアプリケーションの開発中に、親子関係（階層構造）を持つデータに対して、枝番付きの連番を表示したいという要件があったので、実現方法の整理を兼ねたアウトプットです。

DBはMySQL8.0を想定。

階層構造を持つデータ

アプリ開発をしていると、親のIDを保持することで親子関係や階層構造を表現するテーブルに遭遇することが多々あります。よくある例だと、カテゴリとか、企業の組織構造とか。

今回は、階層構造を持つカテゴリのデータを例に考えます。ソフトウェア開発に関する技術を適当にカテゴリ分けして、適当に階層構造を持たせます。

データの作成

実際に試したい人向け。テーブルとデータを作成するSQLです。

create table categories as 
(
select 1 id, 'Web' name, null parent_id
union all
select 2 id, 'HTML' name, 1 parent_id
union all
select 3 id, 'CSS' name, 1 parent_id
union all
select 4 id, 'JavaScript' name, 1 parent_id
union all
select 5 id, 'DB' name, null parent_id
union all
select 6 id, 'SQL' name, 5 parent_id
union all
select 7 id, 'DB設計' name, 5 parent_id
union all
select 8 id, 'インフラ' name, null parent_id
union all
select 9 id, 'Linux' name, 8 parent_id
union all
select 10 id, 'AWS' name, 8 parent_id
);

全件取得結果は以下。

select * from categories;

id	name	parent_id
1	Web	null
2	HTML	1
3	CSS	1
4	JavaScript	1
5	DB	null
6	SQL	5
7	DB設計	5
8	インフラ	null
9	Linux	8
10	AWS	8

階層で表現すると以下ようになります。

• Web
  • HTML
  • CSS
  • JavaScript
• DB
  • SQL
  • DB設計
• インフラ
  • Linux
  • AWS

完成イメージ

Web画面上に以下のように表示されることを想定。親のカテゴリは枝番なしで、子のカテゴリは親のカテゴリにハイフン(‐)付きで連番を振る。

No	カテゴリ名
1	Web
1-1	HTML
1-2	CSS
1-3	JavaScript
2	DB
2-1	SQL
2-2	DB設計
3	インフラ
3-1	Linux
3-2	AWS

実現方法

要件としては、Webの画面上に表示したいのですが、それをフロント側で制御するか、バックエンド側で制御するか、DBから取得時に工夫するか、実現方法に迷いました。いろいろ迷った結果、Windows関数に頼るのが最も手っ取り早そうだったので、SQLに頼りました。

親カテゴリの連番にはdense_rank関数を使い、子カテゴリの連番にはrow_numberを使用。以下のようなSQLで、それぞれの連番を取得。dense_rank関数の引数でcase式を使うのが多分今回のポイント。

select 
dense_rank() over(order by case when parent_id is null then id else parent_id end) parent_row_num
, row_number() over(partition by parent_id order by id) child_row_num
, name
from categories
order by id;

結果は以下。
後は、プログラム側で連結して文字列にすればいい感じになりそう。

parent_row_num	child_row_num	name
1	1	Web
1	1	HTML
1	2	CSS
1	3	JavaScript
2	2	DB
2	1	SQL
2	2	DB設計
3	3	Web
3	1	Linux
3	2	AWS

ここからさらに加工すれば、SQLのみで完結させることも可。

select 
case
    when parent_id is null then parent_row_num
    else concat(parent_row_num, '-', child_row_num)
end No,
name
from(
    select 
    dense_rank() over(order by case when parent_id is null then id else parent_id end) parent_row_num
    , row_number() over(partition by parent_id order by id) child_row_num
    , name
    , parent_id
    from categories
    order by id
) tmp;

No	name
1	Web
1-1	HTML
1-2	CSS
1-3	JavaScript
2	DB
2-1	SQL
2-2	DB設計
3	インフラ
3-1	Linux
3-2	AWS

ここまでやるとちょっとやりすぎ感がありそう。コードの可読性やメンテナンス性を考えると、親と子の連番までをSQLで取得して、プログラムで枝番付きの連番文字列を作るのが、バランス良さそうな気がします。

dense_rank関数やrow_number関数についての詳細はMySQLの公式リファレンスを参照ください。

今回の例ではidが数値の連番だったのでdense_rankが使えましたが、キーがUUIDのような文字列の場合、同じ手法は多分使えず、選ぶカラムで工夫が必要そう。ただ、今回私が開発していたアプリではidに連番が使われていたので、この方法でうまいことできました。

まとめ

階層構造を持つデータに枝番付きの連番を振るというのは、それほど難しくなさそうな要件だと思ったけれど、プログラムで連番生成を制御しようとすると意外と面倒だなと感じました。まあでも、そういうときはやっぱりWindow関数が便利だなと再認識。