SQL 计算众数|极客教程

SQL 计算众数，需要找出某一列的众数（即在一组数据里出现次数最多的那个数）。例如，希望找出 DEPTNO 等于 20 的部门里员工工资的众数。就如下示例而言，众数为 3000。

SQL 计算众数问题描述

需要找出某一列的众数（即在一组数据里出现次数最多的那个数）。例如，希望找出 DEPTNO 等于 20 的部门里员工工资的众数。就如下示例而言，众数为 3000。

select sal
  from emp
 where deptno = 20
 order by sal
　
       SAL
----------
       800
      1100
      2975
      3000
      3000

SQL 计算众数解决方案

DB2 和 SQL Server
使用窗口函数 DENSE_RANK 对工资值出现的次数进行排序，以帮助我们找到众数。

 1  select sal
 2    from (
 3  select sal,
 4         dense_rank() over(order by cnt desc) as rnk
 5    from (
 6  select sal, count(*) as cnt
 8    from emp
 9   where deptno = 20
10   group by sal
11         ) x
12         ) y
13   where rnk = 1

Oracle
对于 Oracle 8i 来说，可以使用 DB2 的解决方案。如果你使用的是 Oracle 9i 及其后续版本，可以使用聚合函数 MAX 的 KEEP 扩展来找到 SAL 列的众数。注意，如果有多个众数，则 KEEP 解决方案只会保留工资值最高的那个。如果希望看到全部众数，就需要修改该解决方案，或者改用前面的 DB2 解决方案。在本例中，因为 3000 是 DEPTNO 等于 20 的部门里 SAL 列的众数，并且也是 SAL 列的最大值，所以下面的解决方案足以胜任。

1  select max(sal)
2          keep(dense_rank first order by cnt desc) sal
3    from (
4  select sal, count(*) cnt
5    from emp
6   where deptno=20
7   group by sal
8         )

MySQL 和 PostgreSQL
使用子查询查找众数。

1  select sal
2    from emp
3   where deptno = 20
4   group by sal
5  having count(*) >= all ( select count(*)
6                             from emp
7                            where deptno = 20
8                            group by sal )

SQL 计算众数扩展知识

DB2 和 SQL Server
内嵌视图 X 返回每一个 SAL 值及其出现的次数。内嵌视图 Y 使用窗口函数 DENSE_RANK（该函数允许出现排名相同的状况）对查询结果进行排序。
查询结果按照每一个 SAL 值的出现次数进行排序，如下所示。

1 select sal,
2        dense_rank()over(order by cnt desc) as rnk
3   from (
4 select sal,count(*) as cnt
5   from emp
6  where deptno = 20
7  group by sal
8        ) x
　
  SAL        RNK
----- ----------
 3000          1
  800          2
 1100          2
 2975          2

最外层的查询只是简单地筛选出 RNK 等于 1 的行。
Oracle
内嵌视图返回每一个 SAL 值及其出现的次数，如下所示。

select sal, count(*) cnt
  from emp
 where deptno=20
 group by sal
　
  SAL        CNT
----- ----------
  800          1
 1100          1
 2975          1
 3000          2

然后利用聚合函数 MAX 的 KEEP 扩展找到众数。下面的 KEEP 子句包含 DENSE_RANK、FIRST 和 ORDER BY CNT DESC 三个部分。

keep(dense_rank first order by cnt desc)

这样做很容易查找众数。KEEP 子句会查看内嵌视图返回的 CNT 值，并决定哪一个 SAL 值会被 MAX 函数返回。按照从右向左的执行顺序，所有的 CNT 值先按照降序排列，然后执行 DENSE_RANK 函数，并保留排在第一位的 CNT 值。仔细观察内嵌视图查询结果，可以发现 3000 对应的 CNT 是 2，在查询结果中是最大的。MAX(SAL) 的返回值是最大的 SAL 值，而且其对应的 CNT 值也是最大的，本例中是 3000。