窗口函数&开窗函数

创建数据

基于navicat，mysql 8.0.12

#建立商品
create table goodsTable( 
id varchar(20) not null, 
name varchar(20),
type varchar(20),
price int)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO goodsTable 
(id, name,type,price)
VALUES
(1,'印章','办公用品','95'), 
(2,'圆珠笔','办公用品','100'), 
(3,'打孔器','办公用品','500'), 
(4,'叉子','厨房用品','880'), 
(5,'案板','厨房用品','700'), 
(1,'高压锅','厨房用品','6000'), 
(1,'菜刀','厨房用品','3000'), 
(1,'T恤','衣服','1000'), 
(1,'运动服','衣服','95'), 
(1,'JK','衣服','500'), 
(1,'Lolita','衣服','1000');

语法

窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。

窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数语法：其中[]中的内容可以省略

<窗口函数> over ([partition by <列清单>]
order by <排序用列清单>)

窗口函数大体可以分为以下两种：
1.能够作为窗口函数的聚合函数**（sum，avg，count，max，min）**
2.rank，dense_rank，row_number等专用窗口函数。

rank排序

例1

选出所有数据，并计算每种商品类别中按照价格高低进行排序

select id,name,type,price,
rank() over (partition by type order by price desc) as 'rank'
from goodsTable;

partition by 能够设定排序的对象范围，类似于group by语句，这里就是以product_type划分排序范围。

order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。

窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

例2

选出所有数据，并按照价格高低进行排序

select id,name,type,price,
rank() over (order by price desc) as 'rank'
from goodsTable;

Tips：partition by不是窗口函数所必须的

rank，dense_rank与row_number

select id,name,type,price,
rank() over (order by price desc) as 'rank',
dense_rank() over (order by price desc) as 'dense_rank',
row_number() over (order by price desc) as 'row_number'
from goodsTable;

Reference:
https://blog.youkuaiyun.com/weixin_43852674/article/details/100127209

sum求和

计算出每种商品卖了多少钱【计算每种商品的GMV】

select id,name,type,price,
sum(price) over (partition by type) as 'rank'
from goodsTable;

avg求平均

移动平均

计算出商品编号小于自己的商品的销售单价的合计值

select id,name,type,price,
avg(price) over (order by id) as 'avg_price'
from goodsTable;

类似于移动平均，按照id进行升序排列，算完一个id的平均值，再继续算两个id的平均值

select id,name,type,price,
avg(price) over (order by id) as 'id_avg_price',
avg(price) over (order by name) as 'name_avg_price',
avg(price) over (order by type) as 'type_avg_price'
from goodsTable;

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

指定最靠近的3行做为汇总对象

select id,name,type,price,
avg(price) over (order by id rows 3 preceding) as 'moving_avg_price'
from goodsTable;

• 第三个平均值：（95+6000+3000）/3 = 3031
• 第四个平均值：（6000+3000+1000）/3 = 3333
• 第五个平均值：（3000+1000+95）/3 = 1365

select id,name,type,price,
avg(price) over (order by id rows 3 preceding) as 'preceding2_avg_price',
avg(price) over (order by id 
rows between 1 preceding and 1 following) as 'preceding1_following1_avg_price'
from goodsTable;

rows 3 preceding:: 前3个数值的平均值

• 第三个平均值：（95+6000+3000）/3 = 3031

• 第四个平均值：（6000+3000+1000）/3 = 3333

• 第五个平均值：（3000+1000+95）/3 = 1365

• id = 2的第一个

• （500+1000+100）/3 = 533.3

• id = 3的第一个

• （1000+100+500）/3 = 533.3

• id = 4的第一个

• （100+500+880）/3 = 493.3

rows between 1 preceding and 1 following : 最近3个数值的平均值

• 第1个平均值：（95+6000）/2 = 3027.5

• 第2个平均值：（95+6000+3000）/3 = 3031.6

• 第3个平均值：（6000+3000+1000）/3 = 3333.3

• 第4个平均值：（3000+1000+95）/3 = 1365

• id = 2的第一个

• （1000+100+500）/3 = 533.3

• id = 3的第一个

• （100+500+880）/3 = 493.3

Tips:

select id,name,type,price,
-- 错误的
avg(price) over (order by id rows 2 following) as 'moving_avg_price'
from goodsTable;

select id,name,type,price,
avg(price) over (order by id desc rows 2 preceding) as 'moving_avg_price'
from goodsTable;

先按id倒序排列，再按照preceding的移动平均进行平均值计算。

官方文档

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

partition by

若是加上partition，就是按照某个字段维度进行分组，就没有了移动平均的效果

下边两个根据id进行分组，之后加不加order_by结果都是一样的。

select id,name,type,price,
avg(price) over (partition by id order by id) as 'avg_price'
from goodsTable;

select id,name,type,price,
avg(price) over (partition by id) as 'avg_price'
from goodsTable;

小结

rank() 排序函数里边不需要加expression
sum([expr]),avg([expr])聚合函数里边要写：根据哪个字段来进行聚合操作

两个order by

select id,name,type,price,
rank() over (order by id) as 'ranking'
from goodsTable;

order by 最后执行，因此最终是根据price来进行排序的

select id,name,type,price,
rank() over (order by id) as 'ranking'
from goodsTable
order by price;