MySQL 窗口函数
1，窗口函数1.1，什么是窗口函数
MySQL窗口函数是一种强大的工具，用于在查询中实验复杂的统计分析，而不必要改变表的结构或数据。MySQL从8.0版本开始支持窗口函数，这些函数也被称为分析函数，由于它们能够处理惩罚相对复杂的报表统计分析场景。
窗口的意思是将数据进行分组，每个分组便是一个窗口，这和利用聚合函数时的group by分组类似，但与聚合函数不同的地方是： 聚合函数（比方：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。 窗口函数会对每一条数据进行盘算，并不会使返回的数据变少（每一行返回一个结果）
1.2，基本语法
-- 匿名窗口
SELECT
<窗口函数> over (partition by <分组列名> order by <排序列名>)
FROM `表名`
-- 显式窗口
SELECT
<窗口函数> OVER w
FROM `表名`
WINDOW w AS (partition by <分组列名> order by <排序列名>)
<窗口函数>的位置，可以放以下两种函数：
[*]聚合函数：如SUM、AVG、COUNT、MAX、MIN等，可以在不合并行的情况下盘算每行的聚合值。
[*]专用窗口函数：
[*]排序函数：包括RANK、DENSE_RANK、ROW_NUMBER等，用于为数据集中的每行分配一个唯一的排名或编号。
[*]偏移函数：包括LAG和LEAD等，用于获取当前行之前的或之后的指定偏移量的值
[*]值函数：FIRST_VALUE和LAST_VALUE返回窗口分区中第一行或最后一行的值，而NTH_VALUE则返回窗口内偏移指定offset后的值。
由于窗口函数是对where或者group by子句处理惩罚后的结果进行操作，所以窗口函数一样平常出现在select子句或者order by子句中。
where, group by, having都不可引用该列，由于这些语句实验在select之前，此时函数尚未盘算出值。
2，函数详解
原始数据如下，表名：class
https://img-blog.csdnimg.cn/direct/56c42661827c40f393e00d395b3a359d.png#pic_center
2.1，聚合函数
窗口操作不会将多组查询行折叠成单个输出行。相反，它们为每一行产生一个结果：
SELECT
*,
-- 总计
SUM(score) OVER () AS sum1,
-- 按course分组求和
SUM(score) OVER (PARTITION BY course) AS sum2,
-- 按course分组累计求和
SUM(score) OVER (PARTITION BY course ORDER BY score DESC) AS sum3
FROM `class`
https://img-blog.csdnimg.cn/direct/684bf1a315a946c4a349e3b51a7dad6f.png#pic_center
SELECT
*,
SUM(score) OVER w AS sum,
AVG(score) OVER w AS avg,
MIN(score) OVER w AS min,
MAX(score) OVER w AS max,
COUNT(score) OVER w AS count
FROM `class`
WINDOW w AS (PARTITION BY course ORDER BY score DESC)
https://img-blog.csdnimg.cn/direct/51e8a84847ee4b2fb10ba6482263c54b.png#pic_center
留意分数雷同时，分组累计（标黄处）的处理惩罚逻辑（见：《3，进阶用法》）
2.2，排序函数
SELECT
*,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM `class`
WINDOW w AS (PARTITION BY course ORDER BY score DESC)
https://img-blog.csdnimg.cn/direct/e65815cd93454c2bbcc2480bd1a85e87.png#pic_center
三者的区别如下：
row_number() 排序雷同时不会重复，会根据顺序排序，即：1、2、3、4；
rank() 排序雷同时会重复，序号有空隙，即1、2、2、4这样的排序结果；
dense_rank() 排序雷同时会重复，序号无空隙，即1、2、2、3这样的排序结果；
求每门课程的前两名：
SELECT * FROM (
SELECT
*,
RANK() OVER (PARTITION BY course ORDER BY score DESC) AS `rank`
FROM `class` ) f
WHERE `rank` <= 2
// 窗口函数得到的列别名不能用于where, group by, having等子句，
// 因为这些语句执行在select之前，此时函数尚未计算出值。
// 以下写法是错误的：
SELECT
*,
RANK() OVER (PARTITION BY course ORDER BY score DESC) AS `rank`
FROM `class`
WHERE `rank` <= 2
https://img-blog.csdnimg.cn/direct/c804bac28a164a47b30604b7326f7bec.png#pic_center
如果每门课程只必要前两条数据，可把RANK() 函数换成 ROW_NUMBER()
2.3，偏移函数
语法：LEAD(字段, 偏移量, 填充值)
偏移量默认为1，填充值默认为NULL
SELECT
*,
-- 获取前面一行的score
LAG(score) OVER W AS `lag`,
-- 获取后面第二行score，且无数据填充0
LEAD(score, 2, 0) OVER W AS `lead`
FROM `class`
WINDOW w AS (PARTITION BY course ORDER BY score DESC)
https://img-blog.csdnimg.cn/direct/002cc2f278074b08811ef4875f02dd58.png#pic_center
2.4，值函数
SELECT
*,
-- 获取第一行的score
FIRST_VALUE(score) OVER w AS `first`,
-- 截止到当前行，获取最后一行score
LAST_VALUE(score) OVER w AS `last`,
-- 截止到当前行，获取最后2行score
NTH_VALUE(score, 2) OVER w AS `second`,
-- 截止到当前行，获取最后3行score
NTH_VALUE(score, 3) OVER w AS `third`
FROM `class`
WINDOW w AS (PARTITION BY course ORDER BY score DESC)
https://img-blog.csdnimg.cn/direct/5cc06c51a5b64c37ad2e5aa04505ba47.png#pic_center
留意了：从结果看，我们对FIRST_VALUE()很清楚，就是获取的第一个值，但是LAST_VALUE()和NTH_VALUE获取的值跟我们想象中的不太一样呢？ 没错，LAST_VALUE()和NTH_VALUE是获取的截止到当前为止的值，而不是整个组的最后一个值后指定的值（见：《3，进阶用法》）。
3，进阶用法
<窗口函数> over (
partition by <用于分组的列名>
order by <用于排序的列名>
rows/range 窗口子句
)
rows/range：窗口子句，主要用来限定分组（也称窗口）的行数和数据范围。
窗口子句必须和order by 子句同时利用，如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从当前分组出发点到当前行。
行比力分析函数lead和lag无窗口子句。
窗口子句常用语法：
[*]CURRENT ROW：当前行
[*]UNBOUNDED：无界限（出发点或终点）
[*]PRECEDING：往前
[*]FOLLOWING：以后
如上文《2.4，值函数》，如果想获取整个窗口的LAST_VALUE()和NTH_VALUE：
SELECT
*,
-- 获取第一行的score
FIRST_VALUE(score) OVER w AS `first`,
-- 获取最后一行score
LAST_VALUE(score) OVER w AS `last`,
-- 获取最后2行score
NTH_VALUE(score, 2) OVER w AS `second`,
-- 获取最后3行score
NTH_VALUE(score, 3) OVER w AS `third`
FROM `class`
WINDOW w AS (
PARTITION BY course
ORDER BY score DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
https://img-blog.csdnimg.cn/direct/708c02fdaf524781a260912080cfe8a3.png#pic_center
rows 和range区别：
[*]rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据盘算（与当前行的值无关，只与排序后的行号干系）。
[*]range是逻辑窗口，即根据order by 子句排序后，取的前N行及和当前行有雷同order by值的所有行数据盘算。
比方在《2.1，聚合函数》飘黄部分，由于默认窗口字句是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，所以改成把"RANGE"改成"ROWS"就是逐条统计：
SELECT
*,
-- 默认RANGE
SUM(score) OVER w AS sum1,
-- 指定ROWS
SUM(score) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum2,
-- 默认RANGE
COUNT(score) OVER w AS count1,
-- 指定ROWS
COUNT(score) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count2
FROM `class`
WINDOW w AS (PARTITION BY course ORDER BY score DESC)
https://img-blog.csdnimg.cn/direct/455198c19241484c97c8f6e58f5fd3c0.png#pic_center
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]