親IDをたくさん持つテーブルを、親ID(name)が左から順に並んでいるような表の形で抽出したい

Question

親IDを持つこれらを、親のnameから順に左から並んでいるテーブルを作成したいです
より子供の方からleftjoinでparent_idを紐付けてくっつけていくと、親の階層が揃いません。
親の方から数えると、aとgは同じ階層になるはずですが、親をひたすらたどるクエリを書くと、階層数が異なるため、崩れた表になってしまいます。どのように書いたらいいでしょうか

id	parent_id	name
1	null	a
2	3	b
3	4	c
4	1	d
5	null	e
6	7	f
7	null	g
8	6	h
9	null	i
10	null	j

Comments

[m.ts10806]

今現在組まれているSQLをご提示ください。また、親・子のテーブル定義とサンプルレコードがあればそれもあわせてお願いします。

Answer 1

こういった自己結合によるツリー形式のテーブル(ナイーブツリー）にはwith recursiveによる再帰読出を使用すると、階層の深さなどを条件として意識する必要がありません。
但し、自己結合の関係（今回であれば、idとparent_id）がループするデータがあると無限ループします。
階層の深さで制限を掛けるなどしてデータの確認を行って下さい。
※サンプルでは階層のレベル(=lvl)も付加してあります。

データ

drop table if exists list;
create table list (id int, parent_id int, name text);
insert into list values
 (1,null,'a') 
,(2,3,'b') 
,(3,4,'c') 
,(4,1,'d') 
,(5,Null,'e') 
,(6,7,'f') 
,(7,null,'g') 
,(8,6,'h') 
,(9,null,'i') 
,(10,null,'j') 
;

問い合わせ（Postgres）

with recursive tree as (
  select *
       , 1 ::int as lvl
       , array[name] as lvl_name 
  from list
  where  parent_id is null
 union all
  select list.*
       , tree.lvl+1
       , array_append(tree.lvl_name, list.name)
  from list inner join tree on list.parent_id=tree.id
)
select id, parent_id, name
     , lvl_name[1] as name1
     , lvl_name[2] as name2
     , lvl_name[3] as name3
     , lvl_name[4] as name4
from tree
-- where lvl<=4
order by lvl_name

追記　問い合わせ(Hive)

親を辿る方向で外部結合し、その状況から何階層目かを求めます（ここポイント）
その位置情報から取得すべき階層を判定し求めます。
※元データにこの何階層目かを判断できる情報があれば、その分簡潔になります。

select id, parent_id, name
     , case lvl when 4 then name_nest4 when 3 then name_nest3 when 2 then name_nest2 when 1 then name_nest1 end as name1 
     , case lvl when 4 then name_nest3 when 3 then name_nest2 when 2 then name_nest1 end as name2 
     , case lvl when 4 then name_nest2 when 3 then name_nest1 end as name3 
     , case lvl when 4 then name_nest1 end as name4 
from (
  select t1.*
       , t1.name as name_nest1
       , t2.name as name_nest2
       , t3.name as name_nest3
       , t4.name as name_nest4
       , case when t4.id is not null then 4 
           else case when t3.id is not null then 3
             else case when t2.id is not null then 2 else 1 end
         end end as lvl
  from list t1 left join list t2 on t2.id=t1.parent_id
       left join list t3 on t3.id=t2.parent_id
       left join list t4 on t4.id=t3.parent_id
) step1

Comments

[9nom]

回答ありがとございます！
Treasuredataを使っているのですが、(基本的にpostgresと同じですが)トレジャーデータではwith recursive treeがサポートされていないみたいでした、、、

[sazi]

追記しました。Hiveは扱ったことが無く、ネットの情報のみで記述しているのでエラーになるかもしれません。
並びは名前順でNulls First にしようとしましたが、可能かどうか分からなかったので、割愛。
階層はサンプルに合わせて４階層としているので、実際の階層数に合わせて下さい。