1. 项目概述：从一次数据清洗的“坑”说起

最近在做一个数据报表项目，需要从一张订单明细表里，为每个客户找出其“最近一次下单时购买的最贵商品”。听起来是个很简单的需求，对吧？我一开始也是这么想的，不就是先按客户分组，再按时间倒序排，最后取价格最高的那条记录嘛。于是，我信心满满地写下了类似SELECT customer_id, MAX(price) FROM orders GROUP BY customer_id的查询，然后发现结果完全不对——它返回的是每个客户在所有订单里的最高价格，而不是“最近一次下单时”的最高价格。这个场景，就是MAX() KEEP (DENSE_RANK LAST ORDER BY ...)这个Oracle特有分析函数大显身手的地方。

这个函数的名字有点长，结构也有点特别，但它解决的问题非常精准：在分组内，先按照某个顺序进行排名，然后在这个排名结果里（比如第一名或最后一名），再对另一个字段进行聚合操作（如取最大值、最小值、求和等）。它完美地填补了标准SQL中GROUP BY与窗口函数OVER(PARTITION BY ... ORDER BY ...)之间的一个空白地带。对于数据分析师、后端开发或者任何需要处理复杂分组聚合逻辑的工程师来说，掌握这个函数能让你写出更简洁、更高效、意图更清晰的SQL，避免写多层嵌套子查询或者复杂的CASE WHEN逻辑。今天，我就结合自己踩过的坑和实际优化案例，把这个函数的里里外外给你讲透。

2. 函数核心语法与执行逻辑拆解

要理解这个函数，我们得先把它“拆开”来看。它的完整语法结构是这样的：

聚合函数(column2) KEEP (DENSE_RANK FIRST|LAST ORDER BY column1 [ASC|DESC], ...) [OVER (PARTITION BY column3, ...)]

别看它写在一行里，其实它的执行逻辑是分步骤的，我们可以把它想象成一个“流水线”：

2.1 逻辑执行步骤分解

第一步：划定“战场”（分区）如果使用了OVER (PARTITION BY ...)子句，那么数据会先被按照指定的列进行分组。如果没有OVER子句，那么通常是在一个GROUP BY查询的上下文中，整个结果集被视为一个分区，或者针对每个GROUP BY组单独计算。这是确定计算范围的基础。

第二步：确定“排名规则”（排序）在每一个分区内部，根据ORDER BY子句指定的列和排序方向（升序ASC或降序DESC）对所有行进行排序。这一步是关键，它决定了哪些行会被认定为“第一”或“最后”。

第三步：锁定“目标行”（KEEP）DENSE_RANK FIRST或DENSE_RANK LAST在这里起作用。注意，这里用的是DENSE_RANK的规则：

• FIRST：会“保留”排序后排名为1的所有行。如果有多行并列第一，它们会被全部保留。
• LAST：会“保留”排序后排名最后的所有行。同样，并列的最后几名也会被全部保留。 这一步并没有真的生成一个排名列，而是在逻辑上筛选出了一个行的子集。

第四步：实施“最终操作”（聚合）对上一步“保留”下来的那个行子集，应用指定的聚合函数（MAX,MIN,SUM,AVG,COUNT等），但对象是另一个字段column2。最终，每个分区只会输出一个聚合结果值。

2.2 一个核心类比：班主任选标兵

为了让你印象更深刻，我打个比方。假设你是一个班主任，班里有一次考试成绩表（score）和一次公益劳动评分（labor_score）。现在要评选“成绩最高的学生中，公益劳动分最高的人”作为学习标兵。

1. 划定战场：你的班级就是一个分区（PARTITION BY class_id）。
2. 排名规则：你按考试成绩从高到低排序（ORDER BY score DESC）。
3. 锁定目标：你找出考试成绩排名第一的学生（DENSE_RANK FIRST）。注意，如果第一名有并列，这几个学生都进入候选。
4. 最终操作：在这几个成绩第一的候选学生里，你再找出公益劳动分最高的那个（MAX(labor_score)）。

这个逻辑用标准SQL写可能要用子查询或者窗口函数嵌套，但用KEEP语法，一句就搞定了：

SELECT class_id, MAX(labor_score) KEEP (DENSE_RANK FIRST ORDER BY score DESC) AS标兵_劳动分 FROM student_scores GROUP BY class_id;

注意：这里有一个非常容易混淆的点！ORDER BY子句决定谁排第一（目标行筛选），而外层的聚合函数（如MAX）是对另一个字段进行操作。千万不要以为MAX(...) KEEP (... ORDER BY ...)中的ORDER BY是用来对MAX的字段排序的，那是完全错误的。

3. 实战场景深度解析与代码实现

理解了原理，我们来看看它到底能解决哪些实际开发中令人头疼的问题。我会用几个比文档更贴近业务的例子来说明。

3.1 场景一：获取每组内最新记录的相关属性

这是最经典的场景，也就是我开篇提到的那个“坑”。假设有订单变更历史表order_status_history：

需求：获取每个订单当前（最新）的状态，以及是谁操作的。错误做法（新手常犯）：

-- 这只能得到每个订单的最后操作时间，但取不到对应的OPERATOR SELECT ORDER_ID, MAX(UPDATE_TIME) FROM order_status_history GROUP BY ORDER_ID; -- 或者用复杂的子查询/自连接 SELECT a.* FROM order_status_history a INNER JOIN ( SELECT ORDER_ID, MAX(UPDATE_TIME) as max_time FROM order_status_history GROUP BY ORDER_ID ) b ON a.ORDER_ID = b.ORDER_ID AND a.UPDATE_TIME = b.max_time;

正确且优雅的做法：

SELECT ORDER_ID, MAX(STATUS) KEEP (DENSE_RANK LAST ORDER BY UPDATE_TIME) AS latest_status, MAX(OPERATOR) KEEP (DENSE_RANK LAST ORDER BY UPDATE_TIME) AS latest_operator FROM order_status_history GROUP BY ORDER_ID;

执行结果：

为什么这里用MAX？因为STATUS和OPERATOR是字符串，在并列最后一条的情况下（虽然时间戳通常唯一，但理论上可能），我们需要一个聚合函数来从多条记录中确定一个值。MAX会按字母序取最大值。如果业务上能确保排序字段（UPDATE_TIME）在分区内唯一，那么KEEP子句筛选出的行只有一条，此时用MAX,MIN甚至AVG结果都一样，但MAX/MIN是适用于字符、数字、日期的通用选择。

3.2 场景二：基于复杂条件的聚合计算

假设有销售表sales：

需求：计算每个销售员在其销量最高那个区域的总销售额。思路拆解：

1. 分区：按销售员（SALESMAN）。
2. 排序：按区域销售额总和降序排，找出销量最高的区域。这里需要先对区域分组求和。
3. 保留：保留排名第一的区域（可能并列）。
4. 聚合：对这些区域的原始销售记录进行求和。

这个需求用普通SQL写起来非常绕，可能需要用到CTE（公用表表达式）进行多层计算。但用KEEP函数结合窗口函数，可以相对清晰：

SELECT SALESMAN, SUM(SALES_AMOUNT) KEEP ( DENSE_RANK FIRST ORDER BY region_total_sales DESC ) AS sales_in_top_region FROM ( SELECT SALESMAN, REGION, SALES_AMOUNT, SUM(SALES_AMOUNT) OVER (PARTITION BY SALESMAN, REGION) AS region_total_sales FROM sales ) t GROUP BY SALESMAN;

说明：内层子查询通过SUM(SALES_AMOUNT) OVER (PARTITION BY SALESMAN, REGION)为每一行都附加了其所属销售员和区域的销售总额。外层查询中，KEEP (DENSE_RANK FIRST ORDER BY region_total_sales DESC)会为每个销售员（GROUP BY SALESMAN）筛选出region_total_sales最高的那些行（即其销量最高的区域的所有记录），然后对这些行的SALES_AMOUNT进行SUM。

3.3 场景三：与OVER()窗口函数结合实现行级计算

KEEP不仅可以和GROUP BY一起用，也可以和OVER (PARTITION BY ...)结合，为每一行返回一个基于复杂排名的聚合值，而不会像GROUP BY那样折叠行。

沿用上面的order_status_history表。需求：在每一行历史记录旁边，都显示该订单最新状态的变更时间。

SELECT ORDER_ID, STATUS, UPDATE_TIME, OPERATOR, MAX(UPDATE_TIME) KEEP (DENSE_RANK LAST ORDER BY UPDATE_TIME) OVER (PARTITION BY ORDER_ID) AS order_latest_update_time FROM order_status_history;

执行结果（片段）：

这样，我们就在保留所有历史明细的同时，轻松拿到了每行对应的最新时间戳，对于制作明细报表非常有用。

4. 常见误区、性能分析与替代方案

4.1 高频踩坑点实录

1. 混淆排序字段与聚合字段：这是最大的坑。务必记住，ORDER BY后面的字段是用于决定“第一”或“最后”的排名依据；聚合函数MAX(),MIN()等括号内的字段，才是最终被计算的值。它们是两个不同的字段。
2. 忽略并列排名的影响：当ORDER BY的字段值在分区内存在重复时，DENSE_RANK FIRST/LAST会保留所有并列的行。这时，外层的聚合函数（如MAX）的作用就是从这些并列行中选出一个值。如果你需要的是任意一条，这没问题；但如果业务逻辑要求必须唯一，你就需要确保排序字段组合在分区内是唯一的（例如使用时间戳+主键），或者考虑使用ROW_NUMBER()替代DENSE_RANK（但KEEP语法不支持ROW_NUMBER，需改用其他写法）。
3. 在GROUP BY中遗漏必要字段：当SELECT列表中包含了KEEP聚合函数，并且没有OVER()子句时，查询通常需要GROUP BY。你必须GROUP BY所有未包含在聚合函数中的列。否则会报ORA-00979错误。
4. 性能陷阱与大量数据：KEEP函数本身计算效率不错，因为它通常只需要一次排序和聚合。但是，如果PARTITION BY或GROUP BY的键值非常多，且数据量巨大，排序操作可能成为瓶颈。关键在于排序字段和分区字段上的索引是否有效。

4.2 性能优化建议

• 索引是关键：为OVER (PARTITION BY col1, col2 ORDER BY col3, col4)子句中的PARTITION BY和ORDER BY字段创建复合索引，可以极大提升性能，避免全表扫描和昂贵的排序操作。例如，对于场景一的查询，索引(order_id, update_time)会非常有效。
• 理解执行计划：使用EXPLAIN PLAN FOR查看SQL的执行计划。关注是否有WINDOW SORT操作，以及它是PGA内存排序还是产生了磁盘临时空间（TEMP）。如果出现磁盘排序，需要考虑调整PGA_AGGREGATE_TARGET或优化SQL。
• 与ROW_NUMBER()方案的对比：实现类似“取每组第一条”的需求，另一种常见写法是使用ROW_NUMBER()窗口函数加外层过滤。

  SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) as rn FROM order_status_history t ) WHERE rn = 1;

  对比分析：
  • 可读性：KEEP语法更声明式，意图一目了然（我要保留最后一条的某个值）。ROW_NUMBER方案更过程式。
  • 灵活性：ROW_NUMBER()方案可以轻松取出排在前N条的所有字段，而KEEP一次只能针对一个聚合字段。如果需要取出最新记录的所有列，ROW_NUMBER()更合适。
  • 性能：在Oracle中，两者性能通常相近，优化器都能进行很好的处理。但在只需要一个聚合值（如最新的状态）时，KEEP可能在理论上更优一点，因为它避免了物化所有列的子查询。但在实际中，差异微乎其微，索引设计才是决定性因素。

4.3 非Oracle数据库的替代方案

如果你在使用 MySQL、PostgreSQL、SQL Server 等数据库，它们没有KEEP语法。但我们可以用通用SQL实现相同逻辑。

需求（同场景一）：获取每个订单的最新状态和操作员。

PostgreSQL/MySQL 8.0/SQL Server (使用窗口函数)：

-- 使用 DISTINCT ON (PostgreSQL特有，非常简洁) SELECT DISTINCT ON (order_id) order_id, status, operator, update_time FROM order_status_history ORDER BY order_id, update_time DESC; -- 使用窗口函数 (通用) SELECT order_id, latest_status, latest_operator FROM ( SELECT order_id, status AS latest_status, operator AS latest_operator, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM order_status_history ) t WHERE rn = 1;

MySQL 5.7 等旧版本（使用子查询）：

SELECT a.order_id, a.status AS latest_status, a.operator AS latest_operator FROM order_status_history a INNER JOIN ( SELECT order_id, MAX(update_time) AS max_time FROM order_status_history GROUP BY order_id ) b ON a.order_id = b.order_id AND a.update_time = b.max_time; -- 注意：如果同一订单有完全相同的update_time，此方法会返回多行，需要额外处理。

5. 进阶技巧与总结

经过上面的剖析，相信你已经对MAX() KEEP (DENSE_RANK LAST ...)这个函数有了深刻的理解。最后，再分享几个我总结的进阶使用心得：

1. 组合使用多个KEEP聚合：你可以在一个SELECT列表中同时使用多个KEEP函数，从同一组排名行中提取不同字段的聚合值。例如，同时获取最新状态和最新操作员：MAX(status) KEEP (...), MAX(operator) KEEP (...)。只要它们的ORDER BY和PARTITION BY逻辑一致，Oracle优化器通常能智能地合并排序操作。

2. 处理NULL值排序：在ORDER BY子句中，NULL值的排序位置会影响FIRST/LAST的结果。默认情况下，ORDER BY ... ASC时NULL值在最后，DESC时在最前。你可以使用NULLS FIRST或NULLS LAST来明确控制，例如ORDER BY update_time DESC NULLS LAST确保最新的非NULL时间排在最前。

3. 不是万能的：虽然强大，但它并不能替代所有复杂分析。对于需要连续计算（如移动平均）、累计求和（running total）或者更复杂的窗口帧（RANGE BETWEEN ...）需求，标准的窗口函数（OVER()）仍然是更合适的选择。

4. 代码可维护性：在团队项目中，如果其他人不熟悉这个语法，可能会增加理解成本。在关键或复杂查询旁添加简要注释，说明其逻辑（例如：-- 取每个订单最新时间的状态），是一个好习惯。

说到底，这个函数是Oracle提供的一把精准的“手术刀”，专门用于解决“按A排序后，对B聚合”这类特定问题。当你下次在写SQL时，发现需要先分组排序、再从中聚合，并且这个逻辑用普通GROUP BY或子查询写起来很别扭时，不妨想想这把“手术刀”。它往往能让你写出更简洁、更易于数据库优化器理解的代码。从我个人的经验来看，在正确的场景下使用它，不仅代码更清爽，由于意图表达明确，后期排查数据问题时也更容易定位逻辑。