窗口函数（window functions）是一种对结果集进行计算，并将计算结果合并到结果集上返回多行的一类函数。MySQL8开始支持窗口函数，包括 RANK()、LAG() 和 NTILE() 等非聚合窗口函数。 以及部分聚合函数现在可以用作窗口函数（例如，SUM() 和 AVG()）。
使用窗口函数需在函数调用后使用over子句，over子句定义了窗口函数如何处理查询行即窗口规范。窗口规范可以在表达式中直接定义，可以使用在其他子句中定义的窗口规范的名称即命名窗口；
注意：不能使用窗口函数在update或delete语句中更新行，可在查询或子查询中用来选择行。
#over子句定义语法。 OVER ([window_name] [partition_clause] [order_clause] [frame_clause]) #window_name：查询中其他windows子句定义的命名窗口。如只含window_name则完全引用。如还包含partitioning, ordering,framing则会修改命名窗口(注意：OVER 子句只能向命名窗口添加属性，而不能修改它们。)； #partition_clause：由PARTITION BY子句指定如何分组；默认所有结果集一个分组；MySQL支持PARTITION BY跟列名或表达式； #order_clause：由ORDER BY子句指定分组中的行如何排序； #frame_clause：滑动窗口中使用。定义frame，即分区中的子集； #命名窗口定义语法（位于from子句之后，having和order by子句之间） WINDOW window_name AS (window_spec)[, window_name AS (window_spec)] window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]复制
- 简单的栗子
mysql> select class_id,student,subject,score from subject_score; +----------+---------+---------+-------+ | class_id | student | subject | score | +----------+---------+---------+-------+ | 8 | lee | english | 60 | | 8 | lee | math | 50 | | 8 | lee | chinese | 80 | | 8 | xiaole | english | 90 | | 8 | xiaole | math | 100 | | 8 | xiaole | chinese | 90 | | 8 | xiaoyun | english | 95 | | 8 | xiaoyun | math | 95 | | 8 | xiaoyun | chinese | 70 | +----------+---------+---------+-------+ 9 rows in set (0.00 sec) #不指定分组 mysql> select class_id,student,subject,score,row_number()over() from subject_score; +----------+---------+---------+-------+--------------------+ | class_id | student | subject | score | row_number()over() | +----------+---------+---------+-------+--------------------+ | 8 | lee | english | 60 | 1 | | 8 | lee | math | 50 | 2 | | 8 | lee | chinese | 80 | 3 | | 8 | xiaole | english | 90 | 4 | | 8 | xiaole | math | 100 | 5 | | 8 | xiaole | chinese | 90 | 6 | | 8 | xiaoyun | english | 95 | 7 | | 8 | xiaoyun | math | 95 | 8 | | 8 | xiaoyun | chinese | 70 | 9 | +----------+---------+---------+-------+--------------------+ 9 rows in set (0.00 sec) #指定分组 mysql> select class_id,student,subject,score,row_number()over(partition by subject) from subject_score; +----------+---------+---------+-------+----------------------------------------+ | class_id | student | subject | score | row_number()over(partition by subject) | +----------+---------+---------+-------+----------------------------------------+ | 8 | lee | chinese | 80 | 1 | | 8 | xiaole | chinese | 90 | 2 | | 8 | xiaoyun | chinese | 70 | 3 | | 8 | lee | english | 60 | 1 | | 8 | xiaole | english | 90 | 2 | | 8 | xiaoyun | english | 95 | 3 | | 8 | lee | math | 50 | 1 | | 8 | xiaole | math | 100 | 2 | | 8 | xiaoyun | math | 95 | 3 | +----------+---------+---------+-------+----------------------------------------+ 9 rows in set (0.00 sec) #指定分组和排序 mysql> select class_id,student,subject,score,row_number()over(partition by subject order by subject,score desc) as subject_order from subject_score; +----------+---------+---------+-------+---------------+ | class_id | student | subject | score | subject_order | +----------+---------+---------+-------+---------------+ | 8 | xiaole | chinese | 90 | 1 | | 8 | lee | chinese | 80 | 2 | | 8 | xiaoyun | chinese | 70 | 3 | | 8 | xiaoyun | english | 95 | 1 | | 8 | xiaole | english | 90 | 2 | | 8 | lee | english | 60 | 3 | | 8 | xiaole | math | 100 | 1 | | 8 | xiaoyun | math | 95 | 2 | | 8 | lee | math | 50 | 3 | +----------+---------+---------+-------+---------------+ 9 rows in set (0.00 sec) #使用命名窗口 mysql> select class_id,student,subject,score,row_number() over w from subject_score window w as(partition by subject ); +----------+---------+---------+-------+---------------------+ | class_id | student | subject | score | row_number() over w | +----------+---------+---------+-------+---------------------+ | 8 | lee | chinese | 80 | 1 | | 8 | xiaole | chinese | 90 | 2 | | 8 | xiaoyun | chinese | 70 | 3 | | 8 | lee | english | 60 | 1 | | 8 | xiaole | english | 90 | 2 | | 8 | xiaoyun | english | 95 | 3 | | 8 | lee | math | 50 | 1 | | 8 | xiaole | math | 100 | 2 | | 8 | xiaoyun | math | 95 | 3 | +----------+---------+---------+-------+---------------------+ 9 rows in set (0.00 sec) #使用并改写命名窗口 mysql> select class_id,student,subject,score,row_number() over(w order by subject,score desc) from subject_score window w as(partition by subject ); +----------+---------+---------+-------+--------------------------------------------------+ | class_id | student | subject | score | row_number() over(w order by subject,score desc) | +----------+---------+---------+-------+--------------------------------------------------+ | 8 | xiaole | chinese | 90 | 1 | | 8 | lee | chinese | 80 | 2 | | 8 | xiaoyun | chinese | 70 | 3 | | 8 | xiaoyun | english | 95 | 1 | | 8 | xiaole | english | 90 | 2 | | 8 | lee | english | 60 | 3 | | 8 | xiaole | math | 100 | 1 | | 8 | xiaoyun | math | 95 | 2 | | 8 | lee | math | 50 | 3 | +----------+---------+---------+-------+--------------------------------------------------+复制
- 部分聚合函数支持over子句用作窗口函数
#AVG 平均数 mysql> select class_id,student,subject,score,avg(score) over(partition by subject ) as subject_avg from subject_score; +----------+---------+---------+-------+-------------+ | class_id | student | subject | score | subject_avg | +----------+---------+---------+-------+-------------+ | 8 | lee | chinese | 80 | 80.0000 | | 8 | xiaole | chinese | 90 | 80.0000 | | 8 | xiaoyun | chinese | 70 | 80.0000 | | 8 | lee | english | 60 | 81.6667 | | 8 | xiaole | english | 90 | 81.6667 | | 8 | xiaoyun | english | 95 | 81.6667 | | 8 | lee | math | 50 | 81.6667 | | 8 | xiaole | math | 100 | 81.6667 | | 8 | xiaoyun | math | 95 | 81.6667 | +----------+---------+---------+-------+-------------+ 9 rows in set (0.00 sec) #BIT_AND() 按位与 #BIT_OR() 按位或 #BIT_XOR() 按位异或 #COUNT() mysql> select class_id,student,subject,score,count(score) over(partition by subject ) as subject_count from subject_score; +----------+---------+---------+-------+---------------+ | class_id | student | subject | score | subject_count | +----------+---------+---------+-------+---------------+ | 8 | lee | chinese | 80 | 3 | | 8 | xiaole | chinese | 90 | 3 | | 8 | xiaoyun | chinese | 70 | 3 | | 8 | lee | english | 60 | 3 | | 8 | xiaole | english | 90 | 3 | | 8 | xiaoyun | english | 95 | 3 | | 8 | lee | math | 50 | 3 | | 8 | xiaole | math | 100 | 3 | | 8 | xiaoyun | math | 95 | 3 | +----------+---------+---------+-------+---------------+ 9 rows in set (0.00 sec) #JSON_ARRAYAGG() 将结果集聚合为单个 JSON 数组，其元素由行组成。 mysql> select class_id,student,subject,score,JSON_ARRAYAGG(score) over(partition by subject ) as score_json from subject_score; +----------+---------+---------+-------+---------------+ | class_id | student | subject | score | score_json | +----------+---------+---------+-------+---------------+ | 8 | lee | chinese | 80 | [80, 90, 70] | | 8 | xiaole | chinese | 90 | [80, 90, 70] | | 8 | xiaoyun | chinese | 70 | [80, 90, 70] | | 8 | lee | english | 60 | [60, 90, 95] | | 8 | xiaole | english | 90 | [60, 90, 95] | | 8 | xiaoyun | english | 95 | [60, 90, 95] | | 8 | lee | math | 50 | [50, 100, 95] | | 8 | xiaole | math | 100 | [50, 100, 95] | | 8 | xiaoyun | math | 95 | [50, 100, 95] | +----------+---------+---------+-------+---------------+ 9 rows in set (0.01 sec) #JSON_OBJECTAGG() 将两个列名或表达式作为参数，第一个用作键，第二个用作值，并返回包含键值对的 JSON 对象。 mysql> select class_id,student,subject,score,JSON_OBJECTAGG(subject,score) over(partition by student ) as score_json from subject_score; +----------+---------+---------+-------+---------------------------------------------+ | class_id | student | subject | score | score_json | +----------+---------+---------+-------+---------------------------------------------+ | 8 | lee | english | 60 | {"math": 50, "chinese": 80, "english": 60} | | 8 | lee | math | 50 | {"math": 50, "chinese": 80, "english": 60} | | 8 | lee | chinese | 80 | {"math": 50, "chinese": 80, "english": 60} | | 8 | xiaole | english | 90 | {"math": 100, "chinese": 90, "english": 90} | | 8 | xiaole | math | 100 | {"math": 100, "chinese": 90, "english": 90} | | 8 | xiaole | chinese | 90 | {"math": 100, "chinese": 90, "english": 90} | | 8 | xiaoyun | english | 95 | {"math": 95, "chinese": 70, "english": 95} | | 8 | xiaoyun | math | 95 | {"math": 95, "chinese": 70, "english": 95} | | 8 | xiaoyun | chinese | 70 | {"math": 95, "chinese": 70, "english": 95} | +----------+---------+---------+-------+---------------------------------------------+ 9 rows in set (0.00 sec) #MAX() MIN() mysql> select class_id,student,subject,score,max(score) over(partition by subject ) as score_max,min(score) over(partition by subject ) as score_min from subject_score; +----------+---------+---------+-------+-----------+-----------+ | class_id | student | subject | score | score_max | score_min | +----------+---------+---------+-------+-----------+-----------+ | 8 | lee | chinese | 80 | 90 | 70 | | 8 | xiaole | chinese | 90 | 90 | 70 | | 8 | xiaoyun | chinese | 70 | 90 | 70 | | 8 | lee | english | 60 | 95 | 60 | | 8 | xiaole | english | 90 | 95 | 60 | | 8 | xiaoyun | english | 95 | 95 | 60 | | 8 | lee | math | 50 | 100 | 50 | | 8 | xiaole | math | 100 | 100 | 50 | | 8 | xiaoyun | math | 95 | 100 | 50 | +----------+---------+---------+-------+-----------+-----------+ 9 rows in set (0.00 sec) #STDDEV_POP(), STDDEV(), STD() 总体标准差 #STDDEV_SAMP() 样本标准差 mysql> select class_id,student,subject,score,STD(score) over(partition by subject ) as score_STD,STDDEV_SAMP(score) over(partition by subject ) as score_SAMP from subject_score; +----------+---------+---------+-------+--------------------+-------------------+ | class_id | student | subject | score | score_STD | score_SAMP | +----------+---------+---------+-------+--------------------+-------------------+ | 8 | lee | chinese | 80 | 8.16496580927726 | 10 | | 8 | xiaole | chinese | 90 | 8.16496580927726 | 10 | | 8 | xiaoyun | chinese | 70 | 8.16496580927726 | 10 | | 8 | lee | english | 60 | 15.45603082582617 | 18.92969448600091 | | 8 | xiaole | english | 90 | 15.45603082582617 | 18.92969448600091 | | 8 | xiaoyun | english | 95 | 15.45603082582617 | 18.92969448600091 | | 8 | lee | math | 50 | 22.484562605386735 | 27.53785273643051 | | 8 | xiaole | math | 100 | 22.484562605386735 | 27.53785273643051 | | 8 | xiaoyun | math | 95 | 22.484562605386735 | 27.53785273643051 | +----------+---------+---------+-------+--------------------+-------------------+ 9 rows in set (0.00 sec) #SUM() mysql> select class_id,student,subject,score,sum(score) over(partition by student ) as student_sum from subject_score; +----------+---------+---------+-------+-------------+ | class_id | student | subject | score | student_sum | +----------+---------+---------+-------+-------------+ | 8 | lee | english | 60 | 190 | | 8 | lee | math | 50 | 190 | | 8 | lee | chinese | 80 | 190 | | 8 | xiaole | english | 90 | 280 | | 8 | xiaole | math | 100 | 280 | | 8 | xiaole | chinese | 90 | 280 | | 8 | xiaoyun | english | 95 | 260 | | 8 | xiaoyun | math | 95 | 260 | | 8 | xiaoyun | chinese | 70 | 260 | +----------+---------+---------+-------+-------------+ 9 rows in set (0.00 sec) #VAR_POP(), VARIANCE() 总体标准方差 #VAR_SAMP() 样本方差 mysql> select class_id,student,subject,score,VAR_POP(score) over(partition by subject ) as score_POP,VAR_SAMP(score) over(partition by subject ) as score_SAMP from subject_score; +----------+---------+---------+-------+--------------------+--------------------+ | class_id | student | subject | score | score_POP | score_SAMP | +----------+---------+---------+-------+--------------------+--------------------+ | 8 | lee | chinese | 80 | 66.66666666666667 | 100 | | 8 | xiaole | chinese | 90 | 66.66666666666667 | 100 | | 8 | xiaoyun | chinese | 70 | 66.66666666666667 | 100 | | 8 | lee | english | 60 | 238.88888888888883 | 358.33333333333326 | | 8 | xiaole | english | 90 | 238.88888888888883 | 358.33333333333326 | | 8 | xiaoyun | english | 95 | 238.88888888888883 | 358.33333333333326 | | 8 | lee | math | 50 | 505.5555555555555 | 758.3333333333333 | | 8 | xiaole | math | 100 | 505.5555555555555 | 758.3333333333333 | | 8 | xiaoyun | math | 95 | 505.5555555555555 | 758.3333333333333 | +----------+---------+---------+-------+--------------------+--------------------+ 9 rows in set (0.00 sec)复制
- 非聚合窗口函数
注意：虽然某些窗口函数允许使用 null_treatment 子句指定计算结果时如何处理 NULL 值，但MySQL实际使用RESPECT NULLS（默认值）处理NULL值，即在计算结果时会考虑NULL值。
| Name | Description |
|---|---|
| CUME_DIST() | 累计分布，分区值小于等于当前值的百分比。需和order by 一起使用 |
| DENSE_RANK() | 当前行在其分区内的排名，不会产生不连续的秩数。 对等点被视为关系并获得相同的等级。需和order by 一起使用 |
| FIRST_VALUE() | Value of argument from first row of window frame |
| LAG() | 分区内在当前行前N 行的值。 如果没有这样的行，则返回值为默认值。通常用于计算行之间的差异 |
| LAST_VALUE() | Value of argument from last row of window frame |
| LEAD() | 分区内在当前行后N 行的值。如果没有这样的行，则返回值为默认值。通常用于计算行之间的差异 |
| NTH_VALUE() | Value of argument from N-th row of window frame |
| NTILE() | 将一个分区划分为 N 个组（桶），为分区中的每一行分配其桶号，并返回其分区内当前行的桶号。需和order by 一起使用 |
| PERCENT_RANK() | 返回小于当前行中的值的分区值的百分比，不包括最高值。 |
| RANK() | 返回当前行在其分区内的排名，有间隙。需和order by 一起使用。注意和DENSE_RANK的区别 |
| ROW_NUMBER() | 返回其分区内当前行的编号。 行数范围从 1 到分区行数。ORDER BY 影响行编号的顺序。 如果没有 ORDER BY，行编号是不确定的。 |
#CUME_DIST() 累计分布，分区值小于等于当前值的百分比。需和order by 一起使用 mysql> select student,subject,score,CUME_DIST() over w from subject_score window w as (partition by subject order by score desc); +---------+---------+-------+--------------------+ | student | subject | score | CUME_DIST() over w | +---------+---------+-------+--------------------+ | xiaole | chinese | 85 | 0.3333333333333333 | | lee | chinese | 80 | 0.6666666666666666 | | xiaoyun | chinese | 70 | 1 | | xiaoyun | english | 95 | 0.3333333333333333 | | xiaole | english | 90 | 0.6666666666666666 | | lee | english | 60 | 1 | | xiaole | math | 100 | 0.3333333333333333 | | xiaoyun | math | 75 | 0.6666666666666666 | | lee | math | 50 | 1 | +---------+---------+-------+--------------------+ 9 rows in set (0.00 sec) #PERCENT_RANK() 返回小于当前行中的值的分区值的百分比，不包括最高值。 mysql> select student,subject,score,PERCENT_RANK() over w from subject_score window w as (partition by subject order by score desc); +---------+---------+-------+-----------------------+ | student | subject | score | PERCENT_RANK() over w | +---------+---------+-------+-----------------------+ | xiaole | chinese | 85 | 0 | | lee | chinese | 80 | 0.5 | | xiaoyun | chinese | 70 | 1 | | xiaoyun | english | 95 | 0 | | xiaole | english | 90 | 0.5 | | lee | english | 60 | 1 | | xiaole | math | 100 | 0 | | xiaoyun | math | 100 | 0 | | lee | math | 50 | 1 | +---------+---------+-------+-----------------------+ 9 rows in set (0.00 sec) #DENSE_RANK() 当前行在其分区内的排名，不会产生不连续的秩数。 对等点被视为关系并获得相同的等级。需和order by 一起使用 mysql> select student,subject,score,DENSE_RANK() over w from subject_score window w as (partition by subject order by score desc); +---------+---------+-------+---------------------+ | student | subject | score | DENSE_RANK() over w | +---------+---------+-------+---------------------+ | xiaole | chinese | 85 | 1 | | lee | chinese | 80 | 2 | | xiaoyun | chinese | 70 | 3 | | xiaoyun | english | 95 | 1 | | xiaole | english | 90 | 2 | | lee | english | 60 | 3 | | xiaole | math | 100 | 1 | | xiaoyun | math | 100 | 1 | | lee | math | 50 | 2 | +---------+---------+-------+---------------------+ 9 rows in set (0.00 sec) #RANK() 返回当前行在其分区内的排名，有间隙。需和order by 一起使用。注意和DENSE_RANK的区别 mysql> select student,subject,score,RANK() over w from subject_score window w as (partition by subject order by score desc); +---------+---------+-------+---------------+ | student | subject | score | RANK() over w | +---------+---------+-------+---------------+ | xiaole | chinese | 85 | 1 | | lee | chinese | 80 | 2 | | xiaoyun | chinese | 70 | 3 | | xiaoyun | english | 95 | 1 | | xiaole | english | 90 | 2 | | lee | english | 60 | 3 | | xiaole | math | 100 | 1 | | xiaoyun | math | 100 | 1 | | lee | math | 50 | 3 | +---------+---------+-------+---------------+ 9 rows in set (0.00 sec) #lag() 分区内在当前行前N 行的值。 如果没有这样的行，则返回值为默认值。通常用于计算行之间的差异 mysql> select student,subject,score,score - lag(score,1,score) over w from subject_score window w as ( order by score ); +---------+---------+-------+-----------------------------------+ | student | subject | score | score - lag(score,1,score) over w | +---------+---------+-------+-----------------------------------+ | lee | math | 50 | 0 | | lee | english | 60 | 10 | | xiaoyun | chinese | 70 | 10 | | lee | chinese | 80 | 10 | | xiaole | chinese | 85 | 5 | | xiaole | english | 90 | 5 | | xiaoyun | english | 95 | 5 | | xiaole | math | 100 | 5 | | xiaoyun | math | 100 | 0 | +---------+---------+-------+-----------------------------------+ 9 rows in set (0.00 sec) #lead() 分区内在当前行后N 行的值。如果没有这样的行，则返回值为默认值。通常用于计算行之间的差异 mysql> select student,subject,score,lead(score,1,score) over w -score from subject_score window w as ( order by score ); +---------+---------+-------+-----------------------------------+ | student | subject | score | lead(score,1,score) over w -score | +---------+---------+-------+-----------------------------------+ | lee | math | 50 | 10 | | lee | english | 60 | 10 | | xiaoyun | chinese | 70 | 10 | | lee | chinese | 80 | 5 | | xiaole | chinese | 85 | 5 | | xiaole | english | 90 | 5 | | xiaoyun | english | 95 | 5 | | xiaole | math | 100 | 0 | | xiaoyun | math | 100 | 0 | +---------+---------+-------+-----------------------------------+ 9 rows in set (0.00 sec) #FIRST_VALUE() #LAST_VALUE() #NTH_VALUE() mysql> select student,subject,score,FIRST_VALUE(score) over w,LAST_VALUE(score) over w,NTH_VALUE(score,2) over w from subject_score window w as (order by score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); +---------+---------+-------+---------------------------+--------------------------+---------------------------+ | student | subject | score | FIRST_VALUE(score) over w | LAST_VALUE(score) over w | NTH_VALUE(score,2) over w | +---------+---------+-------+---------------------------+--------------------------+---------------------------+ | lee | math | 50 | 50 | 60 | 60 | | lee | english | 60 | 50 | 70 | 60 | | xiaoyun | chinese | 70 | 60 | 80 | 70 | | lee | chinese | 80 | 70 | 85 | 80 | | xiaole | chinese | 85 | 80 | 90 | 85 | | xiaole | english | 90 | 85 | 95 | 90 | | xiaoyun | english | 95 | 90 | 100 | 95 | | xiaole | math | 100 | 95 | 100 | 100 | | xiaoyun | math | 100 | 100 | 100 | 100 | +---------+---------+-------+---------------------------+--------------------------+---------------------------+ 9 rows in set (0.00 sec) #ROW_NUMBER() 返回其分区内当前行的编号。 行数范围从 1 到分区行数。 mysql> select student,subject,score,ROW_NUMBER() over w from subject_score window w as (partition by subject order by score desc); +---------+---------+-------+---------------------+ | student | subject | score | ROW_NUMBER() over w | +---------+---------+-------+---------------------+ | xiaole | chinese | 85 | 1 | | lee | chinese | 80 | 2 | | xiaoyun | chinese | 70 | 3 | | xiaoyun | english | 95 | 1 | | xiaole | english | 90 | 2 | | lee | english | 60 | 3 | | xiaole | math | 100 | 1 | | xiaoyun | math | 100 | 2 | | lee | math | 50 | 3 | +---------+---------+-------+---------------------+ 9 rows in set (0.00 sec) #NTILE() 将一个分区划分为 N 个组（桶），为分区中的每一行分配其桶号，并返回其分区内当前行的桶号。需和order by 一起使用 mysql> select student,subject,score,NTILE(4) over w from subject_score window w as (order by score desc); +---------+---------+-------+-----------------+ | student | subject | score | NTILE(4) over w | +---------+---------+-------+-----------------+ | xiaole | math | 100 | 1 | | xiaoyun | math | 100 | 1 | | xiaoyun | english | 95 | 1 | | xiaole | english | 90 | 2 | | xiaole | chinese | 85 | 2 | | lee | chinese | 80 | 3 | | xiaoyun | chinese | 70 | 3 | | lee | english | 60 | 4 | | lee | math | 50 | 4 | +---------+---------+-------+-----------------+ 9 rows in set (0.00 sec)复制
- 滑动窗口
可以使用frame子句定义frame，frame_units定义窗口范围基于ROW还是基于RANGE。frame_extent定义fame的起点和终点。
#frame子句 frame_clause: frame_units frame_extent frame_units:{ROWS | RANGE} #ROWS：fame由开始和结束行位置定义。 偏移量是行号与当前行号的差异。 #RANGE：fame由值范围内的行定义。 偏移量是行值与当前行值的差异。 frame_extent:{frame_start | frame_between} frame_between:BETWEEN frame_start AND frame_end #使用frame_between时 frame_start不能比frame_end大 frame_start, frame_end: { #frame_start 和 frame_end可以使用以下范围表达式 CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING } #CURRENT ROW 基于ROW 边界是当前行，基于Range 边界是当前行的对应值。一般和其他范围表达式一起使用 #UNBOUNDED PRECEDING 边界是分区第一行 #UNBOUNDED FOLLOWING 边界是分区最后一行 #expr PRECEDING 基于ROW 边界是当前行之前的expr行。 基于Range 边界是当前行值减去expr 的行。 #expr FOLLOWING 基于ROW 边界是当前行之后的expr行。 基于Range 边界是当前行值加上expr 的行。 expr在prepare statement中可以用?占位，可以是一个非负的数据 ，或者是一个时间间隔 INTERVAL val unit。 例如： 5 PRECEDING interval 5 year PRECEDING 5 FOLLOWING interval 5 year FOLLOWING 基于时间和数字表达式的Range 需要 时间和数字表达式上的order by复制
用作窗口函数的聚合函数和以下非聚合窗口函数支持滑动窗口
FIRST_VALUE() LAST_VALUE() NTH_VALUE() mysql> select student,subject,score,FIRST_VALUE(score) over w,LAST_VALUE(score) over w,NTH_VALUE(score,2) over w from subject_score window w as (partition by subject order by score rows UNBOUNDED PRECEDING); +---------+---------+-------+---------------------------+--------------------------+---------------------------+ | student | subject | score | FIRST_VALUE(score) over w | LAST_VALUE(score) over w | NTH_VALUE(score,2) over w | +---------+---------+-------+---------------------------+--------------------------+---------------------------+ | xiaoyun | chinese | 70 | 70 | 70 | NULL | | lee | chinese | 80 | 70 | 80 | 80 | | xiaole | chinese | 85 | 70 | 85 | 80 | | lee | english | 60 | 60 | 60 | NULL | | xiaole | english | 90 | 60 | 90 | 90 | | xiaoyun | english | 95 | 60 | 95 | 90 | | lee | math | 50 | 50 | 50 | NULL | | xiaole | math | 100 | 50 | 100 | 100 | | xiaoyun | math | 100 | 50 | 100 | 100 | +---------+---------+-------+---------------------------+--------------------------+---------------------------+ 9 rows in set (0.00 sec)复制
#创建一个简单的收入表，收入数量和时间。通过滑动窗口查询每一个时间点前2小时的总收入，平均收入，最高、最低单次收入。 mysql> select money,insert_time,sum(money) over w,avg(money) over w,max(money) over w,min(money) over w from t_test window w as (order by insert_time range between interval 2 hour preceding and current row ) ; +-------+---------------------+-------------------+-------------------+-------------------+-------------------+ | money | insert_time | sum(money) over w | avg(money) over w | max(money) over w | min(money) over w | +-------+---------------------+-------------------+-------------------+-------------------+-------------------+ | 1 | 2021-08-28 17:35:19 | 1 | 1.0000 | 1 | 1 | | 2 | 2021-08-28 17:45:19 | 3 | 1.5000 | 2 | 1 | | 3 | 2021-08-28 17:55:19 | 6 | 2.0000 | 3 | 1 | | 4 | 2021-08-28 18:05:19 | 10 | 2.5000 | 4 | 1 | | 6 | 2021-08-28 18:15:19 | 16 | 3.2000 | 6 | 1 | | 7 | 2021-08-28 18:25:19 | 23 | 3.8333 | 7 | 1 | | 8 | 2021-08-28 18:35:19 | 31 | 4.4286 | 8 | 1 | | 9 | 2021-08-28 18:45:19 | 40 | 5.0000 | 9 | 1 | | 11 | 2021-08-28 18:55:19 | 51 | 5.6667 | 11 | 1 | | 12 | 2021-08-28 19:05:19 | 63 | 6.3000 | 12 | 1 | | 13 | 2021-08-28 19:15:19 | 76 | 6.9091 | 13 | 1 | | 14 | 2021-08-28 19:25:19 | 90 | 7.5000 | 14 | 1 | | 15 | 2021-08-28 19:35:19 | 105 | 8.0769 | 15 | 1 | | 17 | 2021-08-28 19:45:19 | 121 | 9.3077 | 17 | 2 | | 18 | 2021-08-28 19:55:19 | 137 | 10.5385 | 18 | 3 | | 19 | 2021-08-28 20:05:19 | 153 | 11.7692 | 19 | 4 | | 20 | 2021-08-28 20:15:19 | 169 | 13.0000 | 20 | 6 | | 21 | 2021-08-28 20:25:19 | 184 | 14.1538 | 21 | 7 | | 22 | 2021-08-28 20:35:19 | 199 | 15.3077 | 22 | 8 | | 23 | 2021-08-28 20:45:19 | 214 | 16.4615 | 23 | 9 | | 24 | 2021-08-28 20:55:19 | 229 | 17.6154 | 24 | 11 | | 25 | 2021-08-28 21:05:19 | 243 | 18.6923 | 25 | 12 | | 26 | 2021-08-28 21:15:19 | 257 | 19.7692 | 26 | 13 | | 28 | 2021-08-28 21:25:19 | 272 | 20.9231 | 28 | 14 | | 29 | 2021-08-28 21:35:19 | 287 | 22.0769 | 29 | 15 | | 30 | 2021-08-28 21:45:19 | 302 | 23.2308 | 30 | 17 | | 31 | 2021-08-28 21:55:19 | 316 | 24.3077 | 31 | 18 | | 32 | 2021-08-28 22:05:19 | 330 | 25.3846 | 32 | 19 | | 33 | 2021-08-28 22:15:19 | 344 | 26.4615 | 33 | 20 | | 34 | 2021-08-28 22:25:19 | 358 | 27.5385 | 34 | 21 | | 35 | 2021-08-28 22:35:19 | 372 | 28.6154 | 35 | 22 | | 36 | 2021-08-28 22:45:19 | 386 | 29.6923 | 36 | 23 | | 37 | 2021-08-28 22:55:19 | 400 | 30.7692 | 37 | 24 | | 39 | 2021-08-28 23:05:19 | 415 | 31.9231 | 39 | 25 | | 40 | 2021-08-28 23:15:19 | 430 | 33.0769 | 40 | 26 | | 41 | 2021-08-28 23:25:19 | 445 | 34.2308 | 41 | 28 | | 42 | 2021-08-28 23:35:19 | 459 | 35.3077 | 42 | 29 | | 43 | 2021-08-28 23:45:19 | 473 | 36.3846 | 43 | 30 | | 44 | 2021-08-28 23:55:19 | 487 | 37.4615 | 44 | 31 | | 45 | 2021-08-29 00:05:19 | 501 | 38.5385 | 45 | 32 | …… …… | 200 | 2021-08-31 13:45:19 | 2522 | 194.0000 | 200 | 188 | | 201 | 2021-08-31 13:55:19 | 2535 | 195.0000 | 201 | 189 | | 202 | 2021-08-31 14:05:19 | 2548 | 196.0000 | 202 | 190 | | 203 | 2021-08-31 14:15:19 | 2561 | 197.0000 | 203 | 191 | | 204 | 2021-08-31 14:25:19 | 2574 | 198.0000 | 204 | 192 | | 205 | 2021-08-31 14:35:19 | 2587 | 199.0000 | 205 | 193 | | 206 | 2021-08-31 14:45:19 | 2600 | 200.0000 | 206 | 194 | | 208 | 2021-08-31 14:55:19 | 2614 | 201.0769 | 208 | 195 | | 209 | 2021-08-31 15:05:19 | 2628 | 202.1538 | 209 | 196 | | 210 | 2021-08-31 15:15:19 | 2642 | 203.2308 | 210 | 197 | | 211 | 2021-08-31 15:25:19 | 2656 | 204.3077 | 211 | 198 | | 212 | 2021-08-31 15:35:19 | 2670 | 205.3846 | 212 | 199 | | 999 | 2021-08-31 15:45:19 | 3470 | 266.9231 | 999 | 200 | +-------+---------------------+-------------------+-------------------+-------------------+-------------------+ 422 rows in set (0.09 sec)复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
