快捷搜索:
来自 网络数据库 2019-09-17 17:48 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

如何在mysql中查询每个分组的前几名,如何在sq

问题

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

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

(译)如何在sql中选取每一组的第一行/最后行/前几行,sql几行

转载请注明: TheViper  

部分翻译自How to select the first/least/max row per group in SQL

一些常见的sql问题有着类似的解决方法,比如:查找每个程序最近的日志,查找每个商品分类中最受欢迎的商品,查找每个玩家的玩出的前5高分。。。这些问题可以被归纳为从各组中选出Top N.

fruits表

新京葡娱乐场网址 1

选取每个分类中价格最低的行

步骤:1.找到要求的所需的值price。2.填充其他字段

方法1.自连接

按type分组并选取价格最低的行

select type, min(price) as minprice
from fruits
group by type;

新京葡娱乐场网址 2

用自连接把剩下的行与上面的行合并,由于上面的查询已经分好组了,这里用子查询把剩下的字段连接到没分组的表中。

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;

新京葡娱乐场网址 3

事实上此方法直接用group分组就可以了,不知道作者怎么想的。

SELECT TYPE,variety, MIN(price) AS minprice
FROM fruits
GROUP BY TYPE;

方法2 相关子查询

这种方法效率低点,但是很清晰。

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);

选取每组的Top N行

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));

可以看到,先选出价格最低的行,然后选出价格第二低的行,两个用or连接。

新京葡娱乐场网址 4

这个也可以用自连接写,不过要复杂点。可以看到,如果需要选出top3,top4,...的时候,这种方法就会变得糟糕。

这里有个更好的方法

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

这个可以理解成,遍历外面的fruits各行,如果同一分类中,还有其他行<=该行且这样的行的个数<=2,那该行符合要求,取出。

可以看到这种方法很优雅,因为改变n为其他值时都不需要重写。但是这个方法和上个方法本质上是一样的,都用到了子查询。而一些查询优化器在子查询上做的不够好。

使用union

如果(type, price)上有索引,并且索引可以过滤很多行,这时就可以对各个分类用limit.然后union把它们合并。

(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)

注意,这里是UNION ALL,不是UNION。这样做可以防止在返回结果前,对结果排序以去除重复的行。在该情景中不会出现重复的行,所以这里要告诉数据库不要排序去重。

关于union可以参见Using UNION to implement loose index scan in MySQL

使用用户变量(user variables) 仅限mysql

上面union这种方法在行数较少且有索引可以用来排序时,是个好办法。下面介绍的方法仅对mysql有效。介绍这种方法前请看我的另外一篇文章 how to number rows in MySQL。

文章简单说来,就是为同一分类的行依次递增编号

新京葡娱乐场网址 5

而下面介绍的方法正是基于此。

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;

子查询创建临时表,并向里面填充row_number,dummy,这是一次操作。然后从中选出row_number<=2的行,这又是一次操作。尽管有两次操作,但其复杂度仍然是O(n),只和表的大小相关,这比相关子查询的复杂度O(n2)好很多。相关子查询的n是分类个数,如果有很多分类的话,性能会很糟糕。

(完)

转载请注明: TheViper 部分翻译自How to select the first/le...

翻译

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

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

01 -------- ------------ -------
02 | type   | variety    | price |
03 -------- ------------ -------
04 | apple  | gala       |  2.79 |
05 | apple  | fuji       |  0.24 |
06 | apple  | limbertwig |  2.87 |
07 | orange | valencia   |  3.59 |
08 | orange | navel      |  9.36 |
09 | pear   | bradford   |  6.05 |
10 | pear   | bartlett   |  2.14 |
11 | cherry | bing       |  2.55 |
12 | cherry | chelan     |  6.33 |
13 -------- ------------ -------

选择每个分组中的最高分

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

1 -------- ---------- -------
2 | type   | variety  | price |
3 -------- ---------- -------
4 | apple  | fuji     |  0.24 |
5 | orange | valencia |  3.59 |
6 | pear   | bartlett |  2.14 |
7 | cherry | bing     |  2.55 |
8 -------- ---------- -------

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

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

01 select type, min(price) as minprice
02 from fruits
03 group by type;
04 -------- ----------
05 | type   | minprice |
06 -------- ----------
07 | apple  |     0.24 |
08 | cherry |     2.55 |
09 | orange |     3.59 |
10 | pear   |     2.14 |
11 -------- ----------

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

01 select f.type, f.variety, f.price
02 from (
03    select type, min(price) as minprice
04    from fruits group by type
05 ) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;
06  
07 -------- ---------- -------
08 | type   | variety  | price |
09 -------- ---------- -------
10 | apple  | fuji     |  0.24 |
11 | cherry | bing     |  2.55 |
12 | orange | valencia |  3.59 |
13 | pear   | bartlett |  2.14 |
14 -------- ---------- -------

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

01 select type, variety, price
02 from fruits
03 where price = (select min(price) from fruits as f where f.type = fruits.type);
04 -------- ---------- -------
05 | type   | variety  | price |
06 -------- ---------- -------
07 | apple  | fuji     |  0.24 |
08 | orange | valencia |  3.59 |
09 | pear   | bartlett |  2.14 |
10 | cherry | bing     |  2.55 |
11 -------- ---------- -------

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

找出每组中前N个值

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

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

01 select type, variety, price
02 from fruits
03 where price = (select min(price) from fruits as f where f.type = fruits.type)
04    or price = (select min(price) from fruits as f where f.type = fruits.type
05       and price > (select min(price) from fruits as f2 where f2.type = fruits.type));
06 -------- ---------- -------
07 | type   | variety  | price |
08 -------- ---------- -------
09 | apple  | gala     |  2.79 |
10 | apple  | fuji     |  0.24 |
11 | orange | valencia |  3.59 |
12 | orange | navel    |  9.36 |
13 | pear   | bradford |  6.05 |
14 | pear   | bartlett |  2.14 |
15 | cherry | bing     |  2.55 |
16 | cherry | chelan   |  6.33 |
17 -------- ---------- -------

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

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

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

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

使用 UNION

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

1 (select * from fruits where type = 'apple' order by price limit 2)
2 union all
3 (select * from fruits where type = 'orange' order by price limit 2)
4 union all
5 (select * from fruits where type = 'pear' order by price limit 2)
6 union all
7 (select * from fruits where type = 'cherry' order by price limit 2)

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

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

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:如何在mysql中查询每个分组的前几名,如何在sq

关键词: