SQL怎么计算分组内的最大连续增长次数_使用变量或窗口函数-SQL-PHP中文网

最稳直接结论：优先用 ROW_NUMBER() 配合差值分组（即“间隙识别法”），把连续增长转化为“行号差值不变”，比用户变量更可靠，避免执行顺序不可控导致的错位问题。

sql怎么计算分组内的最大连续增长次数_使用变量或窗口函数

MySQL 8.0+ 用窗口函数实现连续增长计数最稳

直接结论：优先用 ROW_NUMBER() 配合差值分组（即“间隙识别法”），比用户变量更可靠，避免 MySQL 5.7 及以前因执行顺序不可控导致的错位问题。

核心思路是把“连续增长”转化为“连续的行号差值不变”：对原始数据按时间/序号排序后，给每行打一个全局行号 rn；再对满足“当前值 > 前一行值”的记录打一个增长序列号 grn；两者相减得到的 diff 相同，就属于同一段连续增长。

SELECT group_id, MAX(streak_len) AS max_consecutive_up
FROM (
  SELECT group_id,
         COUNT(*) AS streak_len,
         diff
  FROM (
    SELECT group_id,
           value,
           ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts) AS rn,
           ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts) 
             - ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY CASE WHEN value > LAG(value) OVER (PARTITION BY group_id ORDER BY ts) THEN ts END) AS diff
    FROM your_table
  ) t1
  WHERE value > LAG(value) OVER (PARTITION BY group_id ORDER BY ts)
  GROUP BY group_id, diff
) t2
GROUP BY group_id;

⚠️ 注意：LAG(value) 默认返回 NULL，首行比较会失效，需配合 CASE 或子查询过滤掉首行；实际中建议先用 CTE 提取“是否增长”布尔列，再处理，可读性更高。

MySQL 5.7 必须用变量时，顺序和初始化是生死线

变量方案本质是模拟游标逐行计算，但 MySQL 不保证 ORDER BY 在变量赋值前生效 —— 这是绝大多数人结果错乱的根源。

必须在同一个 SELECT 中完成排序、变量判断、变量更新，不能拆到子查询外
@prev_value 和 @streak 必须在 FROM 子句里显式初始化（如 (SELECT @prev_value := NULL, @streak := 0)）
所有变量引用必须出现在 ORDER BY 之后的字段列表中，且顺序不能颠倒：先判断，再更新

SELECT group_id, MAX(consecutive_cnt) AS max_consecutive_up
FROM (
  SELECT group_id,
         @streak := IF(@prev_group = group_id AND value > @prev_value, @streak + 1, 1) AS consecutive_cnt,
         @prev_value := value,
         @prev_group := group_id
  FROM your_table
  CROSS JOIN (SELECT @prev_value := NULL, @prev_group := '', @streak := 0) AS _init
  ORDER BY group_id, ts
) t
GROUP BY group_id;

这个写法在 5.7 单线程下通常有效，但一旦加了 JOIN 或复杂 WHERE，就可能崩。上线前务必用 EXPLAIN 确认执行计划没重排。

PostgreSQL / SQL Server 推荐用递归 CTE 或条件窗口聚合

这些数据库对窗口函数支持更严格，LAG() 行为稳定，但“连续段识别”仍建议走差值法，而不是嵌套 MAX(ROWS BETWEEN ...) —— 后者无法动态截断非连续点。

PostgreSQL 可简化为：

WITH ranked AS (
  SELECT group_id, value, ts,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts) AS rn,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts) 
           - ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts, 
               CASE WHEN value > LAG(value) OVER (PARTITION BY group_id ORDER BY ts) THEN ts END) AS diff
  FROM your_table
),
streaks AS (
  SELECT group_id, COUNT(*) AS len
  FROM ranked
  WHERE value > LAG(value) OVER (PARTITION BY group_id ORDER BY ts)
  GROUP BY group_id, diff
)
SELECT group_id, MAX(len) FROM streaks GROUP BY group_id;

SQL Server 用户注意：LAG() 在 OVER 中不能直接参与算术运算，需先用 CTE 提取 prev_value 列，再做差值。