文章目录
- 3.9 窗口函数
- 3.9.1 定义与作用
- 3.9.2 语法
- 01) 语法：
- 02) 滑动窗口
- 03) 窗口表初始情况
- 3.9.2 排序窗口
- 3.9.3 分布窗口
- 3.9.4 取前取后窗口
- 3.9.5 其他
- 3.9.6 滑动窗口
3.9 窗口函数
3.9.1 定义与作用
- 所谓窗口，就是在每一行数的在执行的时候，旁边都会有一个相应的窗口，窗口中的数据是原表的全部数据，可以通过指定分组形式和排序形式是窗口中的数据呈现不同的顺序（见下图）。
因此窗口函数可以抽象为当前表的另一个形式，既然是表，我们就可以想对普通表一样对窗口表进行操作，唯一不同的是窗口表中的分组为 partition by，排序仍为一样，order by - 窗口函数的作用主要体现在，即需要单行查询，又需要多行统计的时候，亦或者说是即需要行内相关，又需要行外相关的时候。
一般情况下，单行查询和多行统计（聚合）无法一起使用，因为单行查询的结果为1变1，记录数不变，而多行统计为多变1，记录变少无法融合。因此窗口函数的出现就是为了解决这种情况：
若有窗口的存在，当同时单行查询和多行统计时候，可以现在原表中进行单行查询，然后再在窗口表中进行多行统计，统计结果与单行查询结果合并成一个新的行，这样就实现了单行查询和多行统计的同时进行了。可以看出，每一行查询都需要对窗口函数进行查询。
3.9.2 语法
01) 语法：
select col1,
windows_func() over([partiton by ...] [ order by ...] [rows\ranges ... ])- windows_func()：为窗口函数，主要有排序函数，分布函数，取前取后等函数，还有聚合函数（多行统计函数）
- partition by：相当于group by，对进入窗口表进行分组。
- order by：对窗口表进行排序，和普通表一样
- rows\ranges：滑动窗口。
02) 滑动窗口
- 为多行统计指定一个区间，这个区间将根据单行查询的进行而进行移动，这就是窗口函数。
简单的例子：统计每三行的平均值，因此需要指定窗口为3，每次统计时，取当前行与相邻的两行进行计算，每查询完一行就向下移动，重新取当前行和相邻的两行进行计算，以此往复进行，这样就达到了滑动的作用。 - 滑动窗口由关键字：rows 或 ranges 指定
- rows [between … and …]：通过指定行数来定义窗口的大小
- current Row：表示当前行
- N preceding：前n行，包括当前行
- N following：后n行
- unbounded preceding：开头到当前行
- unbounded following：当前行到结尾
# 演示
select salary, avg(salary) over(rows between 1 preceding and 1 following); # 去每三行的平均值
select salary, sum(salary) over(rows 2 prceding ) # 取当前行与前两行的均值- ranges [between…and…]：通过指定值的范围来定义窗口的大小，需要在order by后面
- V precding\following：相差V
- 演示
# ：查询每个员工的工资，以及与他工资相差少于1000的员工的人数
select salary, count(*) over(order by salary range between 1000 preceding and 1000 following)
# 最近7天的值
range interval 7-1 day preceding
# 前后一天和当天的值
range between interval 1 day preceding and interval 1 day following03) 窗口表初始情况
- 注意一点：窗口表的初始情况由传入表决定，通过重新组队排序才成为我们需要的表。
- 我们知道，关键字的执行顺序为
from --> join on --> where --> groupby --> having --> select --> order by --> limit n
窗口函数在select中才被执行，执行前以及经过了where 和groupby 的操作，所以传入窗口函数是经过筛选后的表了，groupby不会对窗口表产生影响
3.9.2 排序窗口
- row_number():当前行在分组内的序号，就是简单的顺序
- dense_rank():不间断的组内排序，例如：A A B的排序结果为 1 1 2
- rand()：间断的组内排序，例如：A A B 的排序结果为 1 1 3
- rand()和dense_rank()是序号排序的序号，因此需要在窗口内指定排序的最对象，也就是order by，否则默认为1
row_number()不需要指定排序对象也可以获得序号
row_number()演示：
# 组内序号的使用
mysql> select salary, department_id, row_number() over(partition by department_id order by salary) as 组内序号 from employees limit 10;
+----------+---------------+----------+
| salary | department_id | 组内序号 |
+----------+---------------+----------+
| 7000.00 | NULL | 1 |
| 4400.00 | 10 | 1 |
| 6000.00 | 20 | 1 |
| 13000.00 | 20 | 2 |
#
mysql> select salary, department_id, row_number() over(partition by department_id ) as 组内序号 , row_number() over () as 组外序号 from employees limit 10;
+----------+---------------+----------+----------+
| salary | department_id | 组内序号 | 组外序号 |
+----------+---------------+----------+----------+
| 7000.00 | NULL | 1 | 1 |
| 4400.00 | 10 | 1 | 2 |
| 13000.00 | 20 | 1 | 3 |
| 6000.00 | 20 | 2 | 4 |dense_rank() ， rank() 演示：获得各个员工在该部门中的工资排名
select department_id, salary, dense_rank() over(partition by department_id order by salary) as 'dense_rank', rank() over(partition by department_id order by salary) as 'rank'
from employees;
#
+---------------+----------+------------+------+
| department_id | salary | dense_rank | rank |
+---------------+----------+------------+------+
| 50 | 2100.00 | 1 | 1 |
| 50 | 2200.00 | 2 | 2 |
| 50 | 2200.00 | 2 | 2 |
| 50 | 2400.00 | 3 | 4 |
| 50 | 2400.00 | 3 | 4 |
| 50 | 2500.00 | 4 | 6 | # 可以看序号方式是不一样的
| 50 | 2500.00 | 4 | 6 |
| 50 | 2500.00 | 4 | 6 |
| 50 | 2500.00 | 4 | 6 |3.9.3 分布窗口
- percent_rank()：累计百分比
- cume_dist()：累计分布函数值，即P(x<x_i)
- 详细区别看例子
获得各个员工的工资在该部门的累计百分比，和分布函数值
select department_id, salary,
percent_rank() over(partition by department_id order by salary) as 'percent_rank',
cume_dist() over(partition by department_id order by salary) as 'cume_dist'
from employees limit 20;
+---------------+----------+----------------------+----------------------+
| department_id | salary | percent_rank | cume_dist |
+---------------+----------+----------------------+----------------------+
| NULL | 7000.00 | 0 | 1 |
| 10 | 4400.00 | 0 | 1 | precent_rank()更像是归一化，得到的值是它的排序。
| 20 | 6000.00 | 0 | 0.5 | 假设比当前行值小的函数为n，总函数为N，则其返回的值为：
| 20 | 13000.00 | 1 | 1 | (n) / (N - 1)
| 30 | 2500.00 | 0 | 0.16666666666666666 |
| 30 | 2600.00 | 0.2 | 0.3333333333333333 | cume_dist()求概率分布值
| 30 | 2800.00 | 0.4 | 0.5 | 假设小于等于当前行值的行数为n，总行数为N，则其返回值为
| 30 | 2900.00 | 0.6 | 0.6666666666666666 | n / N
| 30 | 3100.00 | 0.8 | 0.8333333333333334 |
| 30 | 11000.00 | 1 | 1 |
| 40 | 6500.00 | 0 | 1 |
| 50 | 2100.00 | 0 | 0.022222222222222223 |
| 50 | 2200.00 | 0.022727272727272728 | 0.06666666666666667 |
| 50 | 2200.00 | 0.022727272727272728 | 0.06666666666666667 |
| 50 | 2400.00 | 0.06818181818181818 | 0.1111111111111111 |
| 50 | 2400.00 | 0.06818181818181818 | 0.1111111111111111 |
| 50 | 2500.00 | 0.11363636363636363 | 0.2222222222222222 |
| 50 | 2500.00 | 0.11363636363636363 | 0.2222222222222222 |
| 50 | 2500.00 | 0.11363636363636363 | 0.2222222222222222 |
| 50 | 2500.00 | 0.11363636363636363 | 0.2222222222222222 |
+---------------+----------+----------------------+----------------------+3.9.4 取前取后窗口
- lag(列名, [n, [default]])：取前n行的某一列的数，与当前行合并作为新的列。结果可能为（xi+n, xi)，n默认为1， default默认为null
- lead(列名, [n, [default]])：取后n行
- first_value(列名)：取当分组的第一行的某一列的值
- last_value(列名)：当前行
- nth_value(列名, n)：取截止当前行中的第n行
演示：其实这些都大同小异，不都演示了
# 查询员工工资，以及部门内工资离他最近的员工的工资
select department_id, salary,
lag(salary, 1, null) over(partition by department_id order by salary desc range between unbounded preceding and 0.001 preceding) as 'more',
lead(salary, 1, null) over(partition by department_id order by salary desc range between 0.001 following and unbounded following) as 'least'
from employees limit 10;
+---------------+----------+----------+---------+
| department_id | salary | more | least |
+---------------+----------+----------+---------+
| NULL | 7000.00 | NULL | NULL |
| 10 | 4400.00 | NULL | NULL |
| 20 | 13000.00 | NULL | 6000.00 |
| 20 | 6000.00 | 13000.00 | NULL |
| 30 | 11000.00 | NULL | 3100.00 |
| 30 | 3100.00 | 11000.00 | 2900.00 |
| 30 | 2900.00 | 3100.00 | 2800.00 |
| 30 | 2800.00 | 2900.00 | 2600.00 |
| 30 | 2600.00 | 2800.00 | 2500.00 |
| 30 | 2500.00 | 2600.00 | NULL |
+---------------+----------+----------+---------+3.9.5 其他
- ntile(n)：对分组内数据进行分桶，并返回对应桶的序号
# 将全部工资按顺序进行分桶，个数为10，对各个部门进行分桶，个数为5，并返回各个员工所在的桶
select employee_id, department_id, salary,
ntile(10) over(order by salary) as '组外桶',
ntile(5) over(partition by department_id order by salary) as '组内桶'
from employees
limit 10;
+-------------+---------------+----------+--------+--------+
| employee_id | department_id | salary | 组外桶 | 组内桶 |
+-------------+---------------+----------+--------+--------+
| 178 | NULL | 7000.00 | 6 | 1 |
| 200 | 10 | 4400.00 | 5 | 1 |
| 202 | 20 | 6000.00 | 5 | 1 |
| 201 | 20 | 13000.00 | 10 | 2 |
| 119 | 30 | 2500.00 | 1 | 1 |
| 118 | 30 | 2600.00 | 2 | 1 |
| 117 | 30 | 2800.00 | 2 | 2 |
| 116 | 30 | 2900.00 | 2 | 3 |
| 115 | 30 | 3100.00 | 3 | 4 |
| 114 | 30 | 11000.00 | 9 | 5 |
+-------------+---------------+----------+--------+--------+3.9.6 滑动窗口
- 查询与每五个相近工资的平均值
select salary,
avg(salary) over(order by salary rows between 2 preceding and 2 following) as 'avg'
from employees limit 10;
+---------+-------------+
| salary | avg |
+---------+-------------+
| 2100.00 | 2166.666667 |
| 2200.00 | 2225.000000 |
| 2200.00 | 2260.000000 |
| 2400.00 | 2340.000000 |
| 2400.00 | 2400.000000 |
| 2500.00 | 2460.000000 |
| 2500.00 | 2480.000000 |
| 2500.00 | 2500.000000 |
| 2500.00 | 2500.000000 |
| 2500.00 | 2520.000000 |
+---------+-------------+
10 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
