时间戳

首页 > 解决方案 > 如何在 SQL 中确定等效分组（递归）？

mysql - 如何在 SQL 中确定等效分组（递归）？

问题描述

我有一个由他们的 SKU 标识的产品列表。为简化起见，我在这里将它们命名为 A、B、C、D、...。默认情况下，这些 SKU 中的每一个都被分配了一个已经存在的GroupID，为简单起见，我在这里将它们编号为 1、2、3、...。

这同样GroupID意味着“这些 SKU 是等效的，因此可以使用/购买其中任何一个，因为它没有区别”。

问题是，一些 SKU 出现不止一次，因为它们来自不同的购买来源，但由于它们来自不同的来源，它们有不同的分组。

因此，目标是合并分组并确保它们具有相同的分组。

如果我的插图可能不是超级漂亮，我已经道歉，但我正在努力。这是一个关于原始数据的小数据表示例（第一行是列名）：

    Source      SKU  GroupID
    Seller1      A      1
    Seller1      B      1
    Seller1      C      1
    Seller2      B      2
    Seller2      D      2
    Seller2      E      2
    Seller3      A      3
    Seller3      B      3
    Seller4      F      4
    Seller4      G      4
    Seller4      H      4

结果应该是这样的：

    Source      SKU  GroupID
    Seller1      A      1
    Seller1      B      1
    Seller1      C      1
    Seller2      B      1
    Seller2      D      1
    Seller2      E      1
    Seller3      A      1
    Seller3      B      1
    Seller4      F      4
    Seller4      G      4
    Seller4      H      4

基本上，如果Any SKU in GroupID X is a subset of GroupID Y, then GroupID Y = GroupID X. 但这应该适用于所有 GroupID，因此它似乎是递归的。

我希望我可以展示我已经尝试过并且已经尝试了几天的代码，但实际上我只设法产生了垃圾。

在 C# 中，我知道如何处理这个问题，但我似乎无法完全理解 SQL，因为我没有那么有经验，不幸的是我需要在 SQL 中使用它。

我会感谢任何形式的帮助，即使这只是你们建议我应该尝试的提示或方向。非常感谢！

标签： mysqlsql

解决方案

您需要组之间的对应关系，可以使用递归 CTE 进行计算：

with recursive tt as (
      select distinct t1.groupid as groupid1, t2.groupid as groupid2
      from t t1 join
           t t2
           on t1.sku = t2.sku 
     ),
     cte as (
      select tt.groupid1, tt.groupid2, concat_ws(',', tt.groupid1, tt.groupid2) as visited 
      from tt
      union all
      select cte.groupid1, tt.groupid2, concat_ws(',', visited, tt.groupid2)
      from cte join
           tt
           on cte.groupid2 = tt.groupid1
      where find_in_set(tt.groupid2, cte.visited) = 0
     )
select groupid1, min(groupid2) as overall_group
from cte
group by groupid1;

然后，您可以将其加入原始表以获得“整体组”：

with recursive tt as (
      select distinct t1.groupid as groupid1, t2.groupid as groupid2
      from t t1 join
           t t2
           on t1.sku = t2.sku 
     ),
     cte as (
      select tt.groupid1, tt.groupid2, concat_ws(',', tt.groupid1, tt.groupid2) as visited 
      from tt
      union all
      select cte.groupid1, tt.groupid2, concat_ws(',', visited, tt.groupid2)
      from cte join
           tt
           on cte.groupid2 = tt.groupid1
      where find_in_set(tt.groupid2, cte.visited) = 0
     )
select t.*, g.overall_group
from t join
     (select groupid1, min(groupid2) as overall_group
      from cte
      group by groupid1
     ) g
     on t.groupid = g.groupid1;

这是一个 db<>fiddle。

注意：您的示例数据相当“完整”，因此您不需要针对该特定数据使用递归 CTE。但是，我猜您的真实组的重叠较少，在这种情况下递归是必要的。

推荐阅读