【随手记】MySQL窗口函数计算累加和

SELECT id, month, salary,
    SUM(salary) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_total
FROM employee;

1.当前行及其前2行累加求和

SUM(salary) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 是一个窗口函数，用于计算工资的累计总和。

让我们逐个解释这个窗口函数的不同部分：

• SUM(salary) 表示对 salary 列求和，即计算每个月的工资总和。
• OVER 关键字后面指定了窗口定义，用于定义计算的范围。
• (ORDER BY month) 指定了窗口按照 month 列进行排序，即按照月份的顺序进行计算。
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 指定了窗口的范围，从当前行往前的前两行到当前行的范围。也就是说，对于每个月份的行，窗口函数会计算该行和前两个月份的行的工资总和。

举个例子来说明这个窗口函数的计算过程：

假设有以下数据：

| id | month  | salary |
|----|--------|--------|
| 1  | 202001 | 1000   |
| 2  | 202002 | 1500   |
| 3  | 202003 | 2000   |
| 4  | 202004 | 1200   |
| 5  | 202005 | 1800   |

对于每个月份的行，窗口函数会计算当前行及其前两个月份的工资总和。

• 对于第一行（202001），窗口函数会计算 202001、201912 和 201911 三个月的工资总和（因为前两个月份不存在，所以只计算当前行的工资）。
• 对于第二行（202002），窗口函数会计算 202002、202001 和 201912 三个月的工资总和。
• 对于第三行（202003），窗口函数会计算 202003、202002 和 202001 三个月的工资总和。
• 对于其他行以此类推。

这样，通过窗口函数的计算，可以得到每个月的工资总和和最近3个月的工资总和。

2.当前行及其后2行累加求和

如果想计算当前行及其后两行的和，可以使用 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING。

例如，对于以下数据：

| id | month  | salary |
|----|--------|--------|
| 1  | 202001 | 1000   |
| 2  | 202002 | 1500   |
| 3  | 202003 | 2000   |
| 4  | 202004 | 1200   |
| 5  | 202005 | 1800   |

使用窗口函数 SUM(salary) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)，计算每个月份及其后两个月份的工资总和。

• 对于第一行（202001），窗口函数会计算 202001、202002 和 202004 三个月的工资总和（因为后两个月份不存在，所以只计算当前行的工资）。
• 对于第二行（202002），窗口函数会计算 202002、202003 和 202004 三个月的工资总和。
• 对于第三行（202003），窗口函数会计算 202003、202004 和 202005 三个月的工资总和。
• 对于其他行以此类推。

通过调整窗口定义，可以灵活地计算不同范围的工资总和。

前1行、当前行及其后1行累加求和

如果想计算当前行、前一行和后一行的和，可以使用 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING。

例如，对于以下数据：

| id | month  | salary |
|----|--------|--------|
| 1  | 202001 | 1000   |
| 2  | 202002 | 1500   |
| 3  | 202003 | 2000   |
| 4  | 202004 | 1200   |
| 5  | 202005 | 1800   |

使用窗口函数 SUM(salary) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)，计算每个月份、前一个月份和后一个月份的工资总和。

• 对于第一行（202001），窗口函数会计算 202001、202002 和 202003 三个月的工资总和（因为前一个月份不存在，所以只计算当前行和后一个月份的工资）。
• 对于第二行（202002），窗口函数会计算 202001、202002 和 202003 三个月的工资总和。
• 对于第三行（202003），窗口函数会计算 202002、202003 和 202004 三个月的工资总和。
• 对于其他行以此类推。

通过调整窗口定义，可以灵活地计算不同范围的工资总和。