Oracle 階層構造テーブルで特定条件のレコードのカラムを取得したい

Question

◆環境
Oracle 12.1.0.2.0

◆質問内容
以下の階層構造のテーブルがあります。

ID	名称	親ID	フラグ
1	A	null	0
2	B	1	0
3	C	2	1
4	D	3	0
5	E	1	1
6	F	5	0
7	G	1	0

自レコードよりも上位の階層で、かつフラグが1になっている階層の名称を取得することは可能でしょうか。
上位階層のレコードでフラグが1になるレコードは必ず1つしかありません。

ID	名称	特定階層の名称
1	A
2	B
3	C
4	D	C
5	E
6	F	E
7	G

また、階層レベルが2の階層の名称を取得することは可能でしょうか。

ID	名称	階層レベル2の名称
1	A
2	B	B
3	C	B
4	D	B
5	E	E
6	F	E
7	G	G

Answer 1

「特定階層の名称」、「階層レベル2の名称」は階層の上限を決めてその階層数分の自己結合を行う必要があり、階層の上限を設定で行うような場合には、動的SQLが必要になります。

こういった階層構造には再帰を使用すると便利です。

津島博士のパフォーマンス講座(第31回 再帰的問合せについて)

他の言語の再帰とは違い、SQLの再帰は単なる順次読出しですので、そこまで難しくはありません。

質問のテーブルをtblとし再帰結合した結果をvtblとすると、再帰を使用した問い合わせは以下になります。

with vtbl(階層レベル, 特定階層の名称, 階層レベル２の名称, id, 名称, 親ID, フラグ) as (
    select 1, Null, Null
         , tbl.* 
    from tbl where 親ID is null
  union all
    select vtbl.階層レベル+1
         , case when vtbl.フラグ=1 then vtbl.名称 else vtbl.特定階層の名称 end
         , case when vtbl.階層レベル=1 then tbl.名称 else vtbl.階層レベル２の名称 end
         , tbl.id, tbl.名称, tbl.親ID, tbl.フラグ 
    from vtbl inner join tbl
      on vtbl.id=tbl.親ID
)
select * from vtbl
order by id

Comments

[toyotaku]

特定階層の名称について、
「自レコード"よりも"上位の階層」とあります。
case when vtbl.フラグ=1 then vtbl.名称 end
では、「自レコードの１つ上位の階層」になるため
case when vtbl.フラグ=1 then vtbl.名称 else vtbl.SNAME end
とすべきかと思います。

[sazi]

@toyotakuさん
フォローありがとうございます。
ほんとですね。なぜか一つ上と思い込んでました。修正します。

[nobinobiko]

想定するデータが取得できました。
ありがとうございました。

[Orlofsky]

https://www.oracle.com/jp/technical-resources/article/tsushima/tsushima-31.html
動画を見直そうと思ったけど、
>津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」【WMV】 【MP4】 【PDF】
動画【WMV】 【MP4】が2つともリンク切れ

【PDF】は読めたけど、その中の
https://www.oracle.com/technetwork/jp/ondemand/db-new/b-13-screen-1448415-ja.pdf
の連絡先 https://blogs.oracle.com/oracle4engineer/entry/otn_ondemand_questionnaire がリンク切れ

https://www.oracle.com/jp/technical-resources/article/tsushima/tsushima-31.html
に戻って、いちばん下の
>津島博士の記事についてのご質問はこちらまでお願いいたします。
https://secure.oracle.co.jp/otn/inquiry/index.php もリンク切れ

昔日の面影はなくなったようです。

Answer 2

単純な方法としては、以下のような階層レベル(経路情報)を管理するテーブルを作成する方法が考えられます。

create table 階層レベル (ID int, レベル int, 上位ID int);

データが追加されたときは、親IDのデータ群をレベル+1として格納します。

insert into 階層レベル select <自分のID>,レベル,上位ID from 階層レベル where ID=<親ID>;
insert into 階層レベル select <自分のID>,max(レベル)+1,<自分のID> from 階層レベル where ID=<自分のID>;

質問の例の場合は以下のようなinsert文になります。

insert into 階層レベル values(1,1,1);
insert into 階層レベル select 2,レベル,上位ID from 階層レベル where ID=1;
insert into 階層レベル select 2,max(レベル)+1,2 from 階層レベル where ID=2;
insert into 階層レベル select 3,レベル,上位ID from 階層レベル where ID=2;
insert into 階層レベル select 3,max(レベル)+1,3 from 階層レベル where ID=3;
insert into 階層レベル select 4,レベル,上位ID from 階層レベル where ID=3;
insert into 階層レベル select 4,max(レベル)+1,4 from 階層レベル where ID=4;
insert into 階層レベル select 5,レベル,上位ID from 階層レベル where ID=1;
insert into 階層レベル select 5,max(レベル)+1,5 from 階層レベル where ID=5;
insert into 階層レベル select 6,レベル,上位ID from 階層レベル where ID=5;
insert into 階層レベル select 6,max(レベル)+1,6 from 階層レベル where ID=6;
insert into 階層レベル select 7,レベル,上位ID from 階層レベル where ID=1;
insert into 階層レベル select 7,max(レベル)+1,7 from 階層レベル where ID=7;

これにより、以下のレコードになります。

select * from 階層レベル;
+------+--------+--------+
| ID   | レベル | 上位ID |
+------+--------+--------+
|    1 |      1 |      1 |
|    2 |      1 |      1 |
|    2 |      2 |      2 |
|    3 |      1 |      1 |
|    3 |      2 |      2 |
|    3 |      3 |      3 |
|    4 |      1 |      1 |
|    4 |      2 |      2 |
|    4 |      3 |      3 |
|    4 |      4 |      4 |
|    5 |      1 |      1 |
|    5 |      2 |      5 |
|    6 |      1 |      1 |
|    6 |      2 |      5 |
|    6 |      3 |      6 |
|    7 |      1 |      1 |
|    7 |      2 |      7 |
+------+--------+--------+

フラグが1の名称の取得は以下のとおりです。

select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID where b.フラグ=1 and a.ID<>b.ID;
+------+--------+--------+------+------+------+--------+
| ID   | レベル | 上位ID | ID   | 名称 | 親ID | フラグ |
+------+--------+--------+------+------+------+--------+
|    4 |      3 |      3 |    3 | C    |    2 |      1 |
|    6 |      2 |      5 |    5 | E    |    1 |      1 |
+------+--------+--------+------+------+------+--------+

レベル2の名称の取得は以下のとおりです。

select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID where a.レベル=2;
+------+--------+--------+------+------+------+--------+
| ID   | レベル | 上位ID | ID   | 名称 | 親ID | フラグ |
+------+--------+--------+------+------+------+--------+
|    2 |      2 |      2 |    2 | B    |    1 |      0 |
|    3 |      2 |      2 |    2 | B    |    1 |      0 |
|    4 |      2 |      2 |    2 | B    |    1 |      0 |
|    5 |      2 |      5 |    5 | E    |    1 |      1 |
|    6 |      2 |      5 |    5 | E    |    1 |      1 |
|    7 |      2 |      7 |    7 | G    |    1 |      0 |
+------+--------+--------+------+------+------+--------+

なお、以下のデメリットがあります。

• 冗長なデータを持つ必要がある。
• 行数が階層の深さなどに依存して多くなる。

【補足】
2階層以上の場合の出力例について説明します。
仮に質問の階層構造のテーブルのBのフラグが1で、Cのフラグが0の場合には以下のようにC、D、Fの上位階層の情報が表示できます。

ID	名称	親ID	フラグ
1	A	null	0
2	B	1	1
3	C	2	0
4	D	3	0
5	E	1	1
6	F	5	0
7	G	1	0

select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID inner join 階層構造のテーブル c on a.ID=c.ID where b.フラグ=1 and a.ID<>b.ID;
<= a(階層レベルの情報) => <==== b(フラグが1の名称) ===> <=== c(自レコードの情報) ===>
+------+--------+--------+------+------+------+--------+------+------+------+--------+
| ID   | レベル | 上位ID | ID   | 名称 | 親ID | フラグ | ID   | 名称 | 親ID | フラグ |
+------+--------+--------+------+------+------+--------+------+------+------+--------+
|    3 |      2 |      2 |    2 | B    |    1 |      1 |    3 | C    |    2 |      0 |
|    4 |      2 |      2 |    2 | B    |    1 |      1 |    4 | D    |    3 |      0 |
|    6 |      2 |      5 |    5 | E    |    1 |      1 |    6 | F    |    5 |      0 |
+------+--------+--------+------+------+------+--------+------+------+------+--------+

※わかりやすいように、階層構造のテーブルの自レコードの情報をcとして追加しました。

Comments

[sazi]

階層レベルを設定したとして、それをどう利用するのでしょう？

[etsuhisa]

SQLの具体例を記載しました。insert文もレベルが逆のほうが利用しやすいので修正しました。

[sazi]

> 自レコードよりも上位の階層で、かつフラグが1になっている階層の名称
について、一つ上の階層しか取得出来ていないですよ。
階層構造を制御するには、経路情報を持たなければ出来ないと思います。

[etsuhisa]

階層レベルのテーブルが経路情報であることを記載しました。また、2階層以上の場合の出力例についての説明を補足として追加しました。

Answer 3

階層問い合わせをご検討ください。

SQLの抽出結果を階層構造で表示するテクニック

図でイメージするOracle DatabaseのSQL全集 第6回 階層問い合わせ Oracle SQLの各機能をイメージ図を交えて解説

自レコードよりも上位の階層でかつフラグが1になっている階層の名称を取得
各レコードをルート（最上位）とし、
上位レコードのPARENT_IDとIDが一致する下位レコードを問い合わせ、
FLAGが1のレコードの名前を取得する

SELECT
    CONNECT_BY_ROOT(ID) AS ROOT_ID
,   CONNECT_BY_ROOT(NAME) AS ROOT_NAME 
,   CASE
        WHEN
            FLAG = 1
        AND CONNECT_BY_ROOT(ID) != ID --自レコードのフラグが1の場合は表示しない
        THEN NAME
        ELSE NULL
    END AS FLAG1_NAME
FROM
    TBL     
WHERE
    CONNECT_BY_ISLEAF = 1 -- LEAF行だけを取得
CONNECT BY 
    PRIOR PARENT_ID = ID  -- PARENT_IDで下位レコードのIDと紐づける
AND PRIOR FLAG != 1     -- FLAGが1なら下位レコードを問い合わせない
ORDER BY
    CONNECT_BY_ROOT(ID)

階層レベルが2の階層の名称を取得
各レコードをルート（最上位）とし、
上位レコードのPARENT_IDとIDが一致する下位レコードを問い合わせ、
親レコードのPARENT_IDがNULLではないレコードまで問い合わせる
※親レコードのPARENT_IDがNULL = 階層レベル2

SELECT
    CONNECT_BY_ROOT(ID) AS ROOT_ID
,   CONNECT_BY_ROOT(NAME) AS ROOT_NAME 
,   NVL2(PARENT_ID,NAME,NULL) LEVEL2_NAME 
FROM
    TBL     
WHERE
    CONNECT_BY_ISLEAF = 1 -- LEAF行だけを取得
CONNECT BY 
    PRIOR PARENT_ID = ID  -- PARENT_IDで下位レコードのIDと紐づける
AND PARENT_ID IS NOT NULL -- 親レコードのPARENT_IDがNULLは対象にしない(=レベル2)
ORDER BY
    CONNECT_BY_ROOT(ID)

Answer 4

複数の表からデータを取り出して表示させる 同じテーブルに対して、社員用のデータ取得用と上司のデータ取得用の2つを用意する、を参考にできるでしょう。