SQL遍历二叉树

hive遍历二叉树

          1

   2             3

4   5         6    7

----------------------------------------------------输入                                                                       

node	parent
4	2
5	2
6	3
7	3
2	1
3	1
1	null

----------------------------------------------------输出

node	new_column
1	Root
2	Inner
3	Inner
4	Leaf
5	Leaf
6	Leaf
7	Leaf

select node,
case when parent is null then 'Root'
when array_contains(collect_set(parent)over(order by 1),node) then 'Inner'
else 'Leaf'
end as new_column
from 
(select 4 as node,2 as parent 
union all select 5 as node,2 as parent 
union all select 6 as node,3 as parent 
union all select 7 as node,3 as parent 
union all select 2 as node,1 as parent 
union all select 3 as node,1 as parent 
union all select 1 as node,null as parent 
) t