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()之前，先对分组内的行进行筛选和排序，划定一个更小的、特定的“目标行集”。

整个函数的结构可以分解为三个部分：

1. 外层聚合函数 (MAX/ MIN/ SUM/ AVG/ COUNT等)：这是最终要执行的操作。注意，它聚合的对象，仍然是原始列（比如amount）。
2. KEEP关键字：这是一个信号，表明接下来的子句将定义如何“保持”或“限定”参与聚合计算的行。
3. KEEP的内部 (DENSE_RANK FIRST/LAST ORDER BY ...)）：这是核心逻辑所在。
   • ORDER BY：定义分组内的排序规则。比如ORDER BY change_time DESC，就是按变更时间降序排，最新的排前面。
   • DENSE_RANK FIRST或DENSE_RANK LAST：
     • DENSE_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 的处理过程：

1. GROUP BY order_id将数据按订单分组。
2. 对于订单1001，ORDER BY change_time默认升序，排序后行顺序为：10-01, 10-02, 10-03, 10-03。
3. DENSE_RANK LAST会找出排名最后的行。由于change_time在 10-03 有两条记录，它们的DENSE_RANK值相同（假设为4），都属于“最后”的排名组。因此，被“保持”的行是这两条：(SHIPPED, 550)和(CONFIRMED, 540)。
4. 在这个被保持的行集{550, 540}里，执行MAX(amount)，得到结果550。

查询结果将会是：

这个结果完全符合需求：订单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_NUMBER

WITH 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是针对rn=1的多条记录 FROM ranked_logs WHERE rn = 1 GROUP BY order_id;

方案二：使用FIRST_VALUE配合DISTINCT

SELECT 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;

对比分析：

选择建议：

• 如果你的环境是 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 ASC：NULL会排在所有有效时间之后，因此DENSE_RANK LAST可能会包含这些NULL行。
• ORDER BY change_time DESC：NULL会排在最前面，因此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子句很强大，但在大数据量下也需要考虑性能。

1. 索引是王道：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)的复合索引会极大提升性能，因为数据库可以快速按订单分组并按时间排序。

2. 避免在KEEP内使用复杂表达式：ORDER BY后面的表达式如果太复杂（如函数计算、类型转换），可能会阻止索引的使用，导致全表扫描后排序，性能急剧下降。尽量使用单纯的列名。

3. 与WHERE子句配合：尽可能在WHERE子句中提前过滤掉不必要的数据，减少需要排序和分组的数据量。例如，只查询最近三个月的数据。

4. 理解执行计划：对于复杂的查询，使用EXPLAIN PLAN查看执行计划。关注是否有SORT (GROUP BY)或WINDOW (SORT)这类昂贵的操作。如果发现KEEP子句导致性能问题，可以尝试用等价的窗口函数写法进行对比测试，有时优化器对不同的写法会产生不同的执行计划。

5. 测试边界情况：务必测试排序字段为NULL、分组字段为NULL、以及“目标行集”为空（例如，某个分组下所有行的排序字段都是NULL，导致FIRST/LAST行集为空）的情况。聚合函数对空集的处理是返回NULL，确保你的应用程序能正确处理这个结果。

MAX() KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)不是一个每天都会用到的函数，但它是 Oracle SQL 武器库中一件精准的“手术刀”。当遇到那种需要先定位、再计算的聚合问题时，它能让你的代码变得异常简洁和富有表达力。下次再面对“找出每个XX里，在YY条件下，ZZ的最大/最小值”这类需求时，不妨先想想，是不是可以用这把“手术刀”优雅地解决。记住它的核心：先按规则排序并锁定目标行，再对目标行进行聚合。掌握了这个思维，很多复杂的查询问题都会迎刃而解。