乾学院 53 号会员
1 回帖  •  92 浏览  • 
     ...

从 SQL 到 SPL：计算递归引用的层级

 

MSSQL 数据库某表具有多层级的自关联结构，第 2 个字段父节点 ID 是指向本表的第 1 个字段节点 ID 的外键，第 3 个字段是区域。

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

现在要找出上级层数大于等于 2 的那些节点的层级，以及最高层节点的区域，比如第 1 条记录的上级有 3 层，分别是 5-11-15，最高层是 15；第 2 条记录的上级有 2 层，分别是 6-12，最高层是 12。

product_identifier

hierarchy

Zone

1

3

A

2

2

B

3

2

C

4

2

D

5

2

A

SQL解法：

WITH dt AS (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent 
  FROM temp
  UNION ALL
  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, 
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1 
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) 
ORDER BY product_identifier;

只要找到各节点递归引用的所有层级，就可以方便地过滤出结果，但SQL没有直接可以用的函数，要用结构复杂的递归子查询+自关联join来实现，代码冗长难懂。

SPL提供了直接可用的函数，可以获得节点递归引用的所有层级。

　A

1

=mssql.query("select product_identifier,parent_product_identifier,zone from temp order by product_identifier”)

2

=A1.switch(parent_product_identifier, A1:product_identifier)

3

=A2.derive(~.prior(parent_product_identifier):t, t.len():hierarchy, t.m(-1).zone:z)

4

=A3.select(hierarchy>=2)

5

=A4.new(product_identifier, hierarchy, z:zone)

A2：建立引用关系，将parent_product_identifier替换成外键指向的本表的记录。

A3：新增计算列，用prior函数计算出本节点递归引用的所有层级，这些层级的层数以及最后一个层级的区域。

A4：选出递归引用的所有层级的层数大于等于2的节点。

A5：生成目标结果集。

问题来源：https://stackoverflow.com/questions/78299370/assistance-to-fix-query-to-identify-recursive-relationships-in-data-in-sql-dw