Snowflake中能否用递归CTE将父子层级表展平为宽表？

问题背景 #

我需要将Snowflake中的父子层级表展平，现有多临时表的冗余方案可实现需求，但希望用递归CTE写出更易读、高效的代码，尝试后得到的结果不符合预期。

源表结构（parentmember_table） #

目标展平表（flat_table） #

现有冗余实现方案 #

use database MY_DATABASE;

create temporary table parentmember_table (memberid integer, parentmemberid integer, memberlevel integer);

INSERT INTO parentchild VALUES
  (1,       1,       1), -- root is where memberid = parentmemberid
  (6,       1,       2),
  (1007407, 6,       3),
  (1010551, 1007407, 4)
;


create or replace temporary table t0 as
select  parentmemberid as Level1
        , null as Level2
        , null as Level3
        , null as Level4
        , memberid
        , memberlevel
from    parentchild
where   parentmemberid = memberid
;


create or replace temporary table t1 as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t0 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;


create or replace temporary table t2 as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t1 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;

create or replace temporary table flat_table as
select  a.Level1
        , case  when (b.memberlevel = 2) then b.memberid
                else coalesce(a.Level2, null)
                end as Level2
        
        , case  when (b.memberlevel = 3) then b.memberid
                else coalesce(a.Level3, null)
                end as Level3
        
        , case  when (b.memberlevel = 4) then b.memberid
                else coalesce(a.Level4, null)
                end as Level4
        
        , b.memberid as memberid
        , b.memberlevel as memberlevel
from    t2 as a
        , parentchild as b
where   b.parentmemberid = a.memberid
        and b.parentmemberid <> b.memberid
;

递归CTE尝试及错误结果 #

递归CTE代码 #

create or replace temporary table recursive_cte_table as (
WITH RECURSIVE t (
        Level1
        , Level2
        , Level3
        , Level4
        , MemberId
        , MemberLevel
        ) AS (

        -- anchor_clause
        select  MemberId as Level1
                , null as Level2
                , null as Level3
                , null as Level4
                , MemberId
                , MemberLevel
        from    parentchild
        where   ParentMemberID = MemberID

        UNION ALL

        -- recursive_clause
        select  a.Level1
                , case  when (b.MemberLevel = 2) then b.MemberId
                        end as Level2
                , case  when (b.MemberLevel = 3) then b.MemberId
                        end as Level3
                , case  when (b.MemberLevel = 4) then b.MemberId
                        end as Level4
                , b.MemberId as MemberId
                , b.MemberLevel as MemberLevel
        from    t as a
                , parentchild as b
        where   b.ParentMemberId = a.MemberId
                and b.ParentMemberId <> b.MemberId
)

SELECT * FROM t
);

得到的错误结果 #

提问 #

请问是我的递归CTE代码存在错误，还是对递归CTE的适用场景理解有误？

内容的提问来源于stack exchange，提问作者Ali Mustafa