Postgres - 按用户分组，每组返回一个用户的一行

Question

我有一个purchases表：

-----------------
user_id | amount
-----------------
1       | 12                     
1       | 4                     
1       | 8     
2       | 23                    
2       | 45                    
2       | 7               

我希望查询能够每个user_id返回一个行，但我想要的是每个user_id对应的行中，amount 最小的那一行。所以我的结果集应该如下：

-----------------
user_id | amount
-----------------                   
1       | 4                                        
2       | 7               

在user_id列上使用DISTINCT确保不获取重复的用户，但我不知道如何使其返回具有最少amount的用户行。

- MonkeyOnARock

Answer 1

你可以使用 distinct on：

select distinct on (user) t.*
from t
order by user, amount;

注意：如果您只需要最小值，那么group by通常是解决方案：

select user, min(amount)
from t
group by user;

Distinct on是Postgres的一种方便扩展，可轻松获取每个组的一行数据，并且通常比其他方法性能更好。

- Gordon Linoff

Answer 2

如果您的需求是输出等于最小金额的一行，比如表中包含交易日期并且您需要在输出中显示它，那么一种方便的方法是使用row_number() over()来选择所需的行。例如：

CREATE TABLE mytable(
   user_id  INTEGER  NOT NULL
  ,amount   INTEGER  NOT NULL
  ,trandate DATE   NOT NULL
);
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,12,'2020-09-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,4,'2020-10-02');
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,8,'2020-11-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,23,'2020-12-02');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,45,'2021-01-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,7,'2021-02-02');

select
user_id, amount, trandate
from (
    select user_id, amount, trandate
        , row_number() over(partition by user_id order by amount) as rn
    from mytable
    ) t
where rn = 1

result:

+---------+--------+------------+
| user_id | amount |  trandate  |
+---------+--------+------------+
|       1 |      4 | 2020-10-02 |
|       2 |      7 | 2021-02-02 |
+---------+--------+------------+

这在 db<>fiddle 上有演示，点击此处查看。

- Paul Maxwell

---

为什么你不使用select user_id, min(amount) from purchases group by user_id？这不是更高效的吗？真诚提问。 - hrmnjt

@hrmnjt 使用min(amount)并不能自动与其交易日期对齐，但使用row_number可以返回行号，因此您可以将最小金额及其交易日期（或该行上的任何数据）保持在一起 - 所以这不是性能问题，而是数据完整性的问题。 - Paul Maxwell