正如标题所示,我想选择用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 |
如果要从聚合行集合中选择任何行(根据特定条件)。如果您想使用除max/min之外的另一个(sum/avg)聚合函数。因此,您不能在DISTINCT ON时使用线索
您可以使用下一个子查询:
SELECT
(
SELECT **id** FROM t2
WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )
) id,
name,
MAX(amount) ma,
SUM( ratio )
FROM t2 tf
GROUP BY name
您可以将amount=MAX(tf.amount)替换为任何需要的条件,但有一个限制:此子查询不能返回多行
但是如果你想做这样的事情,你可能需要寻找窗口函数
这是我们如何通过使用windows函数实现的:
create table purchases (id int4, customer varchar(10), total integer);
insert into purchases values (1, 'Joe', 5);
insert into purchases values (2, 'Sally', 3);
insert into purchases values (3, 'Joe', 2);
insert into purchases values (4, 'Sally', 1);
select ID, CUSTOMER, TOTAL from (
select ID, CUSTOMER, TOTAL,
row_number () over (partition by CUSTOMER order by TOTAL desc) RN
from purchases) A where RN = 1;
在支持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
任何数据库都支持:
但你需要添加逻辑来打破联系:
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