Oracle中Max Over Partition By排除当前行的实现方法咨询

测试数据准备 #

先创建测试表并插入示例数据，覆盖多分组多ID、重复值及单ID分组的边界场景：

CREATE TABLE test_data (
    id NUMBER,
    "GROUP" NUMBER,
    value NUMBER
);

INSERT INTO test_data VALUES (1, 1, 10);
INSERT INTO test_data VALUES (2, 1, 20);
INSERT INTO test_data VALUES (3, 1, 30);
INSERT INTO test_data VALUES (4, 2, 5);
INSERT INTO test_data VALUES (5, 2, 15);
INSERT INTO test_data VALUES (6, 2, 5);
INSERT INTO test_data VALUES (7, 3, 100); -- 仅单个ID的分组

方法1：窗口函数+条件判断（推荐，高效） #

利用窗口函数在分组内过滤当前行，仅需扫描一次表即可完成计算，性能最优：

SELECT
    id,
    "GROUP",
    value,
    MAX(CASE WHEN id != td.id THEN value END) OVER(PARTITION BY "GROUP") AS group_max_exclude_current
FROM test_data td;

• 核心逻辑：在每个分组内，通过CASE语句排除当前行的value，再对剩余值取MAX
• 边界处理：若分组仅含一行，结果返回NULL（无其他行可计算），可通过NVL()函数替换为指定默认值（如NVL(..., 0)）

方法2：关联子查询（直观，适合小数据量） #

通过子查询针对每行单独计算对应分组排除当前ID后的最大值，逻辑直观但数据量大时性能较差：

SELECT
    td.id,
    td."GROUP",
    td.value,
    (SELECT MAX(value)
     FROM test_data
     WHERE "GROUP" = td."GROUP" AND id != td.id) AS group_max_exclude_current
FROM test_data td;

• 核心逻辑：每行执行一次子查询，筛选同分组且ID不等于当前行的数据后取最大值
• 边界处理：单ID分组同样返回NULL，可按需用NVL()调整返回结果

特殊场景说明 #

若分组内存在重复value，上述两种方法依然有效——因为是基于ID排除当前行，而非value，确保当前行的value无论是否重复都会被排除。

内容的提问来源于stack exchange，提问作者Rabers