mysql中GROUP BY和 DISTINCT在去重和计数方面的相似与区别（排名问题为例）

问题来源

Group by和distinct与count函数的组合，都可以实现对某一列的重复值消除的作用。
这里以LeetCode上的一个排名练习为例，来证明一下二者的相似性。

mysql> select * from Scores;

+----+-------+
| Id | Score |
+----+-------+
|  1 |  3.50 |
|  2 |  3.65 |
|  3 |  4.00 |
|  4 |  3.85 |
|  5 |  4.00 |
|  6 |  3.65 |
+----+-------+

这里设置3种排名规则
规则1：相同分数取相同排名，相同排名后的下一名按名次加1，排名应连续
规则2：相同分数取相同排名，相同排名后的下一名按人数进行排名，排名不连续
规则3：相同分数取不同排名，不分先后

规则1

思路：

1. 建立score列和对应的rank列，按score列降序排列
2. 其中rank列以子查询的方式表示，选取大于对应分数的列应用count函数

count(distinct)解法：

mysql> select s1.Score as score,
    -> (select count(distinct s2.Score) from Scores s2 where s1.Score<=s2.Score) as rank
    -> from Scores s1 order by Score desc;

选用group by和count的组合则需要创建一个视图复用count，所以应用两次count的效果其实和count(distinct)是相同的。

mysql> create view count 
      ->as select Score, count(*) as num from Scores 
      ->group by score;

mysql> select Scores.Score as score,
    -> (select count(*) from count s2 where Scores.Score<=count.Score) as rank
    -> from Scores s1 order by Score desc;

通过这两种方法可以看出，在我们常用的规则1的排序中，很明显count和distinct的复用是最简洁的。

规则2

原思路：
1. 创建视图count保存各分数重复次数
2. 使用sum函数来记录排名
在这个问题里，用count和distinct的组合是无法解决的。因为这里需要对重复值进行计数，因此只能使用count和group by的组合。

新思路：

原本我认为如果重复计数的话distinct无法完成，但实际上distinct可以对多列作用，这也就相当于变相为每个score值都进行了计数，完成了group by的工作。
具体思路同规则1相似。

count(distinct)解法：

mysql> select s1.score, 
            (select count(distinct score, id)+1 from Scores s2 where s1.score<s2.score) 
         as rank 
        from Scores s1 
        order by rank;

group by解法：

mysql> create view count 
      ->as select Score, count(*) as num from Scores 
      ->group by Score;
      
mysql> select Scores.Score as score, 
            (select 
                ifnull(
                (select sum(num)+1 from count where count.Score>Scores.Score)
                , 1)
            ) as rank 
            from Scores order by Score DESC;
                
  
 +-------+------+
| score | rank |
+-------+------+
|  4.00 |    1 |
|  4.00 |    1 |
|  3.85 |    3 |
|  3.65 |    4 |
|  3.65 |    4 |
|  3.50 |    6 |
+-------+------+

规则3

思路：

与问题1的思路相同，两种方式都可以。区别在于进行计数时的分类标准是(score, id)的组合。
这里只选用count(distinct)的解决方法。

mysql> select s1.score, 
        (select count(distinct score, id) from Scores s2 
            where (s1.score, s1.id)<=(s2.score, s2.id)
        ) as rank 
        from Scores s1 order by rank;


+-------+------+
| score | rank |
+-------+------+
|  4.00 |    1 |
|  4.00 |    2 |
|  3.85 |    3 |
|  3.65 |    4 |
|  3.65 |    5 |
|  3.50 |    6 |
+-------+------+

总结

在sql中进行排序总觉得有些别扭，如果可能还是通过python的pandas库操作会更轻松一些。但不管怎么样，这里对count(distinct * )和group by+count( * )的组合进行了一些比较。可以看出，group by更便于计算重复值的数目，count(distinct)也能实现计算重复值但比较不容易理解。因此，前者在进行多个类别的交叉检索中很重要，后者则在选择排名规则时更简洁。但二者都能通过某种方式达到计算重复值或是排名的效果。