SQL优化：按id和id_addl分组取amt绝对值最大行的code值

参考信息 #

需求说明 #

• 分组规则：按id和id_addl的字段组合划分分组范围
• 计算逻辑：每个分组内找到amt列绝对值最大的行，返回该行对应的code字段值
• 示例：前3行属于同一id+id_addl分组，组内amt绝对值最大值为43562，对应行的code值为CLP

原有SQL缺陷 #

原有实现存在3个明显的性能和逻辑问题：

1. 存在冗余CTE：max_code CTE仅做全量视图查询，无任何计算、过滤逻辑，属于多余的查询层，额外增加解析开销
2. 重复扫描数据源：子查询b和CTEm分别两次读取schema.viw视图，IO开销直接翻倍
3. 隐式连接风险：使用逗号做隐式内连接，当分组内存在多个amt绝对值等于最大值的行时，会产生无意义的笛卡尔积重复数据，且连接条件写在WHERE子句中可读性差

优化后SQL #

写法1：直接返回所有行+分组最大对应code（性能最优） #

不需要自连接，单次扫描视图即可完成计算，适合需要保留分组内所有行、同时附加最大值对应code的场景：

SELECT
  id,
  id_addl,
  amt,
  code,
  MAX(ABS(amt)) OVER(PARTITION BY id, id_addl) AS max_amt,
  FIRST_VALUE(code) OVER(
    PARTITION BY id, id_addl
    ORDER BY ABS(amt) DESC
  ) AS max_amt_code
FROM schema.viw;

注意：如果分组内存在多行amt绝对值并列最大的情况，该写法默认取排序后第一行的code值。

写法2：仅返回分组内绝对值最大的行 #

如果只需要保留每个分组里amt绝对值最大的行，用排序标记过滤即可，同样仅需单次扫描：

WITH sorted_res AS (
  SELECT
    *,
    MAX(ABS(amt)) OVER(PARTITION BY id, id_addl) AS max_amt,
    -- 若需要返回所有并列最大值的行，将ROW_NUMBER()替换为RANK()
    ROW_NUMBER() OVER(
      PARTITION BY id, id_addl
      ORDER BY ABS(amt) DESC
    ) AS sort_rn
  FROM schema.viw
)
SELECT
  id,
  id_addl,
  amt,
  code,
  max_amt,
  code AS max_amt_code
FROM sorted_res
WHERE sort_rn = 1;

优化收益 #

• 数据源扫描次数从2次降为1次，大表/复杂视图场景下性能提升可达50%以上
• 去掉了无意义的自连接逻辑，避免了隐式笛卡尔积产生的重复数据问题
• 逻辑链路更短，SQL可读性更高，数据库优化器可以更高效地生成执行计划

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