sqlite - 选择每个GROUP BY分组的第一行?

  显示原文与译文双语对照的内容

就像标题所暗示的,我想选择每组行的第一行,这些行与一个 GROUP BY 分组。

具体来说,如果我有一个类似这样的purchases 表:


SELECT * FROM purchases;

id | customer | total
---+----------+------
 1 | Joe | 5
 2 | Sally | 3
 3 | Joe | 2
 4 | Sally | 1

我想查询的id 最大购买每个 customer ( total ) 由。 像这样:


SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
 1 | Joe | 5
 2 | Sally | 3
时间:

在 Oracle 8 i+,SQL Server 2005 +,PostgreSQL 8.4 +,DB2,火鸟 2.1 +,Teradata,Sybase,vertical:


WITH summary AS (
 SELECT p.id, 
 p.customer, 
 p.total, 
 ROW_NUMBER() OVER(PARTITION BY p.customer 
 ORDER BY p.total DESC) AS rk
 FROM PURCHASES p)
SELECT s.*
 FROM summary s
 WHERE s.rk = 1

受任何数据库支持:

但是你需要添加逻辑来中断绑定:


 SELECT MIN(x.id), -- change to MAX if you want the highest
 x.customer, 
 x.total
 FROM PURCHASES x
 JOIN (SELECT p.customer,
 MAX(total) AS max_total
 FROM PURCHASES p
 GROUP BY p.customer) y ON y.customer = x.customer
 AND y.max_total = x.total
GROUP BY x.customer, x.total

这是常见的 问题,它已经过了很好的测试,并且具有高度优化的解决方案。 个人我更喜欢 LEFT JOIN 解决方案,Bill Karwin ( 原始日志,包含许多其他解决方案 ) 。

注意,群解决常见的问题可以令人惊讶的是在大多数官方来源之一,mysql手册 ! 查看常见查询的示例: :保留某些列的Group-wise最大值的行。

根据SubQs的存在,这个解决方案不是非常有效,因为存在


select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

在Postgres中,你可以像这样使用 array_agg:


SELECT customer,
 (array_agg(id ORDER BY total DESC))[1],
 max(total)
FROM purchases
GROUP BY customer

这将为你提供每个客户购买的id

有些注意事项:

  • array_agg 是一个聚合函数,因此它与 GROUP BY 一起工作。
  • array_agg 允许你指定一个只限于自身的排序范围,因此它不约束整个查询的结构。 如果你需要做一些不同于默认值的事情,你也可以使用语法来排序。
  • 一旦我们构建了数组,我们就采用第一个元素。 ( Postgres数组是 1 -indexed,而不是 0 -indexed ) 。
  • 你可以为第三个输出列使用 array_agg,但 max(total) 更简单。
  • DISTINCT ON 不同,使用 array_agg 可以让你保持 GROUP BY,以备其他原因。

非常快( postgres版本)


SELECT a.* 
FROM
 purchases a 
 JOIN ( 
 SELECT customer, min( id ) as id 
 FROM purchases 
 GROUP BY customer 
 ) b USING ( id );

或者更标准


SELECT a.* 
FROM
 purchases a 
 JOIN ( 
 SELECT customer, min( id ) as id 
 FROM purchases 
 GROUP BY customer 
 ) b ON ( a.id = b.id );

...