mysql 窗口函数

按照表顺序，汇总第一行至当前行数值时，发现怎么写都不太好写，然后发现了窗口函数。 带窗口函数：

SELECT 
    turn,
    person_name,
    weight,
    SUM(weight) OVER (ORDER BY turn) AS total_weight
FROM Queue

-- SUM(weight) OVER (ORDER BY turn)` 会对每一行，计算从第一行到当前行（按 turn 顺序）的累计重量。

不带窗口函数：

SELECT 
    turn,
    person_name,
    weight,
    SUM(weight) AS total_weight
FROM Queue

-- `SUM(weight)` 会对整个表的所有行求和

常用的窗口函数还有那些呢？

1、累计/分组类

• SUM()
  累计求和
  SUM(weight) OVER (ORDER BY turn)
• AVG()
  累计平均值
  AVG(weight) OVER (ORDER BY turn)
• MIN()/MAX()
  累计最小值/最大值
  MAX(weight) OVER (ORDER BY turn)

2、排名类

• ROW_NUMBER()
  按顺序给每行编号
  ROW_NUMBER() OVER (ORDER BY turn)
• RANK()
  排名（有并列，跳号）
  RANK() OVER (ORDER BY score DESC)
• DENSE_RANK()
  排名（有并列，不跳号）
  DENSE_RANK() OVER (ORDER BY score DESC)

3、差值类 LEAD()/LAG()
获取下一行/上一行的值
LEAD(weight, 1) OVER (ORDER BY turn)
LAG(weight, 1) OVER (ORDER BY turn)