识别重复记录组 (TSQL)

Question

我在 MSSQL 数据库中有大量表（超过 95 000 000 条记录）

id	Configuration_id	Equipment_group_id	name	Price
1	1	100	item1	10
2	1	100	item2	20
3	1	100	item3	30
4	2	100	item1	10
5	2	100	item2	20
6	2	100	item3	30
7	3	100	item1	10
8	3	100	item2	20
9	3	100	item3	31

我将识别重复的记录组。

配置 1 组

id	Configuration_id	Equipment_group_id	name	Price
1	1	100	item1	10
2	1	100	item2	20
3	1	100	item3	30

配置 2 组

id	configuration_id	Equipment_group_id	name	Price
4	2	100	item1	10
5	2	100	项目	2 20
6	2	100	item3	30

配置 3 组

id	配置_id	Equipment_group_id	name	价格
7	3	100	item1	10
8	3	100	item2	20
9	3	100	item3	31

• 在我的逻辑中 组 1 和 组2 是重复项
  • 具有相同数量的记录
  • 在equipment_group_id、name、price字段中具有相同的内容
• 在字段 equipment_group_id、name、price Group 1 和 >组 3 不重复，因为至少有一个不同的元素（最后一条记录的价格为 31，而不是 30）

如何构建查询来查找表中重复的所有组（不是记录） ?

Answer 1

对 95M 记录的查询性能可能并不理想，但这应该可以解决问题。

查找包含多行的组的精确匹配

DROP TABLE IF EXISTS #Config

CREATE TABLE #Config 
(id int
,configuration_id int
,equipment_group_id int
,name VARCHAR(100)
,price INT
)

INSERT INTO #Config
VALUES
(1,1,100,'item1',10)
,(2,1,100,'item2',20)
,(3,1,100,'item3',30)
,(4,2,100,'item1',10)
,(5,2,100,'item2',20)
,(6,2,100,'item3',30)
,(7,3,100,'item1',10)
,(8,3,100,'item2',20)
,(9,3,100,'item3',31)


;WITH cte_ConfigCount AS (
    SELECT *,ConfigTotalRowCnt = COUNT(*) OVER (PARTITION BY A.configuration_id) /*Counts how many rows in each config*/
    FROM #Config AS A
)

SELECT 
    A.configuration_id
    ,B.configuration_id
    ,TextDescription = CONCAT('Config #',A.configuration_id,' matches Config #',B.configuration_id)
    ,A.ConfigTotalRowCnt
    ,RowsMatch = COUNT(*)
FROM cte_ConfigCount AS A
INNER JOIN cte_ConfigCount AS B
    ON A.configuration_id < B.configuration_id /*Don't join to self and only join 1 way (so don't have one row with A-B and another row with B-A)*/
    AND a.equipment_group_id = B.equipment_group_id
    AND A.name = B.name
    AND A.price = B.price
GROUP BY A.configuration_id,A.ConfigTotalRowCnt,B.configuration_id
HAVING A.ConfigTotalRowCnt = COUNT(*) /*Only return where the total row for the config matches the rows where the configs match*/