MySQL8.0新特性—窗口函数
一、概述
窗口函数(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)
2.1 不指定分组
mysql> select class_id,
-> student, subject, score,
-> row_number() over() row_num
-> from subject_score;
+----------+---------+---------+-------+---------+
| class_id | student | subject | score | row_num |
+----------+---------+---------+-------+---------+
| 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)
2.2 指定分组
mysql> select
-> class_id, student, subject, score,
-> row_number() over( partition by subject ) as row_num_par_sub
-> from subject_score;
+----------+---------+---------+-------+-----------------+
| class_id | student | subject | score | row_num_par_sub |
+----------+---------+---------+-------+-----------------+
| 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)
2.3 指定分组和排序
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)
2.4 使用命名窗口
mysql> select class_id,
-> student, subject, score,
-> row_number() over w as row_num_w
-> from subject_score
-> window w as(partition by subject);
+----------+---------+---------+-------+-----------+
| class_id | student | subject | score | row_num_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)
2.5 使用并改写命名窗口
mysql> select class_id,
-> student, subject, score,
-> row_number() over(w order by subject, score desc) as row_num_ss
-> from subject_score
-> window w as(partition by subject);
+----------+---------+---------+-------+------------+
| class_id | student | subject | score | row_num_ss |
+----------+---------+---------+-------+------------+
| 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子句用作窗口函数
3.1 统计
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)
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)
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)
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)
3.2 BIT
BIT_AND() 按位与
BIT_OR() 按位或
BIT_XOR() 按位异或
3.3 JSON
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)
3.4 STD和VAR
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)
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():取分区或结果集中的第N行获取值。如果第N行不存在,则函数返回NULL。N必须是正整数,例如1,2和3。
mysql> select student,
-> subject, score,
-> first_value(score) over w as sorce_first,
-> last_value(score) over w as sorce_last,
-> nth_value(score, 2) over w as sorce_nth
-> from subject_score
-> window w as (order by score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
+---------+---------+-------+-------------+------------+-----------+
| student | subject | score | sorce_first | sorce_last | sorce_nth |
+---------+---------+-------+-------------+------------+-----------+
| 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
定义frame
的起点和终点。
#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 score_first,
-> last_value(score) over w score_last,
-> nth_value(score, 2) over w score_nth
-> from subject_score
-> window w as (partition by subject order by score rows UNBOUNDED PRECEDING);
+---------+---------+-------+-------------+------------+-----------+
| student | subject | score | score_first | score_last | score_nth |
+---------+---------+-------+-------------+------------+-----------+
| 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 as money_sum,
-> avg(money) over w as money_avg,
-> max(money) over w as money_max,
-> min(money) over w as money_min
-> from t_test
-> window w as (order by insert_time range between interval 2 hour preceding and current row );
+-------+---------------------+-----------+-----------+-----------+-----------+
| money | insert_time | money_sum | money_avg | money_max | money_min |
+-------+---------------------+-----------+-----------+-----------+-----------+
| 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 |
| 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)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器