階層問い合わせを書いてみた

こちらはジャンルなしオンラインもくもく会 Advent Calendar 2021
9日目の記事です
https://adventar.org/calendars/6803
まだ参加されていない方も、お気軽にもくもくしに来てください

OracleDatabaseには階層問い合わせ機能がありますが、毎回わからなくなるのでメモです

事前準備

下記のテーブルを作ってレコードを入れます

CREATE TABLE 社員 (
	社員ID VARCHAR2(4)
	,社員名 VARCHAR2(30)
	,上司ID VARCHAR2(4)
	,入社日 DATE
	,部署ID VARCHAR2(2)
)
;

INSERT ALL
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('A001', 'hogehoge', 'A020', sysdate, 'D1')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('A020', 'fugafuga', 'X099', sysdate, 'D1')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('X099', 'uaaaaaaaa',   NULL, sysdate, 'X9')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('B088', 'foo', 'B077', sysdate, 'C7')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('B077', 'bar', 'X099', sysdate, 'C7')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('C048', 'hello', 'B077', sysdate, 'C7')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('W029', 'world', 'Q999', sysdate, 'C7')
INTO 社員(社員ID, 社員名, 上司ID, 入社日, 部署ID) VALUES('Z017', 'xxx', 'B088', sysdate, 'C7')
SELECT * FROM dual
;
COMMIT;

階層問い合わせと言えばCONNECT BYです

-- とりあえずの階層問い合わせ
 SELECT 社員ID
      , 社員名
      , 上司ID
   FROM 社員
CONNECT BY PRIOR 社員ID = 上司ID
ORDER BY 社員ID, LEVEL DESC
;

こうなる

抽出結果

ルートは指定してませんが、“W029”の社員は存在しない上司IDが指定されているので抽出されないかと思いきやされました。
それはいいですが、なんだかZ017レコードとか4つも出ていてわけがわからんので片っ端から疑似列を出力します

 SELECT 社員ID
      , 社員名
      , 上司ID
      , LEVEL "疑似列(親子を表す)"
      , CONNECT_BY_ROOT 社員名 "nLEVEL上の人"
      , CONNECT_BY_ISCYCLE 
      , CONNECT_BY_ISLEAF "子有り1/その他0"
      , SYS_CONNECT_BY_PATH(社員名, '/') "Path表示"
   FROM 社員
CONNECT BY NOCYCLE PRIOR 社員ID = 上司ID -- CONNECT_BY_ISCYCLEを使うときはNOCYCLEにする必要がある
ORDER BY 社員ID, LEVEL DESC
;

こうなる

抽出結果２

親階層がある場合は、その分レコード出てるようなので社員IDが'Z017'とか4レコード出てます。
LEVEL1が自分で、2が一つ上の上司、3が二つ上の上司のような形でいくつ上の上司かということがわかります

特に意図はないですが、とりあえず社員名と上司名を出してみました

 SELECT DISTINCT -- 一旦DISTINCTで逃げ
        社員1.社員ID
      , 社員1.社員名
      , 社員1.上司ID
      , 社員2.社員名 上司名
   FROM 社員 社員1 LEFT OUTER JOIN 社員 社員2
     ON 社員1.上司ID = 社員2.社員ID
CONNECT BY  PRIOR 社員1.社員ID = 社員1.上司ID
  ORDER BY 社員1.社員ID
;

結果

抽出結果３

そこそこ階層問い合わせらしくなってきました。
しかし、階層問合せでは、ORDER BYとGROUP BYはダメらしい

 SELECT DISTINCT -- 一旦DISTINCTで逃げ
        社員1.社員ID
      , 社員1.社員名
      , 社員1.上司ID
      , 社員2.社員名 上司名
   FROM 社員 社員1 LEFT OUTER JOIN 社員 社員2
     ON 社員1.上司ID = 社員2.社員ID
CONNECT BY PRIOR 社員1.社員ID = 社員1.上司ID
  ORDER SIBLINGS BY 社員1.社員ID
;

結果

抽出結果４

次はW029を消したいので“X099”を根っこに指定します

 SELECT 社員1.社員ID
      , 社員1.社員名
      , 社員1.上司ID
      , 社員2.社員名 上司名
      , SYS_CONNECT_BY_PATH(社員1.社員名, '/') "Path表示"
   FROM 社員 社員1 LEFT OUTER JOIN 社員 社員2
     ON 社員1.上司ID = 社員2.社員ID
CONNECT BY PRIOR 社員1.社員ID = 社員1.上司ID
  START WITH 社員1.社員ID = 'X099'
  ORDER SIBLINGS BY 社員1.社員ID
;

こんな感じで出力

抽出結果５

ついでにPathも出しました
社員名と上司名とPath表示があれば随分階層表示らしくなりましたので良しとします。