SQL如何统计分组中的极值变动_使用窗口函数对比-SQL-PHP中文网

max()仅返回分组极值而丢失行位置，无法识别变动时刻；row_number()等窗口函数保留行序，可结合lag()、累计聚合等检测“首次突破”等动态变化事件。

sql如何统计分组中的极值变动_使用窗口函数对比

为什么 `MAX()` 和 `ROW_NUMBER()` 在分组内找极值变动时行为不同

直接用 MAX() 聚合函数只能拿到每个分组的极值本身，但无法定位该极值出现在哪一行、何时发生变动。而统计“变动”意味着要识别极值首次出现的位置、前后值是否变化、甚至变动方向（比如从低到高突破历史最大值）。这时候必须依赖窗口函数的行序感知能力。

关键区别在于：MAX() 是标量聚合，丢失行上下文；ROW_NUMBER()、RANK()、LAG() 等窗口函数保留原始行结构，允许你逐行比较。

MAX(value) OVER (PARTITION BY group_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 可以计算「截止当前行的历史最大值」，但仍是值，不是变动事件
真正标记“首次达到新高”的逻辑需要结合 LAG() 或自连接判断前一行的最大值是否小于当前值
如果时间戳 ts 有重复，RANK() 比 ROW_NUMBER() 更稳妥，避免因排序不确定性漏掉并列极值

用 `LAG()` + 累计最大值检测“突破性变动”

典型场景：监控某商品每日销量，想标记出“单日销量首次超过历史最高值”的日期。这不是静态分组极值，而是随时间演进的动态突破点。

实操上分两步：先算累计最大值，再和上一行累计最大值比对。只有当当前累计最大值 ≠ 上一行累计最大值时，才表示发生了新高突破。

SELECT
  group_id,
  ts,
  value,
  MAX(value) OVER (PARTITION BY group_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cummax,
  LAG(MAX(value) OVER (PARTITION BY group_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) 
    OVER (PARTITION BY group_id ORDER BY ts) AS prev_cummax,
  CASE WHEN cummax != COALESCE(prev_cummax, -999999) THEN 1 ELSE 0 END AS is_new_high
FROM sales_log;

COALESCE(prev_cummax, -999999) 防止首行 LAG() 返回 NULL 导致整行判为 NULL
不能把 MAX() OVER 写两次——子查询或 CTE 更清晰，否则多数数据库会重复计算，影响性能
若需同时捕获“新低”，只需把 MAX 换成 MIN，条件改为 cummin != prev_cummin

用 `ROW_NUMBER()` 定位每组首个极值位置（含并列）

当你要找“每组中第一次出现最大值的记录”，且允许并列（如多个相同最大值，只取最早时间那条），ROW_NUMBER() 最直接；但如果要包含所有并列最大值，则该用 RANK() 或 DENSE_RANK()。

注意排序键必须明确：仅按 value DESC 排不够，必须加二级排序（如 ts ASC）来打破并列，否则结果不可控。

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY value DESC, ts ASC) AS rn
  FROM sales_log
) t
WHERE rn = 1;

如果业务要求“只要值最大就全选”，改用 RANK() OVER (PARTITION BY group_id ORDER BY value DESC)，然后 WHERE rk = 1
MySQL 8.0+、PostgreSQL、SQL Server 支持；SQLite 3.25+ 也支持，但旧版不支持窗口函数
Oracle 中 ROW_NUMBER() 的排序稳定性更好，但要注意 NVL() 替代 COALESCE()