online analytical processing
https://www.bilibili.com/video/BV1954y1v7tZ
function (expression)
OVER (
PARTITION BY column
ORDER BY column ASC/DESC
ROWS […]
)
简单来说,窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数
3)语法如下:
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
OVER: 必须有, 其他非必要
PARTITION ( 分割) BY
ROWS: 默认值: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN:
unbounded preceding/ x preceding/ x following/current row
AND unbounded following/ c preceding/ c following / current row
function (expression)
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
练习:
https://www.windowfunctions.com/
例子:
https://zhuanlan.zhihu.com/p/92654574
专用窗口函数rank, dense_rank, row_number有什么区别呢?
它们的区别我举个例子,你们一下就能看懂:
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
得到结果:
3.聚合函数作为窗口函数
聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。
我们来看一下窗口函数是聚合函数时,会出来什么结果:
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表
得到结果: