如何在数据库中计算重复记录的数量？

Question

考虑下面的“tweets”表格：

tweet_id  user_id  text
-----------------------------------------
1         1        look at my tweet
2         1        look at my tweet
3         1        a very different tweet
4         1        look at my tweet
5         1        look at my tweets
6         2        a cool tweet
7         2        this is my tweet
8         1        hello
9         1        hello

对于每个用户，我想要统计重复推文的数量。在上面的例子中，user_id为1的用户共有5条推文，其中有2条是独特的（tweet_id为3和5），另外3条是重复的（1、2、4）。因此，对于用户1的查询结果应该是“3”。

[编辑] 看一下用户1。推文“look at my tweet”出现了3次，“hello”出现了2次。重复推文的总数是3 + 2 = 5。

- Pr0no

---

我可以说，任何最佳解决方案都会非常慢。 - safarov

可能是此问题的重复：在SQL表中查找重复值。 - tkruse

Answer 1

对于第一部分，您可以使用以下查询

select user_id, sum(count)
from
(
select user_id, text, count(tweet_id) count
from tweets 
group by 
user_id, text
having count(tweet_id) > 1
) t
group by user_id

内部查询查找所有发生超过一次的用户和推文。外部查询为每个用户累加重复值。

- Chetter Hummin

---

请问您能解释一下您的查询吗？当我对用户25（一个垃圾账户）运行此查询时，它返回39,740；而Apurv Gupta的查询对于同一用户返回36,577。我想通过比较这两个查询来找出原因。我正在向他询问同样的问题 :) - Pr0no

Answer 2

试试这个：

Select count(text)-count(distinct text) from tweets where user_id=1

- Apurv Gupta

---

请问您能解释一下您的查询吗？当我对用户25（一个垃圾账户）运行此查询时，它返回36,577；而Amit Bhargava的查询对于同一用户返回39,740。我想找出原因 :) - Pr0no

实际上，我的查询计算了重复的数量，因此我的查询将把“看看我的推文”视为“一个”合法条目，而其他两个将被视为重复项，而Amit的查询则计算了出现超过一次的文本数量。尽管如此，我的查询速度非常快。 :) - Apurv Gupta

count(text) 返回 7，而 count(distinct text) 返回 4，因此输出结果为 3。但是根据 OP 的预期输出应该是 5。您能否解释一下这个查询？ - Jayy

@Reveller：简单来说，这个查询会计算当消息开始重复时的数量。如果没有重复，它将返回0。如果一条消息出现两次，它将被计算为1（它重复了一次），如果它出现3次，查询将把它计算为2（重复了两次），以此类推每一行。结果可能对您有兴趣，但就我理解您的问题而言，它们绝对不是您所要求的内容。 - Andriy M

Answer 3

select count(*) as count, text from table group by text order by user_id desc;

您将需要一个服务器端的函数来根据user_id进行分组。

- Straseus

---

请解释一下您所说的“服务器端函数”按user_id分组的含义。为什么在这里使用“GROUP BY user_id”会失败？ - Pr0no

我不明白。我正在使用SQL。如果输出需要进行一些处理，我可以从PHP中调用查询，但我希望只用一个查询来完成所有操作，因为我认为这样速度更快。 - Pr0no