正如标题所示,我想选择用GROUP BY分组的每组行中的第一行。

具体来说,如果我有一个如下所示的采购表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个客户的最大购买量(总购买量)。类似于:

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

当前回答

这是一个常见的最大的每组问题,它已经有了经过充分测试和高度优化的解决方案。就我个人而言,我更喜欢比尔·卡尔温(Bill Karwin)的左联解决方案(原始帖子中有很多其他解决方案)。

请注意,MySQL手册中可以意外地找到解决这个常见问题的一系列解决方案——尽管您的问题是在Postgres中,而不是MySQL中,但给出的解决方案应该适用于大多数SQL变体。请参见常见查询的示例::保持某一列的组最大值的行。

其他回答

通过我的测试,公认的OMG Ponies“受任何数据库支持”解决方案的速度很快。

在这里,我提供了一种相同的方法,但更完整、更干净的任何数据库解决方案。考虑联系(假设希望每个客户只获得一行,甚至每个客户最多获得多条记录),将为采购表中的实际匹配行选择其他采购字段(例如purchase_payment_id)。

任何数据库都支持:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

这个查询相当快,特别是当采购表上有一个类似(customer,total)的复合索引时。

备注:

t1、t2是可以根据数据库删除的子查询别名。注意:截至2017年1月本次编辑,MS-SQL和Oracle数据库目前不支持using(…)子句。您必须自己将其扩展到例如t2.id=purchase.id等。USING语法适用于SQLite、MySQL和PostgreSQL。

在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一起工作。arrayagg允许您指定仅限于自身的排序,因此它不会约束整个查询的结构。如果您需要执行与默认值不同的操作,还可以使用语法对NULL进行排序。一旦我们构建了数组,我们就使用第一个元素。(Postgres数组是1索引的,而不是0索引的)。您可以以类似的方式对第三个输出列使用array_agg,但max(total)更简单。与DISTINCT ON不同,使用array_agg可以保留GROUP BY,以防出于其他原因需要。

非常快速的解决方案

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

如果表是按id索引的,则速度非常快:

create index purchases_id on purchases (id);

这是一个常见的最大的每组问题,它已经有了经过充分测试和高度优化的解决方案。就我个人而言,我更喜欢比尔·卡尔温(Bill Karwin)的左联解决方案(原始帖子中有很多其他解决方案)。

请注意,MySQL手册中可以意外地找到解决这个常见问题的一系列解决方案——尽管您的问题是在Postgres中,而不是MySQL中,但给出的解决方案应该适用于大多数SQL变体。请参见常见查询的示例::保持某一列的组最大值的行。

对PostgreSQL、U-SQL、IBM DB2和Google BigQuery SQL使用ARRAY_AGG函数:

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