`
ssxxjjii
  • 浏览: 950660 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

[翻译]如何在mysql中查询每个分组的前几名

 
阅读更多

http://my.oschina.net/u/1032146/blog/149300

问题

在工作中常会遇到将数据分组排序的问题,如在考试成绩中,找出每个班级的前五名等。 在orcale等数据库中可以使用partition 语句来解决,但在mysql中就比较麻烦了。这次翻译的文章就是专门解决这个问题的

原文地址: How to select the first/least/max row per group in SQL

翻译

在使用SQL的过程中,我们经常遇到这样一类问题:如何找出每个程序最近的日志条目?如何找出每个用户的最高分?在每个分类中最受欢迎的商品是什么?通常这类“找出每个分组中最高分的条目”的问题可以使用相同的技术来解决。在这篇文章里我将介绍如何解决这类问题,而且会介绍如何找出最高的前几名而不仅仅是第一名。

这篇文章会用到行数(row number),我在原来的文章 MySQL-specific 和 generic techniques 中已经提到过如何为每个分组设置行数了。在这里我会使用与原来的文章中相同的表格,但会加入新的price 字段

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

选择每个分组中的最高分

这里我们要说的是如何找出每个程序最新的日志记录或审核表中最近的更新或其他类似的排序问题。这类问题在IRC频道和邮件列表中出现的越来越频繁。我使用水果问题来作为示例,在示例中我们要选出每类水果中最便宜的一个,我们期望的结果如下

+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+

这个问题有几种解法,但基本上就是这两步:找出最低的价格,然后找出和这个价格同一行的其他数据

其中一个常用的方法是使用自连接(self-join),第一步根据type(apple, cherry etc)进行分组,并找出每组中price的最小值

select type, min(price) as minprice
from fruits
group by type;
+--------+----------+
| type   | minprice |
+--------+----------+
| apple  |     0.24 | 
| cherry |     2.55 | 
| orange |     3.59 | 
| pear   |     2.14 | 
+--------+----------+

第二步是将刚刚结果与原来的表进行连接。既然刚刚给结果已经被分组了,我们将刚刚的查询语句作为子查询以便于连接没有被分组的原始表格。

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| cherry | bing     |  2.55 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
+--------+----------+-------+

还可以使用相关子查询(correlated subquery)的方式来解决。这种方法在不同的mysql优化系统下,可能性能会有一点点下降,但这种方法会更直观一些。

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);
+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+

这两种查询在逻辑上是一样的,他们性能也基本相同

找出每组中前N个值

这个问题会稍微复杂一些。我们可以使用聚集函数(MIN(), MAX()等等)来找一行,但是找前几行不能直接使用这些函数,因为它们都只返回一个值。但这个问题还是可以解决的。

这次我们找出每个类型(type)中最便宜的前两种水果,首先我们尝试

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
   or price = (select min(price) from fruits as f where f.type = fruits.type
      and price > (select min(price) from fruits as f2 where f2.type = fruits.type));
+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | gala     |  2.79 | 
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| orange | navel    |  9.36 | 
| pear   | bradford |  6.05 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
| cherry | chelan   |  6.33 | 
+--------+----------+-------+

是的,我们可以写成自连接(self-join)的形式,但是仍不够好(我将这个练习留给读者)。这种方式在N变大(前三名,前4名)的时候性能会越来越差。我们可以使用其他的表现形式编写这个查询,但是它们都不够好,它们都相当的笨重和效率低下。(译者注:这种方式获取的结果时,如果第N个排名是重复的时候最后选择的结果会超过N,比如上面例子还有一个apple价格也是0.24,那最后的结果就会有3个apple)

我们有一种稍好的方式,在每个种类中选择不超过该种类第二便宜的水果

select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price <= fruits.price
) <= 2;

这次的代码要优雅很多,而且在N增加时不需要重新代码(非常棒!)。但是这个查询在功能上和原来的是一样。他们的时间复杂度均为分组中条目数的二次方。而且,很多优化器都不能优化这种查询,使得它的耗时最好为全表行数的二次方(尤其在没有设置正确的索引时),而且数据量大时,可能将服务器会停止响应。那么还有更好的方法吗?有没有办法可以仅仅扫描一次数据,而不是通过子查询进行多次扫描。(译者注:这种方法有一个问题,就是如果排名并列第一的数字超过N后,这个分组会选不出数据,比如price为2.79的apple有3个,那么结果中就没有apple了)

使用 UNION

如果已经为type, price设置了索引,而且在每个分组中去除的数据要多于包含的数据,一种非常高效的单次扫描的方法是将查询拆分成多个独立的查询(尤其对mysql,对其他的RDBMSs也有效),再使用UNION将结果拼到一起。mysql的写法如下:

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

Peter Zaistev写了相关的文章, 我在这里就不赘述了。如果这个方案满足你的要求,那它就是一个非常好的选择.

注意:这里要使用UNION ALL,而不是UNION。后者会在合并的时候会将重复的条目清除掉。在我们的这个示例中没有去除重复的需求,所以我们告诉服务器不要清除重复,清除重复在这个问题中是无用的,而且会造成性能的大幅下降。

使用用户自定义变量

但结果是数据表中很小一部分条目并且有索引用来排序的时候,使用UNION的方式是一个很好的选择。而当你要获取数据表中大部分条目时也有一种能达到线性时间的方法,那就是使用用户定义变量。这里我将介绍的仅仅是mysql中的用法。在我原来的博客在mysql中,如何为条目编号(How to number rows in MySQL)里介绍了它是怎么工作的:

set @num := 0, @type := '';
select type, variety, price
from (
   select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
  from fruits
  order by type, price
) as x where x.row_number <= 2;

这个方法并不仅仅做单次扫描,子查询在后台创建临时表,然后通过一次扫描将数据填充进去,然后在临时表中选择数据用于主查询的WHERE语句。但即使是两次扫描,它的时间复杂度仍是O(n),这里n是表示数据表的行数。它远比上面的相关子查询的结果O(n ^ 2)要好许多, 这里的n表示的是分组中平均条目数 - 即使是中等规模的数据也会造成极差的性能。(假设每种水果中有5 varitey,那么就需要25次扫描)

在MySQL中一次扫描的方法

如果你无法放弃你头脑中优化查询的想法,你可以试试这个方法,它不使用临时表,并且只做一次扫描

set @num := 0, @type := '';

select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
from fruits
group by type, price, variety
having row_number <= 2;

只要MySQL的GROUP BY语句符合标准,这个方式在理论上就是是可行。那么实际上可行吗?下面是我在MySQL 5.0.7的Windows 版上的结果

+--------+----------+-------+------------+--------+
| type   | variety  | price | row_number | dummy  |
+--------+----------+-------+------------+--------+
| apple  | gala     |  2.79 |          1 | apple  |
| apple  | fuji     |  0.24 |          3 | apple  |
| orange | valencia |  3.59 |          1 | orange |
| orange | navel    |  9.36 |          3 | orange |
| pear   | bradford |  6.05 |          1 | pear   |
| pear   | bartlett |  2.14 |          3 | pear   |
| cherry | bing     |  2.55 |          1 | cherry |
| cherry | chelan   |  6.33 |          3 | cherry |
+--------+----------+-------+------------+--------+

可以看到,这已经和结果很接近了。他返回了每个分组的第一行和第三行,结果并没有按照price的升序进行排列。当时HAVING 语句要求row_number不应当大于2。接下来是5.0.24a 在ubuntu上的结果:

+--------+------------+-------+------------+--------+
| type   | variety    | price | row_number | dummy  |
+--------+------------+-------+------------+--------+
| apple  | fuji       |  0.24 |          1 | apple  |
| apple  | gala       |  2.79 |          1 | apple  |
| apple  | limbertwig |  2.87 |          1 | apple  |
| cherry | bing       |  2.55 |          1 | cherry |
| cherry | chelan     |  6.33 |          1 | cherry |
| orange | valencia   |  3.59 |          1 | orange |
| orange | navel      |  9.36 |          1 | orange |
| pear   | bartlett   |  2.14 |          1 | pear   |
| pear   | bradford   |  6.05 |          1 | pear   |
+--------+------------+-------+------------+--------+

这次,所有的row_number都是1,而且好像所有行都返回了。可以参考MySQL手册用户自定义变量

使用这种技术的结果很难确定,主要是因为这里涉及的技术是你和我都不能直接接触的,例如MySQL在Group的时候使用哪个索引。如果你仍需要使用它 - 我知道很多人已经用了,因为我告诉了他们 - 你还是可以用的。我们正在进入SQL的真正领域,但是上面的结果是在没有设置索引的情况下得到的。我们现在看看了设置了索引之后group的结果是什么。

alter table fruits add key(type, price);

执行之后会发现没有什么变化,之后使用EXPLAIN查看查询过程,会发现此查询没有使用任何索引。这是为什么呢?因为Group使用了3个字段,但是索引只有两个字段。实际上,查询仍使用了临时表,所有我们并没完成一次扫描的目标。我们可以强制使用索引:

set @num := 0, @type := '';

select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;

我们看一下是否起作用了。

+--------+----------+-------+------------+--------+
| type   | variety  | price | row_number | dummy  |
+--------+----------+-------+------------+--------+
| apple  | fuji     |  0.24 |          1 | apple  | 
| apple  | gala     |  2.79 |          2 | apple  | 
| cherry | bing     |  2.55 |          1 | cherry | 
| cherry | chelan   |  6.33 |          2 | cherry | 
| orange | valencia |  3.59 |          1 | orange | 
| orange | navel    |  9.36 |          2 | orange | 
| pear   | bartlett |  2.14 |          1 | pear   | 
| pear   | bradford |  6.05 |          2 | pear   | 
+--------+----------+-------+------------+--------+

现在我们得到了我们想要的结果了,而且没有文件排序(filesort)和临时表。还有一种方法就是将variety提出到GROUP BY之外,这样它就可以使用自己的索引。因为这个查询是一个从分组中查询非分组字段的查询,它只能在 ONLYFULLGROUP_BY 模式关闭(链接)的情况下才能起作用。但是在没有特殊原因的情况下,我不建议你这么做。

其他方法

可以在评论中看到其他的方法,里面有的确有一些非常梦幻的方法。我一直在你们的评论获取知识,感谢你们。

总结

 

我们这里介绍了集中方法去解决“每个分组中最大的条目”这类问题已经进一步扩展到查询每组中前N个条目的方法。之后我们深入探讨了一些MySQL特定的技术,这些技术看起来有一些傻和笨。但是如果你需要榨干服务器的最后一点性能,你就需要知道什么时候去打破规则。对于那些认为这是MySQL本身的问题的人,我要说这不是,我曾经看到过使用其他平台的人也在做着同样的事情,如SQL Server。在每个平台上都会有很多特殊的小技巧和花招,使用他们的人必须去适应它。

分享到:
评论

相关推荐

    mysql分组取每组前几条记录(排名) 附group by与order by的研究

    –按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...

    MySql实现分组取n条最大记录.txt

    2. 使用变量`@r`来为每一行分配一个行号(`rnum`),以便于后续筛选出每个分组内的前几条记录。 3. 使用子查询来统计每个分组内符合条件的行数,并与当前行的行号进行比较,从而筛选出所需的记录。 #### 步骤三:...

    mysql使用GROUP BY分组实现取前N条记录的方法

    总结来说,MySQL中的GROUP BY分组功能不仅限于简单的聚合计算,还可以配合其他查询语句,如JOIN、HAVING和子查询,实现更复杂的查询需求,如获取每个分组的前N条记录。在处理这类问题时,理解并熟练运用这些技巧将有...

    LeetCode:MySQL分组内取前几名问题(难度:困难)

    题目:查询部门工资前三高的所有员工 Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。...对于这种分组内取前几名的问题,可以先group by

    MySql基本查询、连接查询、子查询、正则表达查询讲解

    不加条件,那么就只取每个分组的第一条。 如果想看分组的内容,可以加groub_concat [sql] view plain copy select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX; 3.2、一般情况下group需与...

    MySql分组后随机获取每组一条数据的操作

    在MySQL数据库中,有时我们需要对数据进行分组处理,并从每个分组中随机选取一条记录。这在统计分析或者抽样调查等场景中非常常见。本文将详细介绍如何在MySQL中实现这一操作,以及一些关于随机选取数据的优化方法。...

    MySQL学习-查询语句汇总

    这里的`GROUP BY article`将数据按`article`字段进行分组,然后`MAX(price)`计算出每个分组内的最大价格。 #### 四、找到每个组中具有某一列最大值的行 有时候,我们不仅需要知道每个组中的最大值,还需要知道这些...

    MySQL获取所有分类的前N条记录

    在MySQL数据库中,获取每个分类的前N条记录是一个常见的查询需求,特别是在处理如文章、商品、用户等分组信息时。以下将详细介绍如何通过SQL语句实现这一功能,并给出三个示例。 首先,我们需要理解基本的SQL语法,...

    Mysql连接,mysql查询

    1. 引入JDBC驱动:在Java项目中,确保引入了MySQL的JDBC驱动库(如`mysql-connector-java.jar`)。你可以通过Maven或Gradle将其添加为依赖项。 2. 加载驱动:在Java代码中,使用`Class.forName()`方法加载MySQL的...

    072301MySQL查询2

    **子查询**是在查询语句内部嵌套另一个查询,用于获取特定的数据集,然后在外部查询中使用。例如: ```sql SELECT s1.Name FROM (SELECT Name,Gender FROM student WHERE Age&gt;=30) AS s1 WHERE s1.Gender='M'; ```...

    MySQL学习资料.zip

    在MySQL的命令行操作中,用户通常会接触到以下几个关键概念: 1. **连接数据库**:使用`mysql -u [username] -p`命令,其中`username`是你的MySQL用户名。输入密码后,你将进入MySQL的命令行界面。 2. **选择...

    MySql培训日志_stu

    ### MySQL培训日志知识点梳理 ... - 每个列都是不可分割的基本数据项。 - **1.20.2 第二范式** - 在满足第一范式的基础上,确保表中的每一列都依赖于主键。 - **1.20.3 第三范式** - 在满足第二范式的基础上,...

    经典的SQL查询语句,将内容作为字段查询

    6. 分组和聚合函数:GROUP BY语句用于将数据按指定字段进行分组,而聚合函数(如COUNT、SUM、AVG、MAX和MIN)则可以对每个分组进行计算。例如,`GROUP BY department`可以按部门分组,`COUNT(*)`可以计算每个部门的...

    mysql查询练习

    此查询返回 `student` 表中年龄在 18 至 22 岁之间的所有学生信息,并计算出每个学生的年龄。 #### 分组查询 - **统计每个院系的学生人数**: ```sql SELECT department, COUNT(id) FROM student GROUP BY ...

    mysql培训题答案

    在这个特定的场景中,我们看到的是一个关于MySQL培训题目的描述,其中包含了几个数据库表格的结构和一些SQL查询示例。这些题目主要关注数据库的基本操作,如选择、连接、聚合函数以及条件过滤。 1. **员工信息查询*...

    MySQL命令大全

    -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库 A:常用source 命令 进入mysql数据库控制台, 如mysql -u root -p mysql&gt;use 数据库 然后使用source命令,后面参数为脚本...

    mysql酒店管理系统数据表设计

    字段adname作为主键,确保每个管理员的登录名都是唯一的。创建这个表的SQL语句如下: ```sql CREATE TABLE adinfo ( adname VARCHAR(20) PRIMARY KEY, adpwd VARCHAR(20) NOT NULL, adlevel INT NOT NULL ); ```...

    Oracle与MySQL的几点区别

    为了确保结果的准确性,Oracle要求每个非聚合列必须被包含在`GROUP BY`子句中或作为聚合函数的一部分。 #### 二、序列(Sequence)的创建和使用 在数据库设计中,序列(Sequence)是一种用于自动生成唯一编号的...

    mysql试题.docx

    在给定的试题中,主要涉及了几个关键的MySQL知识点: 1. **SQL查询排序**: - 使用`ORDER BY`子句对数据进行排序。例如:`SELECT * FROM employee ORDER BY deptid DESC, salary`,这将首先按部门编号降序排序,...

Global site tag (gtag.js) - Google Analytics