SQL: 分组与统计

栈烟
119 阅读2分钟

这篇文章主要讲带有分组+统计的SQL会有的小坑, 以及解决方案.

可以通过一个简单的业务SQL需求切入了解.

查找每个班级中成绩最好的学生

下面是聚合一些主要数据后的表(方便看主要的SQL, 少了JOIN操作):

create table achievements
(
    id         bigint auto_increment
        primary key,
    student_id bigint      not null,
    class_id   bigint      not null,
    subject    varchar(30) not null comment '科目',
    max_value  varchar(50) not null,
    value      int         not null
)
    comment '成绩表';

对于这个需求, 肯定是要先对数据进行分组 然后再求分组中的最大值.

那么我们可能会得出以下SQL:

SELECT * , MAX(value) AS max_value FROM achievements GROUP BY class_id

这个SQL乍一看是没啥问题的, 但执行一下SQL

SELECT *, MAX(value) AS max_value, GROUP_CONCAT(value) AS all_value,  GROUP_CONCAT(value) AS students
FROM achievements
GROUP BY class_id

会发现

也就是得到分组数据中, 并不一定是该组value最大的数据(其实选择的是分组中的第一条数据).
注意: MAX()聚合函数得到的是具体的值, 并不会得到一行数据.

那么用子查询+排序的方式, 先使用子查询根据成绩进行排序, 然后使用GROUP BY:

SELECT a.*
FROM (SELECT class_id, student_id, value FROM achievements ORDER BY value DESC) AS a
GROUP BY a.class_id;

执行后发现同样不行, 通过EXPLAIN对语句进行分析后发现只有一条执行情况 说明该语句是被优化器优化了(子查询消除).

那可以换种思路, 先查出分组数据和分组中最大的值, 然后再进行匹配:

SELECT a.*
FROM achievements a
         INNER JOIN
     (SELECT MAX(value) AS max_value, class_id
      FROM achievements
      GROUP BY class_id) AS b
     ON a.value = b.max_value AND a.class_id = b.class_id

执行后得到了正确的结果.

开窗函数

在MySQL的8.x版本提供了开窗函数可以很简单直观地查出: 
SELECT a.*
FROM (SELECT ai.class_id,
             ai.student_id,
             ai.value,
             RANK() OVER (PARTITION BY ai.class_id ORDER BY ai.value DESC) AS rk
      FROM achievements ai) as a
WHERE rk <= 2;

其他等效的SQL:

SELECT *
FROM achievements
WHERE (class_id, value) IN 
      (SELECT class_id, MAX(value)
       FROM achievements
       GROUP BY class_id);

SELECT a.*
FROM achievements a
         LEFT OUTER JOIN achievements b
                         ON a.class_id = b.class_id AND a.value < b.value
WHERE b.class_id IS NULL;

参考文章

stackoverflow: sql-select-only-rows-with-max-value-on-a-column 知乎: 聊聊MySql8.0中的group by 和 max函数取最新(优)一条记录的问题

avatar
文章
阅读
粉丝