选择每个 GROUP BY 组中的第一行?

2021-12-01 00:00:00 postgresql sql group-by sqlite greatest-n-per-group

正如标题所暗示的，我想选择用 GROUP BY 分组的每组行的第一行.

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

具体来说，如果我有一个看起来像这样的 purchases 表:

Specifically, if I've got a purchases table that looks like this:

SELECT * FROM purchases;

我的输出:

<头>
id客户总计
1乔5
2莎莉3
3乔2
4莎莉1

我想查询每个客户的最大购买量(total)的id.像这样:

I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

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

预期输出:

<头>
FIRST(id)客户第一个(总计)
1乔5
2莎莉3

推荐答案

关于支持 CTE 和窗口功能:

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

任何数据库都支持:

但是你需要添加逻辑来打破关系:

Supported by any database:

But you need to add logic to break ties:

  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

相关文章