如何运行Teradata递归查询来查找表中的层次结构？

Question

我有一个表 -

ID  Name 
A  Steve 
AA  Carla 
AAA  Anthony 
AAAA  Martin 
AAB  Casey 
AABA  Mark 

在输入数据上述

•员工“史蒂夫”具有ID“A”。像这样的单个字符表示客户没有任何推荐就购买了该产品。

•从Steve的推荐中，Carla购买了另一种产品并获得了“AA”的ID。在这里，卡拉的客户代码“AA”表示他被某个客户使用ID“A”引用。

•从Carla的推荐中，Anthony和Casey分别购买了产品并分别获得了“AAA”和“AAB”的ID，表示它们由Carla以ID“AA”引用。

•每个推介的ID都有一个模式，可以在链中一直翻译为ID。

我需要这个表转换为以下使用Teradata的SQL递归查询ONLY

DATA LINEAGE 
1>Steve 
1.1>>Carla 
1.1.1>>>Anthony 
1.1.1.1>>>>Martin 
1.1.2>>>Casey 
1.1.2.1>>>>Marc 

以下几点是值得一提的关于此输出表：

•多个引用一个特定的人都在字母顺序。例如，安东尼&凯西以字母升序显示，安东尼获得1.1.1的前缀，而凯西获得1.1.2。

•可能有多个客户开始连锁店，即没有任何推介的直接客户。

•所有直接客户在其输出开始时应该有整数（没有点和单箭头）。

•开始供应链的客户，即直接客户也应该按字母顺序排列。

•前缀符号“>”的数量对应于层次的深度。

Answer 1

这不是递归的，但我不认为它需要。

CREATE MULTISET VOLATILE TABLE voltest 
(
    id VARCHAR(50), 
    nm VARCHAR(50) 
) PRIMARY INDEX (id) ON COMMIT PREServe ROWS; 
INSERT INTO voltest(id,nm) VALUES ('A','Steve'); 
INSERT INTO voltest(id,nm) VALUES ('AA','Carla'); 
INSERT INTO voltest(id,nm) VALUES ('AAA','Anthony'); 
INSERT INTO voltest(id,nm) VALUES ('AAAA','Martin'); 
INSERT INTO voltest(id,nm) VALUES ('AAB','Casey'); 
INSERT INTO voltest(id,nm) VALUES ('AABA','Mark'); 

SELECT 
    path || bumpers || nm as "DATA LINEAGE" 
FROM 
(
    SELECT 
     oreplace(TRIM(TRAILING '.' FROM (XMLAGG(trim(id_number) || '.' ORDER BY token_index) (VARCHAR(50)))), ' ', '') as path, 
     bumpers, 
     nm 
    FROM 
     (
      SELECT to_number(token, 'xxxxx')-9 as id_number, nm, token, token_index, SUBSTRING('>>>>>>>>>>>>>>' FROM 1 FOR max(token_index) OVER (PARTITION BY nm)) as bumpers 
      FROM TABLE (
       REGEXP_SPLIT_TO_TABLE(voltest.nm, volTest.id, '', 'i') 
        RETURNS (nm VARCHAR(50) character set unicode, token_index integer, token varchar(50) CHARACTER SET UNICODE) 
       ) AS dt 
     ) sub 
    GROUP BY nm, bumpers 
) sub2 

这regexp_split_to_table将在id列的每一个字符分割成它自己的一行处为重点。我们还捕获了token_index中的位置。拆分出的字符在token中被捕获。

我们使用to_number吐出你token的十六进制表示的整数值，并减去9于是A = 1，B = 2，依此类推。

我们对token_index使用窗口函数MAX()为每个名称确定要为该名称拉动多少个箭头>。

然后我们用XMLAGG连同一个句点分隔符（修剪掉最后一个）来汇总标记字符串。

这会吐出如下：

+-------------------+ 
| DATA LINEAGE | 
+-------------------+ 
| 1.1.1.1>>>>Martin | 
| 1.1.1>>>Anthony | 
| 1.1.2.1>>>>Mark | 
| 1.1>>Carla  | 
| 1>Steve   | 
| 1.1.2>>>Casey  | 
+-------------------+