扫码加入数据分析学习群

mysql> create table order_tab(order_id int,user_no varchar(3),amount int,create_date date);

mysql> insert into order_tab values

-> (1,'001',100,'2019-01-01'),

-> (2,'001',300,'2019-01-02'),

-> (3,'001',500,'2019-01-02'),

-> (4,'001',800,'2019-01-03'),

-> (5,'001',900,'2019-01-04'),

-> (6,'002',500,'2019-01-03'),

-> (7,'002',600,'2019-01-04'),

-> (8,'002',300,'2019-01-10'),

-> (9,'002',800,'2019-01-16'),

-> (10,'002',800,'2019-01-22');

mysql> select * from order_tab;

+----------+---------+--------+-------------+

| order_id | user_no | amount | create_date |

+----------+---------+--------+-------------+

| 1 | 001 | 100 | 2019-01-01 |

| 2 | 001 | 300 | 2019-01-02 |

| 3 | 001 | 500 | 2019-01-02 |

| 4 | 001 | 800 | 2019-01-03 |

| 5 | 001 | 900 | 2019-01-04 |

| 6 | 002 | 500 | 2019-01-03 |

| 7 | 002 | 600 | 2019-01-04 |

| 8 | 002 | 300 | 2019-01-10 |

| 9 | 002 | 800 | 2019-01-16 |

| 10 | 002 | 800 | 2019-01-22 |

+----------+---------+--------+-------------+

mysql> select *,sum(amount) over(partition by user_no) sum_amount

-> from order_tab;

+----------+---------+--------+-------------+------------+

| order_id | user_no | amount | create_date | sum_amount |

+----------+---------+--------+-------------+------------+

| 1 | 001 | 100 | 2019-01-01 | 2600 |

| 2 | 001 | 300 | 2019-01-02 | 2600 |

| 3 | 001 | 500 | 2019-01-02 | 2600 |

| 4 | 001 | 800 | 2019-01-03 | 2600 |

| 5 | 001 | 900 | 2019-01-04 | 2600 |

| 6 | 002 | 500 | 2019-01-03 | 3000 |

| 7 | 002 | 600 | 2019-01-04 | 3000 |

| 8 | 002 | 300 | 2019-01-10 | 3000 |

| 9 | 002 | 800 | 2019-01-16 | 3000 |

| 10 | 002 | 800 | 2019-01-22 | 3000 |

+----------+---------+--------+-------------+------------+

我们知道聚合函数对一组值执行计算并返回一个值，但有时候一组数据只返回一个结果值并不能满足需求。
示例：查询每个用户按时间顺序的累计订单金额

mysql> select *,sum(amount) over(partition by user_no order by create_date) sum_amount

-> from order_tab;

+----------+---------+--------+-------------+------------+

| order_id | user_no | amount | create_date | sum_amount |

+----------+---------+--------+-------------+------------+

| 1 | 001 | 100 | 2019-01-01 | 100 |

| 2 | 001 | 300 | 2019-01-02 | 900 |

| 3 | 001 | 500 | 2019-01-02 | 900 |

| 4 | 001 | 800 | 2019-01-03 | 1700 |

| 5 | 001 | 900 | 2019-01-04 | 2600 |

| 6 | 002 | 500 | 2019-01-03 | 500 |

| 7 | 002 | 600 | 2019-01-04 | 1100 |

| 8 | 002 | 300 | 2019-01-10 | 1400 |

| 9 | 002 | 800 | 2019-01-16 | 2200 |

| 10 | 002 | 800 | 2019-01-22 | 3000 |

————————————————

开窗函数分类

按照函数功能不同，MySQL支持的开窗函数分为如下几类：

序号函数：row_number() / rank() / dense_rank()

分布函数：percent_rank() / cume_dist()

前后函数：lag() / lead()

头尾函数：first_value() / last_value()

其他函数：nth_value() / nfile()

开窗函数使用

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由

over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。

order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。

frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

示例：比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口

mysql> mysql> select *,sum(amount) over(partition by user_no order by create_date desc rows between 1 preceding and 1 following) sum_amount

-> from order_tab;

+----------+---------+--------+-------------+------------+

| order_id | user_no | amount | create_date | sum_amount |

+----------+---------+--------+-------------+------------+

| 5 | 001 | 900 | 2019-01-04 | 1700 |

| 4 | 001 | 800 | 2019-01-03 | 2000 |

| 2 | 001 | 300 | 2019-01-02 | 1600 |

| 3 | 001 | 500 | 2019-01-02 | 900 |

| 1 | 001 | 100 | 2019-01-01 | 600 |

| 10 | 002 | 800 | 2019-01-22 | 1600 |

| 9 | 002 | 800 | 2019-01-16 | 1900 |

| 8 | 002 | 300 | 2019-01-10 | 1700 |

| 7 | 002 | 600 | 2019-01-04 | 1400 |

| 6 | 002 | 500 | 2019-01-03 | 1100 |

————————————————

对于滑动窗口的范围指定，有两种方式：基于行和基于范围。