如何在 SQL 中查找模式，即 MAX(COUNT(*)) 不起作用
在统计中，模式是返回在一系列中最常出现的元素的函数。这就是人们在运行 MAX(COUNT(*))... 时试图实现的目标，但他们发现它不起作用。让我们看看如何在 SQL 中获取模式。
让我们像往常一样举一个例子。假设我们需要找出公司中哪个部门的员工最多。
SELECT department_id, COUNT(*) AS how_many
FROM employee
GROUP BY department_id
ORDER BY how_many DESC
LIMIT 1;
我们在这里所做的是获取唯一的最少部门，获取每个部门的员工人数，按（升序）员工人数对行进行排序，然后只取第一个。
WITH TIES
但是，多个部门可以有相同数量的员工，但上面的查询只能返回一行。如果我们想获得员工人数最多的所有部门，我们可以使用这个查询：
SELECT department_id, COUNT(*) AS how_many
FROM employee
GROUP BY department_id
ORDER BY how_many DESC
FETCH FIRST 1 ROWS WITH TIES;
FETCH FIRST n ROWS WITH TIES语法意味着我们当然想要第一行，但是接下来的行对于需要返回的列n具有相同的值ORDER BY（在这种情况下，相同）。COUNT(*)
支持 WITH TIES
但是，并非所有 DBMS 都支持这种语法。它支持：
- PostgreSQL
- 玛丽亚数据库
- SQL 服务器
- 甲骨文
它不支持：
- MySQL
- SQLite
- 数据库2
- 红移
- 雪花
替代方法
在不支持的 DBMS 上WITH TIES，您可以查看替代方法，其中包括子查询和窗口函数。
使用子查询获取所有模式
SELECT department_id, COUNT(*) AS how_many
FROM employee
GROUP BY department_id
HAVING how_many = (
SELECT COUNT(*) AS how_many
FROM employee
GROUP BY department_id
ORDER BY how_many DESC
LIMIT 1
);
在这里，我们：
- 使用本文中的第一个查询来获取部门中最多的员工数（子查询HAVING）。
- 再次计算每个部门的员工人数。
- 从这个统计数据中，我们只取员工人数等于最高员工人数的行。
使用窗口函数方法获取所有模式
虽然上述方法有效，但语法有点过于冗长，而且速度可能很慢。我们可以使用窗口函数。
SELECT department_id, how_many AS how_many
FROM (
SELECT
department_id,
COUNT(*) AS how_many,
RANK() OVER (ORDER BY COUNT(*) DESC) AS department_rank
FROM employee
GROUP BY department_id
) s
WHERE department_rank = 1;
我们所做的是：
- 按部门对员工进行分组，统计每个部门的员工人数。
- 为每一行分配一个排名，从最高计数 ( ORDER BY COUNT(*) DESC) 开始。
- 请注意，其中的行COUNT(*)相同，排名也将相同。
- 从该子查询的结果中，选择排名较低（最高计数）的行。
为什么 MAX(COUNT(*)) 不起作用
SELECT MAX(COUNT(*)) FROM table GROUP BY column 不适用于任何 DBMS。
原因是聚合函数（如MAX()和COUNT()）接受任意数量的行作为输入并返回一个值作为输出。如果使用，输入行是每个组的行GROUP BY，否则是所有表行。
在这种情况下，COUNT()将获取每个组的行并返回该组的值。您期望MAX()如此，但这不是您正在编写的内容：您将聚合结果传递给它。此操作是不可能的。
表现
所有示例都需要在 上的索引，或者第一列为 的索引。否则至少会执行一次全表扫描（顺序读取），对于大表，这个操作很慢。department_iddepartment_id
要返回多种模式，该WITH TIES方法是最快的。它大致与第一个查询一样快，最多返回一行。
窗口函数方法会很快。
子查询方法可能很慢，因为GROUP BY它运行了两次。请注意，这LIMIT 1不会使 aGROUP BY更快，因为数据库在读取所有相关行之前无法返回正确的结果。
