我正在努力解决一个问题,需要一些帮助。
示例表:
ID |Identifier1 | Identifier2
---------------------------------
1 | a | c
2 | b | f
3 | a | g
4 | c | h
5 | b | j
6 | d | f
7 | e | k
8 | i |
9 | l | h
我想对两列之间相互关联的标识符进行分组,并分配一个唯一的组ID。
预期输出:
Identifier | Gr_ID | Gr.Members
---------------------------------------------------
a | 1 | (a,c,g,h,l)
b | 2 | (b,d,f,j)
c | 1 | (a,c,g,h,l)
d | 2 | (b,d,f,j)
e | 3 | (e,k)
f | 2 | (b,d,f,j)
g | 1 | (a,c,g,h,l)
h | 1 | (a,c,g,h,l)
j | 2 | (b,d,f,j)
k | 3 | (e,k)
l | 1 | (a,c,g,h,l)
i | 4 | (i)
注:Gr. Members一栏不是必须的,主要用于更清晰的查看。
因此,组的定义是:如果行与组中的至少一行共享至少一个标识符,则该行属于该组
但是组id必须分配给每个标识符(由两列的并集选择),而不是分配给行。
关于如何构建查询以给出所需输出的帮助?
谢谢你。
- 更新:**以下是一些额外的样品组及其预期输出。
给定表格:
Identifier1 | Identifier2
----------------------------
a | f
a | g
a | NULL
b | c
b | a
b | h
b | j
b | NULL
b | NULL
b | g
c | k
c | b
d | l
d | f
d | g
d | m
d | a
d | NULL
d | a
e | c
e | b
e | NULL
预期产出:所有记录应属于组ID = 1的同一组。
给定表格:
Identifier1 | Identifier2
--------------------------
a | a
b | b
c | a
c | b
c | c
预期产出:记录应位于组ID = 1的同一组中。
4条答案
按热度按时间r7xajy2e1#
下面是一个变体,它不使用游标,但使用单个递归查询。
本质上,它将数据视为图中的边,递归遍历图中的所有边,在检测到循环时停止,然后将所有发现的循环分组,并给每个组一个编号。
请参阅下面关于它如何工作的详细说明。我建议您运行查询CTE-by-CTE并检查每个中间结果以了解它的作用。
我又添加了一个具有
z
值的行,以使多个行具有不成对的值。工作原理
我将使用第二组样本数据进行解释。
CTE_Idents
给出了同时出现在Ident1
和Ident2
列中的所有标识符的列表。由于它们可以以任何顺序出现,因此我们将两列一起UNION
。UNION
还删除了任何重复项。CTE_Pairs
给出了图中两个方向上所有边的列表,同样,UNION
用于删除重复的边。CTE_Recursive
是查询的主要部分,它从每个唯一标识符开始递归遍历图形。这些起始行由UNION ALL
的第一部分生成。UNION ALL
的第二部分递归连接到自身,将Ident2
链接到Ident1
。由于我们预先创建了CTE_Pairs
,所有边都在两个方向上写入,我们总是只能将Ident2
链接到Ident1
,这样我们就可以得到图中的所有路径,同时查询构建IdentPath
--一个到目前为止已经遍历过的逗号分隔的标识符字符串,它被用在WHERE
过滤器中:一旦我们遇到之前包含在路径中的标识符,递归就会停止,因为连接节点列表已用尽。
AnchorIdent
是递归的起始标识符,稍后将使用它来对结果进行分组。Lvl
并没有真正使用,我包含它是为了更好地理解正在发生的事情。CTE_CleanResult
仅留下与CTE_Recursive
相关的部分,并且再次使用UNION
合并Ident1
和Ident2
。现在,我们需要为每个
AnchorIdent
构建一个逗号分隔的Ident
值字符串。CROSS APPLY
与FOR XML
可以完成此操作。DENSE_RANK()
计算每个AnchorIdent
的GroupID
数。3lxsmp7m2#
此脚本根据需要生成测试集1、2和3的输出。算法注解作为脚本中的注解。
请注意:
#tree
。因此使用此脚本需要将源数据插入到#tree
中NULL
值。在使用ISNULL(source_col,CHAR(0))
插入#tree
时,请将NULL
值替换为CHAR(0)
,以避免此缺点。在从最终结果中选择时,请使用NULLIF(node,CHAR(0))
将CHAR(0)
替换为NULL
。请注意,answer using recursive CTEs更优雅,因为它是单个SQL语句,但对于大型输入集,使用递归CTE可能会导致执行时间非常长(请参阅关于该答案的评论)。下面描述的解决方案虽然更复杂,但对于大型输入集应该运行得更快。
第1组的输出:
第2组的输出:
第3组的输出:
e7arh2l63#
我的建议是使用带游标的存储过程,它容易实现,速度也比较快,只需两步:
质询:
结果:
Ident | GroupID --------------------------------------------------- a | 1 | b | 2 | c | 1 | d | 2 | e | 3 | f | 2 | g | 1 | h | 1 | j | 2 | k | 3 | l | 1 |
创建存储过程的代码:
Sp_GetIdentByGroup有一个速度索引,它使用游标准备所需的结果集。存储过程要求#PairIds表存在。
有关SQL How to group identifiers that are related with each other in specific groups的更多信息。
rjee0c154#
sp_GetIdentByGroup是一种非常好的方法。我使用CTE函数搜索了几天类似的解决方案,但它们太慢了。例如,我们在5秒内完成了20条记录,在1:40分钟内完成了30条记录,还有35条记录运行了很长时间。
您的程序设法排名150个项目是与分裂的秒组.谢谢!