sql - 连通分量

Question

我有一组数据，它们是通过将相似的子项目匹配在一起，然后按“类别”对这些相似的项目进行分组而创建的。

现在，生成的类别必须以在每个“group_id”中将相关类别组合在一起的方式进行匹配。在下面的示例中，一个匹配项是 A->B->C->D->E->F->G，这是通过对行进行递归获得的。

我已经发布了我的 current answer ，它适用于这个简单的数据集，但由于实际数据集最多包含 1M 行，并且每个“group_id”可能有多达 60 个类别，因此此查询会导致实际数据出现“假脱机空间不足”错误。

请注意:

• 由于公司限制，我不能使用存储过程。
• 我不能使用用户定义函数 (UDF)
• 我不能使用用户定义类型 (UDT)

正确答案会

• 为 Teradata 或兼容软件编写
• 比我的回答更有效率
• 尊重我上面提到的限制

Sample Input:

Desired Output:

Answer 1

您需要一个递归方法，但是您的WITH RECURSIVE 会创建一个巨大的中间结果，这会导致不再有假脱机。

对于类似的过程，我使用了以下方法(最初在存储过程中使用 WHILE 循环):

CREATE MULTISET VOLATILE TABLE vt_tmp, NO Log  AS
 (
  SELECT group_id, category_1, category_2, 
     -- assign a unique number to 
     Dense_Rank() Over (ORDER BY group_id, category_1) AS rnk

  -- remove when you source data is unique
  GROUP BY 1,2,3 -- same result as a DISTINCT, but processed before DENSE_RANK
  FROM match_detail 
 )
WITH DATA
PRIMARY INDEX (category_2)
ON COMMIT PRESERVE ROWS;

现在重复以下更新，直到处理了 0 行:

-- find matching categories and assign them a common number    
UPDATE vt_tmp FROM
 ( SELECT e2.group_id, e2.category_1, Min(e1.rnk) AS minrnk
   FROM vt_tmp e1 JOIN vt_tmp e2
   ON e1.category_2 = e2.category_2
   AND e1.rnk < e2.rnk
   GROUP BY e2.group_id, e2.category_1
 ) x
SET rnk = minrnk
WHERE 
  vt_tmp.group_id = x.group_id
AND vt_tmp.category_1 = x.category_1
;

要获得您最终需要的相关类别:

SELECT group_id, category_1 AS category, rnk AS related_categories
FROM vt_tmp
UNION
SELECT group_id, category_2, rnk 
FROM vt_tmp

为了与您的预期结果完全匹配，您需要添加 DENSE_RANK:

SELECT group_id, category, Dense_Rank() Over (PARTITION BY group_id ORDER BY related_categories)
FROM
 (
   SELECT group_id, category_1 AS category, rnk AS related_categories
   FROM vt_tmp
   UNION
   SELECT group_id, category_2, rnk 
   FROM vt_tmp
 ) AS dt