文档地址
窗口函数分类
MySQL
从
8.0
版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而
窗口函数是将结果置于每一条数据记录中。
窗口函数可以分为
静态窗口函数
和
动态窗口函数
静态窗口函数的窗口大小是固定的,不会因为记录的不同而不同;
动态窗口函数的窗口大小会随着记录的不同而变化。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数,如下表:
语法结构:
窗口函数的语法结构是:
函数
OVER
(
[
PARTITION BY
字段名
ORDER BY
字段名
ASC
|
DESC
]
)
或者是:
函数
OVER
窗口名
…WINDOW
窗口名
AS
(
[
PARTITION BY
字段名
ORDER BY
字段名
ASC
|
DESC
]
)
OVER
关键字指定函数窗口的范围。
如果省略后面括号中的内容,则窗口会包含满足
WHERE
条件的所有记录,窗口函数会基于所
有满足
WHERE
条件的记录进行计算。
如果
OVER
关键字后面的括号不为空,则可以使用如下语法设置窗口。
窗口名:为窗口设置一个别名,用来标识窗口。
PARTITIONBY
子句:指定窗口函数按照哪些字段进行分组。分组后,窗口函数可以在每个分组中分别执行。
ORDERBY
子句:指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
FRAME
子句:为分区中的某个子集定义规则,可以用来作为滑动窗口使用。
1.序号函数
1.ROW_NUMBER()函数
ROW_NUMBER()
函数能够对数据中的序号进行顺序显示。
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC)AS row_num,
id,category_id,category,NAME,price,stock
FROM goods;
2.RANK()函数
使用
RANK()
函数能够对序号进行并列排序,并且会跳过重复的序号,比如序号为
1
、
1
、
3
。
注意: category_id 和 price 一样rou_num 序号才会一样
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id,category_id,category,NAME,price,stock
FROM goods;
3.DENSE_RANK()函数
DENSE_RANK()
函数对序号进行并列排序,并且不会跳过重复的序号,比如序号为
1
、
1
、
2
。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC)AS row_num,
id,category_id,category,NAME,price,stock
FROM goods;
举例:使用
DENSE_RANK()
函数获取
goods
数据表中类别为
“
女装
/
女士精品
”
的价格最高的 4
款商品信息。
注意:最高4款 和4个的区别,每款商品都可能含有多个
SELECT*
FROM(
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC)AS row_num,
id,category_id,category,NAME,price,stock
FROM goods)t
WHERE category_id =1 AND row_num <=3;
可以看到,使用
DENSE_RANK()
函数得出的行号为
1
、
2
、
2
、
3
,相同价格的商品序号相同,后面的商品序
号是连续的,并且没有跳过重复的序号。
2. 分布函数
1.PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
rank 排列的出的序号,rows窗口的行数
(rank - 1) / (rows - 1)
其中,rank的值为使用RANK()函数产生的序号,rows的值为当前窗口的总记录数。
写法一:
SELECT
RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
写法二:
相当于声明一个窗口
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC):
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);
(r-1)/(6-1):
(1-1)/(6-1),(2-1)/(6-1),(2-1)/(6-1),(4-1)/(6-1)........
2.CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
举例:查询goods数据表中小于或等于当前价格的比例。
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;
女装有6个,其中其中比百褶裙 小的或者等于的 只有百褶裙 一个 所以 1/6
3. 前后函数
1.LAG(expr,n)函数
LAG(expr,n)
函数返回当前行的前
n
行的
expr
的值。
SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)
举例:查询
goods
数据表中前一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (
SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
2.LEAD(expr,n)函数
LEAD(expr,n)
函数返回当前行的后
n
行的
expr
的值。
举例:查询
goods
数据表中后一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_price
FROM(
SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
4. 首尾函数
1.FIRST_VALUE(expr)函数
FIRST_VALUE(expr)
函数返回第一个
expr
的值。
举例:按照价格排序,查询第
1
个商品的价格信息。
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS
first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
2.LAST_VALUE(expr)函数
LAST_VALUE(expr)
函数返回最后一个
expr
的值。
举例:按照价格排序,查询最后一个商品的价格信息。
SELECT id, category, NAME, price, stock,
LAST_VALUE(price) OVER w AS last_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);
5. 其他函数
1.NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)
函数返回第
n
个
expr
的值。
举例:查询
goods
数据表中排名第
2
和第
3
的价格信息。
SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
NTH_VALUE(price,3) OVER w AS third_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);
2.NTILE(n)函数
NTILE(n)
函数将分区中的有序数据分为
n
个桶,记录桶编号。
举例:将
goods
表中的商品按照价格分为
3
组。
SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);
如果 4组 就是: 11,22.3,4
5组: 11,2,3,4,5
小 结
窗口函数的特点是可以分组,而且可以在分组内排序。另外,窗口函数不会因为 分组而减少原表中的行数,这对我们在原表数据的基础上进行统计和排序非常有用。