【KPI】家系図テーブルの操作 1【oracle】

某炎の紋章の家系図見てて、TRPGなどに使えるかなと思って家系図テーブルの運用を考える。
階層問い合わせを利用し、人の一覧である「M_人」テーブルと、子とその親を載せた「T_家」テーブルがある前提で、大雑把にデータを取得する方法を考える。

※WITH区で疑似テーブルとして作成

``` oracle:oracle
WITH M_人 AS
(
    SELECT '01' AS NO, '人01' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '02' AS NO, '人02' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '03' AS NO, '人03' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '04' AS NO, '人04' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '05' AS NO, '人05' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '06' AS NO, '人06' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '07' AS NO, '人07' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '08' AS NO, '人08' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '09' AS NO, '人09' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '10' AS NO, '人10' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '11' AS NO, '人11' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '12' AS NO, '人12' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '13' AS NO, '人13' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '14' AS NO, '人14' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '15' AS NO, '人15' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '16' AS NO, '人16' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '17' AS NO, '人17' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '18' AS NO, '人18' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '19' AS NO, '人19' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '20' AS NO, '人20' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '21' AS NO, '人21' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL UNION ALL
    SELECT '22' AS NO, '人22' AS 名, DATE '1900-01-01' AS 生年月日 FROM DUAL
)
, T_家 AS
(
    SELECT '03' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '04' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '05' AS NO, '01' AS 父, '02' AS 母 FROM DUAL UNION ALL
    SELECT '08' AS NO, '06' AS 父, '07' AS 母 FROM DUAL UNION ALL
    SELECT '09' AS NO, '06' AS 父, '07' AS 母 FROM DUAL UNION ALL
    SELECT '12' AS NO, '11' AS 父, '10' AS 母 FROM DUAL UNION ALL
    SELECT '13' AS NO, '11' AS 父, '10' AS 母 FROM DUAL UNION ALL
    SELECT '14' AS NO, '03' AS 父, '12' AS 母 FROM DUAL UNION ALL
    SELECT '15' AS NO, '03' AS 父, '12' AS 母 FROM DUAL UNION ALL
    SELECT '16' AS NO, '09' AS 父, '05' AS 母 FROM DUAL UNION ALL
    SELECT '17' AS NO, '09' AS 父, '05' AS 母 FROM DUAL UNION ALL
    SELECT '18' AS NO, '14' AS 父, '16' AS 母 FROM DUAL
)
SELECT  LEVEL AS レベル
    , SYS_CONNECT_BY_PATH(MAIN.NO, ',') AS 根_子
    , SYS_CONNECT_BY_PATH(MAIN.父, ',') AS 根_父
    , SYS_CONNECT_BY_PATH(MAIN.母, ',') AS 根_母
    , MAIN.NO AS 子
    , MAIN.父
    , MAIN.母
FROM T_家 MAIN
--START WITH (MAIN.NO) IN (SELECT NO FROM T_家)  -- ツリーのルート用件番号
CONNECT BY PRIOR MAIN.父 = MAIN.NO OR PRIOR MAIN.母 = MAIN.NO
ORDER BY MAIN.NO, MAIN.父, LEVEL
```

実行結果は下記となる。

|レベル|根_子|根_父|根_母|子|父|母|
|:------------------:|------------------:|------------------:|------------------:|:------------------:|:------------------:|:------------------:|
|1|,03|,01|,02|03|01|02|
|2|,15,03|,03,01|,12,02|03|01|02|
|2|,14,03|,03,01|,12,02|03|01|02|
|3|,18,14,03|,14,03,01|,16,12,02|03|01|02|
|1|,04|,01|,02|04|01|02|
|1|,05|,01|,02|05|01|02|
|2|,17,05|,09,01|,05,02|05|01|02|
|2|,16,05|,09,01|,05,02|05|01|02|
|3|,18,16,05|,14,09,01|,16,05,02|05|01|02|
|1|,08|,06|,07|08|06|07|
|1|,09|,06|,07|09|06|07|
|2|,17,09|,09,06|,05,07|09|06|07|
|2|,16,09|,09,06|,05,07|09|06|07|
|3|,18,16,09|,14,09,06|,16,05,07|09|06|07|
|1|,12|,11|,10|12|11|10|
|2|,15,12|,03,11|,12,10|12|11|10|
|2|,14,12|,03,11|,12,10|12|11|10|
|3|,18,14,12|,14,03,11|,16,12,10|12|11|10|
|1|,13|,11|,10|13|11|10|
|1|,14|,03|,12|14|03|12|
|2|,18,14|,14,03|,16,12|14|03|12|
|1|,15|,03|,12|15|03|12|
|1|,16|,09|,05|16|09|05|
|2|,18,16|,14,09|,16,05|16|09|05|
|1|,17|,09|,05|17|09|05|
|1|,18|,14|,16|18|14|16|

項目「レベル」は世代の数-1（連続するデータの数）となる。レベル2は親-子-孫の3世代いる。
「根_子」「根_父」「根_母」はそれぞれ、世代ごとの子、父、母となるコードが出力される。

これらを使用して、次はデータ操作を行う画面を作成したい。