sql - 如何查找重复项(正确方法)？

Question

我正在使用Snowflake数据库并运行此查询以查找总数，不同记录的数量和差异:

select 
    (select count(*) from mytable) as total_count, 
    (select count(*) from (select distinct * from mytable)) as distinct_count,
    (select count(*) from mytable) - (select count(*) from (select distinct * from mytable)) as duplicate_count
from mytable limit 1;

结果:

1,759,867
1,738,924
20,943 (duplicate_count)

但是，当尝试使用另一种方法时(将所有列分组并找到count大于1的位置):

select count(*) from (
SELECT 
    a, b, c, d, e,
    COUNT(*)
FROM 
    mytable
GROUP BY 
    a, b, c, d, e
HAVING 
    COUNT(*) > 1
)

我得到 5,436。

为什么重复次数有所不同？ ( 20,943与 5,436)

谢谢。

Answer 1

好的。让我们从一个简单的示例开始:

create table #test
(a int, b int, c int, d int, e int)

insert into #test values (1,2,3,4,5)
insert into #test values (1,2,3,4,5)
insert into #test values (1,2,3,4,5)
insert into #test values (1,2,3,4,5)
insert into #test values (1,2,3,4,5)
insert into #test values (5,4,3,2,1)
insert into #test values (5,4,3,2,1)
insert into #test values (1,1,1,1,1)

并尝试您的子查询以了解您将获得什么:

SELECT 
    a, b, c, d, e,
    COUNT(*)
FROM 
    #test
GROUP BY 
    a, b, c, d, e
HAVING 
    COUNT(*) > 1

想一会儿...

当当当当〜

a   b   c   d   e   (No column name)
1   2   3   4   5   5
5   4   3   2   1   2

因为您使用了“group by”，它只会返回两行。但是它仍然计算每个a，b，c，d，e组合的重复编号。

如果您想要重复的总数，请尝试以下操作:

select sum(sub_count) from (
SELECT 
    a, b, c, d, e,
    COUNT(*) - 1 as sub_count
FROM 
    #test
GROUP BY 
    a, b, c, d, e
HAVING 
    COUNT(*) > 1)a

如果我正确理解了您的原始查询，在这种情况下，您需要减一。如果我错了，请纠正我。