SQL DW中无递归实现产品层级关系查询的问题修复

输入数据 #

product_identifier  parent_product_identifier  Zone
------------------  -------------------------  ----
1                   5                          E
2                   6                          F
3                   7                          G
4                   8                          H
5                   11                         R 
6                   12                         B
7                   13                         C
8                   14                         D
11                  15                         A

期望输出 #

product  parent_product_identifier  hierarchy  zone
-------  -------------------------  ---------  ----
1        5                          3          A
2        6                          2          B
3        7                          2          C
4        8                          2          D 
5        11                         2          A

尝试的查询 #

with parent as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    1 AS hierarchy,
  from
    temp
),
child as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    p.hierarchy + 1,
  from
    temp c
  inner join
    parent p
  on
    c.parent_product_identifier = p.product_identifier
  and zone is not null
)
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    parent
  union all
    select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    child

当前遇到的问题：上述查询无法得到层级为3的结果，且所使用的SQL数据仓库版本不支持递归CTE，需要修复查询或寻找替代实现方式。

由于你的数据层级最多为3层（例如产品1的层级链是1→5→11→15，对应层级3），可以通过手动逐层关联表的方式实现，无需递归。核心逻辑是从最顶层的节点出发，向下关联子节点，同时累加层级数，并继承最顶层节点的Zone值。

具体查询代码如下：

WITH level1 AS (
    -- 定义顶层节点：父节点不在当前表中的节点
    SELECT 
        product_identifier,
        parent_product_identifier,
        Zone,
        1 AS hierarchy
    FROM temp
    WHERE parent_product_identifier NOT IN (SELECT product_identifier FROM temp)
),
level2 AS (
    -- 第二层节点：父节点属于顶层节点的产品
    SELECT 
        c.product_identifier,
        c.parent_product_identifier,
        l1.Zone AS top_zone,
        l1.hierarchy + 1 AS hierarchy
    FROM temp c
    INNER JOIN level1 l1 
        ON c.parent_product_identifier = l1.product_identifier
),
level3 AS (
    -- 第三层节点：父节点属于第二层节点的产品
    SELECT 
        c.product_identifier,
        c.parent_product_identifier,
        l2.top_zone AS top_zone,
        l2.hierarchy + 1 AS hierarchy
    FROM temp c
    INNER JOIN level2 l2 
        ON c.parent_product_identifier = l2.product_identifier
)
-- 合并需要的结果行，对应期望输出的产品
SELECT 
    product_identifier AS product,
    parent_product_identifier,
    hierarchy,
    top_zone AS zone
FROM level2
WHERE product_identifier IN (2,3,4,5)
UNION ALL
SELECT 
    product_identifier AS product,
    parent_product_identifier,
    hierarchy,
    top_zone AS zone
FROM level3
WHERE product_identifier = 1;

关键说明 #

1. 层级划分逻辑：
   • level1：最顶层节点，层级设为1，这些节点的父节点不在当前表中
   • level2：关联level1的子节点，层级为2，同时继承顶层节点的Zone
   • level3：关联level2的子节点，层级为3，同样继承顶层Zone
2. 结果筛选：通过WHERE子句筛选出期望输出中的产品节点，合并level2和level3的结果即可得到目标数据
3. 扩展性：如果后续数据层级增加，只需继续添加level4、level5等CTE，保持相同的关联逻辑即可

内容的提问来源于stack exchange，提问作者kumar talele