涨薪必备！开窗函数了解一下

创建数据

创建数据

基于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;

sum求和

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

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

移动平均

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

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 2 preceding) as 'moving_avg_price'
from goodsTable;

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

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

select id,name,type,price,
avg(price) over (order by id rows 2 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;

Tips： 官方文档

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

## partition by

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

作者：石小秀

某知名大厂大数据分析师

悉尼大学IT硕士

网易云课堂资深讲师

前中国银行·悉尼分行数据分析从业人员