正如 Headers 所示,我想选择与 GROUP BY
分组的每组行的第一行 .
具体来说,如果我有一个如下所示的 purchases
表:
SELECT * FROM purchases;
My Output:
id | customer | total
---+----------+------
1 | Joe | 5
2 | Sally | 3
3 | Joe | 2
4 | Sally | 1
我想查询每个 customer
所做的最大购买( total
)的 id
. 像这样的东西:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Expected Output:
FIRST(id) | customer | FIRST(total)
----------+----------+-------------
1 | Joe | 5
2 | Sally | 3
11 回答
在Oracle 9.2(不是最初的8i),SQL Server 2005,PostgreSQL 8.4,DB2,Firebird 3.0,Teradata,Sybase,Vertica上:
受任何数据库支持:
但是你需要添加逻辑来打破关系:
在 PostgreSQL 中,这通常是 simpler and faster (下面有更多的性能优化):
或者更短(如果不是很清楚)具有序数的输出列:
如果
total
可以为NULL(赢得't hurt either way, but you' ll想要匹配现有索引):重点
DISTINCT ON是该标准的PostgreSQL扩展(其中只定义了整个
SELECT
列表中的DISTINCT
) .列出
DISTINCT ON
子句中的任意数量的表达式,组合的行值定义重复项 . The manual:大胆强调我的 .
DISTINCT ON
可与 ORDER BY 结合使用 . 前导表达式必须以相同的顺序匹配前导DISTINCT ON
表达式 . 您可以向ORDER BY
添加其他表达式,以从每个对等组中选择一个特定行 . 我添加id
作为最后一项来打破关系:“从共享最高
total
的每个组中选择最小id
的行 . ”如果
total
可以为NULL,则很可能需要具有最大非空值的行 . 像演示一样添加NULLS LAST
. 细节:PostgreSQL sort by datetime asc, null first?
The SELECT list 不受任何方式的
DISTINCT ON
或ORDER BY
中的表达式约束 . (在上面的简单案例中不需要):您不必在
DISTINCT ON
或ORDER BY
中包含任何表达式 .您可以在
SELECT
列表中包含任何其他表达式 . 这有助于用子查询和聚合/窗口函数替换更复杂的查询 .我测试了Postgres版本8.3 - 11.但是至少从版本7.1开始,该功能已经存在,所以基本上总是如此 .
索引
上述查询的完美索引是multi-column index,它匹配匹配序列中的所有三列并具有匹配的排序顺序:
可能太专业了 . 但是,如果特定查询的读取性能至关重要,请使用它 . 如果查询中包含
DESC NULLS LAST
,请在索引中使用相同的内容,以便排序顺序匹配且索引适用 .有效性/性能优化
在为每个查询创建定制索引之前,权衡成本和收益 . 上述指数的潜力很大程度上取决于 data distribution .
使用索引是因为它提供了预先排序的数据 . 在Postgres 9.2或更高版本中,如果索引小于底层,则查询也可以从 index only scan 中受益表 . 但是,索引必须完整扫描 .
对于 few rows per customer (列
customer
中的高基数),这非常有效 . 如果你还需要分类输出,那就更是如此了 . 随着每个客户的行数越来越多,收益越来越大 .理想情况下,您有足够的work_mem来处理RAM中涉及的排序步骤而不会溢出到磁盘 . 但通常设置太高可能会产生不利影响 . 考虑
SET LOCAL
用于异常大的查询 . 通过EXPLAIN ANALYZE
查找您需要多少 . 在排序步骤中提到“磁盘:”表示需要更多:Configuration parameter work_mem in PostgreSQL on Linux
Optimize simple query using ORDER BY date and text
对于 many rows per customer (列
customer
中的低基数),loose index scan(又名"skip scan")会更高效,但是直到Postgres 11还没有实现 . (Postgres 12计划实现仅索引扫描 . 请参阅here和here . )现在,有 faster query techniques 替代这个 . 特别是如果您有一个单独的表来容纳唯一的客户,这是典型的用例 . 但如果你不这样做:
Optimize GROUP BY query to retrieve latest record per user
Optimize groupwise maximum query
Query last N related rows per row
基准
我在这里有一个简单的基准,现在已经过时了 . 我用detailed benchmark in this separate answer替换了它 .
由于存在SubQ,Erwin指出解决方案效率不高
我用这种方式(仅限postgresql):https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
然后你的例子应该按原样工作:
CAVEAT:忽略NULL行
编辑1 - 改为使用postgres扩展名
现在我用这种方式:http://pgxn.org/dist/first_last_agg/
要在ubuntu 14.04上安装:
这是一个postgres扩展,为您提供第一个和最后一个功能;显然比上述方式更快 .
编辑2 - 订购和过滤
如果使用聚合函数(如这些),则可以对结果进行排序,而无需已经订购数据:
所以等效的例子,有了排序,就像这样:
当然,您可以按照您认为适合的方式订购和过滤;它的语法非常强大 .
基准
使用Postgres 9.4 和 9.5 测试最有趣的候选人,在
purchases
和 10k distinct customer_id (每个客户平均20行)中使用 200k rows 的中间表 .对于Postgres 9.5,我有效地为86446个不同的客户进行了第二次测试 . 见下文(每个客户平均2.3行) .
设置
主表
我使用
serial
(下面添加PK约束)和整数customer_id
,因为这是一个更典型的设置 . 还添加了some_column
以弥补通常更多的列 .虚拟数据,PK,索引 - 一个典型的表也有一些死元组:
customer
表 - 用于高级查询在我的 second test for 9.5中,我使用相同的设置,但使用
random() * 100000
生成customer_id
,每个customer_id
只获得几行 .表购买的对象大小
生成this query .
查询
CTE中的
1. row_number(),(参见其他答案)
子查询中的
2. row_number()(我的优化)
3. DISTINCT ON(见其他答案)
4.带有LATERAL子查询的rCTE(见这里)
5.与LATERAL的客户表(见这里)
带有ORDER BY的
6. array_agg()(参见其他答案)
结果
使用
EXPLAIN ANALYZE
(以及所有选项关闭)的上述查询的执行时间,最好是5次运行 .所有查询都在
purchases2_3c_idx
上使用 Index Only Scan (以及其他步骤) . 其中一些只是针对较小的索引,其他更有效 .A. Postgres 9.4有200k行,每个customer_id约20
B.与Postgres 9.5相同
C.与B.相同,但每个customer_id有~2.3行
2011年的原始(过时)基准
我在PostgreSQL 9.1 上运行了三个测试,在一个包含65579行的实际表中,并且在所涉及的三列中的每一列上都有单列btree索引,并且执行了5次运行的最佳执行时间 .
将@OMGPonies'第一个查询( A )与above DISTINCT ON solution( B )进行比较:
WHERE customer BETWEEN x AND y
产生1000行 .WHERE customer = x
选择单个客户 .用另一个答案中描述的索引重复相同的测试
这是常见的greatest-n-per-group问题,已经过很好的测试和高度optimized solutions . 我个人更喜欢left join solution by Bill Karwin(original post with lots of other solutions) .
请注意,对于这个常见问题的一堆解决方案可以在大多数官方消息来源中找到令人惊讶的结果, MySQL manual !见Examples of Common Queries :: The Rows Holding the Group-wise Maximum of a Certain Column .
在Postgres中你可以像这样使用
array_agg
:这将为您提供每个客户最大的购买
id
.有些事情需要注意:
array_agg
是一个聚合函数,因此它适用于GROUP BY
.array_agg
允许您指定作用于自身的排序,因此它不会约束整个查询的结构 . 如果您需要执行与默认值不同的操作,还有关于如何对NULL进行排序的语法 .构建数组后,我们采用第一个元素 . (Postgres数组是1索引的,不是0索引的) .
您可以以类似的方式使用
array_agg
作为第三个输出列,但max(total)
更简单 .与
DISTINCT ON
不同,使用array_agg
可以保留GROUP BY
,以防出于其他原因 .如果要从聚合行集中选择任何(通过某些特定条件)行 .
如果除了
max/min
之外还想使用另一个(sum/avg
)聚合函数 . 所以你不能用DISTINCT ON
的线索您可以使用下一个子查询:
您可以使用一个限制将
amount = MAX( tf.amount )
替换为您想要的任何条件:此子查询不得返回不止一排但如果你想做这样的事情你可能正在寻找window functions
查询:
HOW DOES THAT WORK! (我去过那里)
我们希望确保每次购买的总数最高 .
Some Theoretical Stuff (如果您只想了解查询,请跳过此部分)
设Total为函数T(customer,id),返回给定名称和id的值为了证明给定的总数(T(customer,id))是最高的,我们必须证明我们要证明
要么
第一种方法需要我们获取我不喜欢的那个名字的所有记录 .
第二个将需要一个聪明的方式来说没有比这个更高的记录 .
Back to SQL
如果我们在名称上加入表,并且总数少于连接表:
我们确保所有具有相同用户总数较高的记录的记录加入:
这将有助于我们过滤每次购买的最高总额,而无需分组:
这就是我们需要的答案 .
被接受的OMG小马“由任何数据库支持”的解决方案在我的测试中具有良好的速度 .
在这里,我提供了一个相同的方法,但更完整,更干净的任何数据库解决方案 . 考虑关系(假设希望每个客户只获得一行,甚至每个客户的最大总数为多个记录),并且将为购买表中的实际匹配行选择其他购买字段(例如purchase_payment_id) .
任何数据库支持:
此查询相当快,尤其是在购买表上存在类似(客户,总计)的复合索引时 .
备注:
t1,t2是子查询别名,可以根据数据库删除它们 .
Caveat :2017年1月编辑时,MS-SQL和Oracle数据库中不支持
using (...)
子句 . 您必须自己将其扩展为例如on t2.id = purchase.id
等USING语法适用于SQLite,MySQL和PostgreSQL .很快的解决方案
如果表由id索引,那么非常快