正如标题所示，我想选择用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 FUNCTION on total和GROUP by id和customer轻松实现。
SELECT id, customer, MAX(total) FROM purchases GROUP BY id, customer
ORDER BY total DESC;
其他回答
Snowflake/Teradata支持QUALIFY子句，其工作方式与窗口函数的HAVING类似：
SELECT id, customer, total
FROM PURCHASES
QUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1
对于SQl Server，最有效的方法是：
with
ids as ( --condition for split table into groups
select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)
)
,src as (
select * from yourTable where <condition> --use this as filter for other conditions
)
,joined as (
select tops.* from ids
cross apply --it`s like for each rows
(
select top(1) *
from src
where CommodityId = ids.i
) as tops
)
select * from joined
不要忘记为使用过的列创建聚集索引
查询：
SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL
这是怎么回事！（我去过那里）
我们希望确保每次购买的总金额最高。
一些理论知识（如果您只想了解查询，请跳过此部分）
让Total是一个函数T（customer，id），其中它返回一个给定名称和id的值为了证明给定的总数（T（customer，id））是最高的，我们必须证明我们想证明
∀x T（customer，id）>T（customer，x）（这个总数高于所有其他该客户的总计）
OR
∃x T（customer，id）<T（customers，x）（不存在更高的总数该客户）
第一种方法需要我们获取我不太喜欢的名字的所有记录。
第二个将需要一个聪明的方式来表示，没有比这个更高的记录了。
返回SQL
如果我们在表的名称和总数小于连接表的情况下留下连接表：
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
我们确保要加入的同一用户的另一条记录总数较高的所有记录：
+--------------+---------------------+-----------------+------+------------+---------+
| purchases.id | purchases.customer | purchases.total | p.id | p.customer | p.total |
+--------------+---------------------+-----------------+------+------------+---------+
| 1 | Tom | 200 | 2 | Tom | 300 |
| 2 | Tom | 300 | | | |
| 3 | Bob | 400 | 4 | Bob | 500 |
| 4 | Bob | 500 | | | |
| 5 | Alice | 600 | 6 | Alice | 700 |
| 6 | Alice | 700 | | | |
+--------------+---------------------+-----------------+------+------------+---------+
这将有助于我们在不需要分组的情况下筛选每次购买的最高总额：
WHERE p.total IS NULL
+--------------+----------------+-----------------+------+--------+---------+
| purchases.id | purchases.name | purchases.total | p.id | p.name | p.total |
+--------------+----------------+-----------------+------+--------+---------+
| 2 | Tom | 300 | | | |
| 4 | Bob | 500 | | | |
| 6 | Alice | 700 | | | |
+--------------+----------------+-----------------+------+--------+---------+
这就是我们需要的答案。
在PostgreSQL中，DISTINCT ON通常是最简单和最快的。（有关特定工作负载的性能优化，请参见下文。）
SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
或更短（如果不清楚），输出列的序号为：
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
如果总数可以为NULL，请添加NULLS LAST:
...
ORDER BY customer, total DESC NULLS LAST, id;
无论哪种方法都有效，但您需要匹配现有索引
db<>fiddle在这里
主要要点
DISTINCT ON是标准的PostgreSQL扩展，其中只定义了整个SELECT列表中的DISTINCT。
在DISTINCT ON子句中列出任意数量的表达式，组合的行值定义重复项。手册：
显然，如果两行至少在一列值。空值在以下情况下被视为相等比较
我的大胆强调。
DISTINCT ON可以与ORDER BY组合使用。ORDER BY中的前导表达式必须在DISTINCT ON中的表达式集合中，但您可以自由地重新排列这些表达式之间的顺序。实例您可以向ORDERBY添加其他表达式，以从每组对等对象中选择特定的行。或者，如手册所述：
DISTINCT ON表达式必须与最左边的ORDER BY匹配表达式。ORDER BY子句通常包含表达式，用于确定每个DISTINCT ON组。
我添加了id作为最后一个打破联系的项目：“从共享最高总数的每个组中选择id最小的行。”
为了以与确定每组第一个结果的排序顺序不一致的方式对结果进行排序，可以使用另一个ORDERBY.Example将上面的查询嵌套在外部查询中。
如果total可以为NULL，则很可能需要具有最大非空值的行。添加NULLS LAST，如图所示。参见：
按ASC列排序，但先按NULL值排序？
SELECT列表不受DISTINCT ON或ORDER by中表达式的任何约束：
您不必在DISTINCT ON或ORDER BY中包含任何表达式。您可以在SELECT列表中包含任何其他表达式。这对于替换复杂的子查询和聚合/窗口函数非常有用。
我使用Postgres 8.3–15版本进行了测试。但该功能至少从7.1版开始就存在了，所以基本上一直存在。
指数
上述查询的理想索引是一个多列索引，以匹配的顺序和匹配的排序顺序跨越所有三列：
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
可能过于专业化。但如果特定查询的读取性能至关重要，请使用它。如果查询中有DESC NULLS LAST，请在索引中使用相同的名称，以便排序顺序匹配，索引完全适用。
效率/性能优化
在为每个查询创建定制索引之前，权衡成本和收益。上述指数的潜力很大程度上取决于数据分布。
使用索引是因为它提供了预排序的数据。在Postgres 9.2或更高版本中，如果索引小于基础表，则查询也可以从仅索引扫描中受益。不过，必须对索引进行整体扫描。实例
对于每个客户很少的行（列客户中的基数很高），这是非常有效的。如果您仍然需要排序输出，则更是如此。随着每个客户的行数不断增加，这一优势也在缩小。理想情况下，您有足够的work_mem在RAM中处理相关的排序步骤，而不会溢出到磁盘。但通常将work_mem设置得过高会产生不利影响。对于异常大的查询，请考虑SET LOCAL。通过EXPLAIN ANALYZE了解您需要多少。在排序步骤中提到“磁盘：”表示需要更多：
Linux上PostgreSQL中的配置参数work_mem使用ORDERBY日期和文本优化简单查询
对于每个客户的许多行（列客户中的基数较低），“索引跳过扫描”或“松散索引扫描”将更有效。但直到Postgres 15都没有实现。多年来，以这样或那样的方式实施它的严肃工作一直在进行，但迄今为止尚未成功。看看这里和这里。目前，有更快的查询技术可以替代这一点。特别是如果您有一个单独的表，其中包含唯一的客户，这是典型的用例。但如果你没有：
SELECT DISTINCT比PostgreSQL中我的表上的预期速度慢优化GROUP BY查询以检索每个用户的最新行优化分组最大查询查询每行最后N个相关行
基准
请参见单独的答案。
这是我们如何通过使用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;
