sql - 识别 Redshift SQL 中的层次结构

Question

有没有办法在给定一组节点关系的情况下识别层次结构？

--input/existing table
select 'W' as edition, 'X' as new_edition
union
select 'Y' as edition, 'Z' as new_edition
union 
select 'Z' as edition, null as new_edition
union
select 'X' as edition, 'Y' as new_edition;

--expected output
select 'W' as all_editions, 'Z' as latest_edition, 3 as level
union
select 'X' as all_editions, 'Z' as latest_edition, 2 as level
union
select 'Y' as all_editions, 'Z' as latest_edition, 1 as level
union
select 'Z' as all_editions, 'Z' as latest_edition, 0 as level;

如果可以动态识别级别，那就太好了，但是我们将拥有的最大级别是 10。这应该涵盖可能有 2 个新版本 X、X2 链接到 W 的边缘情况

标签： sqlamazon-redshiftnested-loopshierarchical-data

Answer 1

好的，我想我有一个不使用 CTE 的基本版本的查询。它看起来并没有我想象的那么糟糕。

这是一个 6 级层次结构：

select
  e1,
  coalesce(e6, e5, e4, e3, e2, e1) as latest_edition,
  case when e2 is null then 0
       when e3 is null then 1
       when e4 is null then 2
       when e5 is null then 3
       when e6 is null then 4
       else 5
  end as level
from (
  select
    t1.edition as e1, t2.edition as e2, t3.edition as e3, 
    t4.edition as e4, t5.edition as e5, t6.edition as e6
  from my_table t1
  left join my_table t2 on t2.edition = t1.new_edition
  left join my_table t3 on t3.edition = t2.new_edition
  left join my_table t4 on t4.edition = t3.new_edition
  left join my_table t5 on t5.edition = t4.new_edition
  left join my_table t6 on t6.edition = t5.new_edition
) x