MySQL进阶:窗口函数
什么是窗口函数
窗口函数也叫分析函数(处理相对复杂的报表统计分析场景):
- 什么叫做窗口?
窗口可以理解为记录集合。窗口函数用于符合某种条件的记录集合上执行的特殊函数。静态窗口:对于每条记录都要在此窗口内执行函数,窗口大小都是固定的;动态窗口:也成为滑动窗口,不同的记录对应不同的窗口。 - 窗口函数的基本用法如下:
函数名 ([expr]) over 子句
- over是关键字 ,用来指定函数执行的窗口范围,包含三个分析子句:
函数名([expr] over(partition by <要分类的列> order by <要排名的列> rows between <数据范围Data1> and <数据范围Data2>))
– partition by 分类子句
– order by 排名的子句
– rows窗口子句
如果over()括号里面为空,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算。
窗口函数–over()
sum(…A…) over(partiton by …B… order by …C… rows between …D1… and …D2…)
avg(…A…) over(partiton by …B… order by …C… rows between …D1… and …D2…)
A:需要筛选的列名(字段名称)
B:分类的字段名称
C:排序的字段名称
D:计算的行数范围
-- current 当前
-- unbounded 无限
-- preceding 向前
-- following 向后
rows between 2 preceding and current row; -- 取当前行和前两行
rows between unbounded preceding and current row; -- 取当前行和前面的所有行
rows between current and unbounded following; -- 取当行和之后的所有行
rows between unbounded preceding and current row;-- 取本行以及前三行
rows between 3 preceding and 1 following row;-- 取本行、前三行以及后一行,共5行
窗口函数–排序
窗口函数 | 例子 |
---|---|
row_number() | 行号,1、2、3… |
rank() | 排名,1、1、3… |
dense_rank() | 密集排名 1、1、2… |
ntile(n) | 分组排名 ,n=4,[1,1,1,2,2,3,3,4,4],函数将九行划分为四个组,则最终将在第一组中包含三行,而另外三组各包含两行。 |
CUME_DIST() | 它返回一组值中值的累积分布。它表示值小于或等于行的值除以总行数的行数;可用于求累计金额占总体金额的占比; |
SQL运行顺序
from-->where-->group by-->having-->select-->order by-->limit
limit的用法:
以下的两种方式均表示取2,3,4三条数据。
1.select * from test LIMIT 1,3;
当limit后面跟两个参数的时候,第一个数表示要跳过的数量,后一位表示要取的数量。
2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的的数量,offset表示要跳过的数量