每组产生n行 [英] Producing n rows per group

问题描述

众所周知，GROUP BY每组产生一行.我想每组产生多行.特定的用例是，例如，为每个商品选择两个最便宜的商品.

对于组中的两个或三个元素而言，这是微不足道的:

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

(在mysql中每个组选择n行)

但是我正在寻找一个查询，该查询可以显示每组n行，其中n任意大.换句话说，通过仅替换其中的一些常量，应该将显示每组5行的查询转换为显示每组7行的查询.

我不受任何DBMS的约束，所以我对在任何DBMS上运行的任何解决方案都感兴趣.如果使用某些非标准语法，那就很好.

解决方案

对于支持分析功能\窗口函数的任何数据库，这都相对容易

select *
  from (select type, 
               variety, 
               price,
               rank() over ([partition by something]
                            order by price) rnk
          from fruits) rank_subquery
 where rnk <= 3

如果省略[partition by something]，则将获得总排名前三的行.如果想要每个type的前三名，则可以在rank()函数中使用partition by type.

根据您要处理领带的方式，可能要使用dense_rank()或row_number()而不是rank().如果两行并列使用rank，则下一行的rnk为3，而下一行的rnk为2，且带有dense_rank.在这两种情况下，两个绑定行的rnk均为1.row_number会任意给两个绑定行之一的rnk设置为1，将另一个rnk设置为2.

It is known that GROUP BY produces one row per group. I want to produce multiple rows per group. The particular use case is, for example, selecting two cheapest offerings for each item.

It is trivial for two or three elements in the group:

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

(Select n rows per group in mysql)

But I am looking for a query that can show n rows per group, where n is arbitrarily large. In other words, a query that displays 5 rows per group should be convertible to a query that displays 7 rows per group by just replacing some constants in it.

I am not constrained to any DBMS, so I am interested in any solution that runs on any DBMS. It is fine if it uses some non-standard syntax.

解决方案

For any database that supports analytic functions\ window functions, this is relatively easy

select *
  from (select type, 
               variety, 
               price,
               rank() over ([partition by something]
                            order by price) rnk
          from fruits) rank_subquery
 where rnk <= 3

If you omit the [partition by something], you'll get the top three overall rows. If you want the top three for each type, you'd partition by type in your rank() function.

Depending on how you want to handle ties, you may want to use dense_rank() or row_number() rather than rank(). If two rows tie for first, using rank, the next row would have a rnk of 3 while it would have a rnk of 2 with dense_rank. In both cases, both tied rows would have a rnk of 1. row_number would arbitrarily give one of the two tied rows a rnk of 1 and the other a rnk of 2.

这篇关于每组产生n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！