Oracle SQL KEEP子句：精准处理分组内排序聚合的利器

1. 从一个看似简单的需求说起如何找到每个分组里“最后一条”记录的最大值在数据库开发中我们经常会遇到一些需要“钻牛角尖”的聚合查询。比如你手头有一张销售订单变更流水表记录了订单状态每次变化的详情。现在产品经理提了个需求“给我找出每个订单在它最后一次状态变更时对应的那个最高的金额是多少。”你眉头一皱感觉事情并不简单。这可不是简单的GROUP BY order_id, MAX(amount)因为MAX(amount)会找出这个订单历史上所有金额里的最大值而我们需要的是“最后一次状态变更发生的那条记录上金额字段的值”。如果最后一次变更的金额不是历史最高那结果就错了。或者另一个场景学生每次考试的成绩表你想找出每个学生最后一次考试中分数最高的那个科目是什么。这也不是MAX(score)而是先定位到“最后一次考试”这个子集再在这个子集里找最高分。面对这种“先排序再在排序结果的特定位置如第一或最后进行聚合”的需求很多开发者第一反应是写子查询或者窗口函数。比如用ROW_NUMBER()先给每个订单的状态变更时间倒序排个号取第一条再关联回去。代码会变得冗长且不易读。直到有一天你翻看 Oracle 的 SQL 参考手册或者偶然看到一段“老司机”的代码发现了这个语法MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time)初看之下MAX和KEEP这两个词组合在一起DENSE_RANK和LAST又掺和进来确实有点让人摸不着头脑。但一旦理解了它的运作机制你就会发现它简直是处理这类“条件聚合”问题的神器能让复杂的逻辑在一行内清晰表达。今天我们就来彻底拆解这个 Oracle 独有的、强大却常被忽略的分析函数MAX() KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)。我会结合具体的模拟数据一步步带你理解它的语法、原理、应用场景以及那些官方文档里没写的实战避坑点。2. 语法拆解KEEP子句到底在“保持”什么要理解这个函数我们必须打破对MAX()的传统认知。通常MAX(column)是在一个分组内对所有行的column值求最大值。而KEEP子句的作用是在应用MAX()之前先对分组内的行进行筛选和排序划定一个更小的、特定的“目标行集”。整个函数的结构可以分解为三个部分外层聚合函数 (MAX/ MIN/ SUM/ AVG/ COUNT等)这是最终要执行的操作。注意它聚合的对象仍然是原始列比如amount。KEEP关键字这是一个信号表明接下来的子句将定义如何“保持”或“限定”参与聚合计算的行。KEEP的内部 (DENSE_RANK FIRST/LAST ORDER BY ...)这是核心逻辑所在。ORDER BY定义分组内的排序规则。比如ORDER BY change_time DESC就是按变更时间降序排最新的排前面。DENSE_RANK FIRST或DENSE_RANK LASTDENSE_RANK是一个排名函数但它在这里不是用来生成一个排名列而是作为一种“排名规则”被引用。FIRST表示取按ORDER BY排序后排名为第一的所有行。LAST表示取按ORDER BY排序后排名为最后的所有行。为什么是DENSE_RANK而不是ROW_NUMBER或RANK这是语法固定搭配。DENSE_RANK在处理并列ties时排名是连续的例如 1,2,2,3。KEEP子句使用DENSE_RANK的规则来确定哪些行属于“第一”或“最后”的排名组。如果排序字段有重复值FIRST或LAST可能会对应多行这是一个关键点。所以MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time)的完整解读是在每一个分组内先按照change_time进行排序。然后找出所有排在最后一位LAST的行可能有多行如果时间相同。最后在这些被“保持”下来的行中计算amount字段的最大值。同理MIN(score) KEEP (DENSE_RANK FIRST ORDER BY exam_date DESC)的意思是在每个分组内按考试日期降序排最近的在前。取排名第一即最近一次考试的所有行在这些行里找最低分。为了更直观我们创建一个测试表并插入数据-- 创建订单变更流水表 CREATE TABLE order_change_log ( order_id NUMBER, change_time DATE, status VARCHAR2(20), amount NUMBER(10, 2) ); -- 插入测试数据 INSERT INTO order_change_log VALUES (1001, DATE 2023-10-01, CREATED, 500.00); INSERT INTO order_change_log VALUES (1001, DATE 2023-10-02, PAID, 500.00); -- 同订单第二次变更金额相同 INSERT INTO order_change_log VALUES (1001, DATE 2023-10-03, SHIPPED, 550.00); -- 第三次变更金额更高 INSERT INTO order_change_log VALUES (1001, DATE 2023-10-03, CONFIRMED, 540.00); -- 同一天另一次变更金额不同 INSERT INTO order_change_log VALUES (1002, DATE 2023-10-01, CREATED, 300.00); INSERT INTO order_change_log VALUES (1002, DATE 2023-10-05, CANCELLED, 300.00); -- 最后一次变更 COMMIT;3. 实战演练用KEEP解决开篇难题现在我们用实际查询来验证。需求回顾找出每个订单在它最后一次状态变更时对应的最高金额。3.1 基础查询理解LAST的行为SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) as last_change_max_amount, MAX(change_time) as last_change_time -- 辅助查看用于验证 FROM order_change_log GROUP BY order_id;我们来分析一下这个查询对订单 1001 的处理过程GROUP BY order_id将数据按订单分组。对于订单1001ORDER BY change_time默认升序排序后行顺序为10-01, 10-02, 10-03, 10-03。DENSE_RANK LAST会找出排名最后的行。由于change_time在 10-03 有两条记录它们的DENSE_RANK值相同假设为4都属于“最后”的排名组。因此被“保持”的行是这两条(SHIPPED, 550)和(CONFIRMED, 540)。在这个被保持的行集{550, 540}里执行MAX(amount)得到结果550。查询结果将会是ORDER_IDLAST_CHANGE_MAX_AMOUNTLAST_CHANGE_TIME1001550.002023-10-031002300.002023-10-05这个结果完全符合需求订单1001在最后一次变更10-03发生的多条记录中最大金额是550订单1002最后一次变更的金额是300。3.2 进阶思考如果我要的是“最后一次变更的金额”本身呢注意我们的需求是“最后一次变更时对应的最高金额”。这隐含了最后一次变更可能有多条记录。如果业务上明确“一次变更”只对应一条记录或者即使有多条我们也只想任意取一条的金额那么KEEP子句可能不是最直接的。你可以用FIRST_VALUE或LAST_VALUE窗口函数SELECT DISTINCT order_id, LAST_VALUE(amount) OVER (PARTITION BY order_id ORDER BY change_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_amount FROM order_change_log;但LAST_VALUE的默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW需要特别注意通常需要像上面那样扩展窗口到整个分区才能拿到最后的值。这反而不如KEEP子句意图清晰。所以KEEP的核心优势在于它明确地表达了“在排序后的某个特定排名组内进行聚合”这个两层逻辑语义非常清晰。3.3 复杂场景结合多个KEEP表达式KEEP子句可以和其他聚合字段一起使用实现更复杂的单次查询。例如我们想同时知道每个订单最后一次变更时的最高金额 (last_max_amount)每个订单第一次变更时的最低金额 (first_min_amount)SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) as last_max_amount, MIN(amount) KEEP (DENSE_RANK FIRST ORDER BY change_time) as first_min_amount, LISTAGG(status, , ) WITHIN GROUP (ORDER BY change_time) as status_flow -- 顺便列出状态流水 FROM order_change_log GROUP BY order_id;这个查询展示了KEEP子句的灵活性可以在一个SELECT列表中定义多个不同的“目标行集”进行不同的聚合运算。4. 深度原理KEEP与窗口函数的对比与选择很多同学会想到用窗口函数来实现类似功能。我们来对比一下用常见的窗口函数方法如何实现“最后一次变更的最高金额”方案一使用子查询与ROW_NUMBERWITH ranked_logs AS ( SELECT order_id, amount, change_time, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY change_time DESC) as rn FROM order_change_log ) SELECT order_id, MAX(amount) as last_change_max_amount -- 这里MAX是针对rn1的多条记录 FROM ranked_logs WHERE rn 1 GROUP BY order_id;方案二使用FIRST_VALUE配合DISTINCTSELECT DISTINCT order_id, FIRST_VALUE(amount) OVER (PARTITION BY order_id ORDER BY change_time DESC) as last_amount FROM order_change_log;注意这个查询直接返回最后一次变更的金额如果有多条返回排序第一条的金额而不是“最后一次变更记录中的最大值”。要得到最大值需要更复杂的嵌套。方案三使用KEEP子句SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) as last_change_max_amount FROM order_change_log GROUP BY order_id;对比分析特性KEEP子句窗口函数方案语法简洁性极高。一行聚合函数内直接表达完整逻辑意图清晰。较复杂。通常需要CTE或子查询OVER、PARTITION BY、ORDER BY分散在不同部分。逻辑直观性非常直观。“保持最后排序的行然后取最大”读起来就像业务逻辑。相对间接。需要理解“先排名再过滤再聚合”或“第一值”的窗口范围。处理并列数据天然支持。DENSE_RANK FIRST/LAST本身就处理了并列情况聚合函数如MAX再对并列行进行计算。需要小心处理。ROW_NUMBER()不产生并列WHERE rn1只取一条RANK()或DENSE_RANK()需要配合聚合或额外处理。性能通常较好。Oracle 对这类原生分析聚合有优化一次扫描即可计算。取决于写法。多层嵌套或使用DISTINCT可能影响性能。通用性Oracle 特有。这是最大的限制代码无法直接迁移到其他数据库如 MySQL, PostgreSQL。SQL 标准。窗口函数是 SQL:2003 标准的一部分绝大多数现代数据库都支持可移植性好。选择建议如果你的环境是 Oracle并且需求是“在排序后的特定排名组内做聚合”KEEP子句通常是首选。它写起来快读起来明白执行效率也高。如果你需要代码跨数据库移植或者团队对窗口函数更熟悉那么使用窗口函数方案是更安全的选择。对于“取第一条/最后一条记录的某个值”这种简单需求FIRST_VALUE/LAST_VALUE可能更直接。但对于“第一条/最后一条记录所在组内的聚合”需求KEEP的优势无可替代。5. 常见误区与避坑指南在实际使用中我踩过不少坑也见过很多同事误解这个函数的行为。下面总结几个关键点5.1 误区一ORDER BY排序方向的影响这是最容易出错的地方。LAST和FIRST是相对于ORDER BY排序结果而言的。ORDER BY change_time默认升序 (ASC)。最早的时间排第一 (FIRST)最晚的时间排最后 (LAST)。ORDER BY change_time DESC降序。最晚的时间排第一 (FIRST)最早的时间排最后 (LAST)。所以MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time DESC)的意思是按时间降序排最新的在前取排在最后面的行即最旧的行在这些最旧的行里找最大金额。这通常不是我们想要的。避坑法则在写KEEP子句时先在脑子里或纸上把ORDER BY子句的排序结果画出来明确哪头是FIRST哪头是LAST再结合业务需求选择。5.2 误区二NULL值在排序中的处理在 Oracle 中ORDER BY排序时NULL默认被视为最大值在ASC排序中排在最后在DESC排序中排在最前。这会影响KEEP的结果。假设change_time字段有NULL值ORDER BY change_time ASCNULL会排在所有有效时间之后因此DENSE_RANK LAST可能会包含这些NULL行。ORDER BY change_time DESCNULL会排在最前面因此DENSE_RANK FIRST可能会包含这些NULL行。如果你的业务逻辑不允许NULL参与计算必须在排序前处理掉它们。可以使用NVL、COALESCE赋予一个默认值或者在ORDER BY中使用NULLS FIRST/LAST明确控制但更常见的做法是在WHERE子句中过滤掉NULL。-- 错误的如果最后一条记录的change_time是NULL它会被包含在内 SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log GROUP BY order_id; -- 正确的先排除NULL值 SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log WHERE change_time IS NOT NULL -- 关键过滤 GROUP BY order_id;5.3 误区三与GROUP BY的配合KEEP子句是聚合函数的一部分它必须出现在SELECT、HAVING或ORDER BY子句中并且通常与GROUP BY一起使用。它不能独立于分组上下文。-- 错误缺少 GROUP BY整个表被视为一组但通常这不是我们想要的 SELECT MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log; -- 正确按订单分组为每个订单计算 SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log GROUP BY order_id;5.4 误区四对“并列行”聚合结果的理解这是KEEP子句的精髓也是容易困惑的点。当排序字段有重复值导致FIRST或LAST对应多行时外层的聚合函数MAX,MIN,SUM,AVG等是对这多行进行运算。-- 回顾我们的测试数据订单1001在2023-10-03有两条记录。 -- 这个查询会返回550因为它在 {550, 540} 中取MAX。 SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log WHERE order_id 1001 GROUP BY order_id; -- 如果我们用SUM呢它会返回 550 540 1090 SELECT order_id, SUM(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log WHERE order_id 1001 GROUP BY order_id; -- 如果我们用COUNT呢它会返回 2 SELECT order_id, COUNT(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log WHERE order_id 1001 GROUP BY order_id;关键点KEEP子句先定义了一个“行集”然后聚合函数在这个行集上工作。你需要想清楚当出现并列时业务上期望的聚合逻辑是什么是取最大/最小值还是求和或是计数6. 更多应用场景与变体理解了核心机制后这个函数的应用场景就非常广泛了。它本质上解决的是“基于某个排序规则对特定位置的子集进行聚合”的问题。场景一获取每个部门工资最高的员工中入职最早的那位的工资。这听起来有点绕分解一下先找每个部门最高工资可能多人再在这些“最高工资员工”里找入职最早的。SELECT department_id, MIN(hire_date) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) as earliest_hire_date_of_top_earner, MAX(salary) as top_salary -- 这个就是部门的最高工资 FROM employees GROUP BY department_id;这里DENSE_RANK FIRST ORDER BY salary DESC定义了“工资最高的人”这个行集然后MIN(hire_date)在这个行集里找最早的入职日期。场景二计算每个产品类别下销量排名前三的产品其平均售价。SELECT category_id, AVG(price) KEEP (DENSE_RANK FIRST ORDER BY sales_volume DESC) as avg_price_of_top1, -- 注意这里 FIRST ORDER BY DESC 取的是销量第一的行集 -- 如果要前三需要更复杂的逻辑KEEP子句本身不直接支持“前N”它只认FIRST/LAST。 -- 这个例子更适用于用窗口函数。这里只是展示FIRST的用法。 FROM products GROUP BY category_id;提示KEEP子句擅长处理“第一组”或“最后一组”对于“前N组”这种需求使用窗口函数ROW_NUMBER() N或NTILE()会更合适。场景三在数据清洗中保留每个用户最新一条非空邮箱记录。假设有用户联系历史表邮箱可能更新也可能为空。SELECT user_id, MAX(email) KEEP (DENSE_RANK LAST ORDER BY update_time) as latest_email FROM user_contact_history WHERE email IS NOT NULL -- 先过滤掉空邮箱确保参与排序和KEEP的都是有效记录 GROUP BY user_id;7. 性能考量与最佳实践虽然KEEP子句很强大但在大数据量下也需要考虑性能。索引是王道KEEP子句中的ORDER BY字段以及GROUP BY的字段是创建索引的重点考虑对象。例如对于SELECT order_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY change_time) FROM order_change_log GROUP BY order_id;一个(order_id, change_time)的复合索引会极大提升性能因为数据库可以快速按订单分组并按时间排序。避免在KEEP内使用复杂表达式ORDER BY后面的表达式如果太复杂如函数计算、类型转换可能会阻止索引的使用导致全表扫描后排序性能急剧下降。尽量使用单纯的列名。与WHERE子句配合尽可能在WHERE子句中提前过滤掉不必要的数据减少需要排序和分组的数据量。例如只查询最近三个月的数据。理解执行计划对于复杂的查询使用EXPLAIN PLAN查看执行计划。关注是否有SORT (GROUP BY)或WINDOW (SORT)这类昂贵的操作。如果发现KEEP子句导致性能问题可以尝试用等价的窗口函数写法进行对比测试有时优化器对不同的写法会产生不同的执行计划。测试边界情况务必测试排序字段为NULL、分组字段为NULL、以及“目标行集”为空例如某个分组下所有行的排序字段都是NULL导致FIRST/LAST行集为空的情况。聚合函数对空集的处理是返回NULL确保你的应用程序能正确处理这个结果。MAX() KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)不是一个每天都会用到的函数但它是 Oracle SQL 武器库中一件精准的“手术刀”。当遇到那种需要先定位、再计算的聚合问题时它能让你的代码变得异常简洁和富有表达力。下次再面对“找出每个XX里在YY条件下ZZ的最大/最小值”这类需求时不妨先想想是不是可以用这把“手术刀”优雅地解决。记住它的核心先按规则排序并锁定目标行再对目标行进行聚合。掌握了这个思维很多复杂的查询问题都会迎刃而解。