SQL:窗口函数初步介绍

SQL版本：MicroSoft SQL Server 2019 Express （其实SQL练手的话，SQL 免费的Express版本就够了）

概念

定义：窗口函数，又叫OLAP(Online Anallytical Processing)函数，可对数据库数据进行实时分析处理。
功能：对表进行分组并排序，不同与Group By函数，窗口函数不减少原表的行数，即生成行数相同的表
语法：

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>);
-- over关键字用于指定函数的窗口范围，
-- partition by 用于对表分组，
-- order by子句用于对分组后的结果进行排序。

窗口函数位置可放入以下两类函数：
1.专用窗口函数：row_number()、rank()、dense_rank()等
2.聚合类型的窗口函数：sum(),avg(),count()等

表数据：

create table TEST1 (
	department varchar(11) not null,
	location varchar(11) not null,
	cost int not null
);

insert into test1 values('A','One',100);
insert into test1 values('A','Two',150);
insert into test1 values('A','Three',200);
insert into test1 values('B','One',50);
insert into test1 values('B','Two',100);
insert into test1 values('B','Three',150);
insert into test1 values('B','Four',150);
insert into test1 values('B','Five',200);

1.专用窗口函数

用法：
均用于分组排序（废话），生成一个新的列存放排序序号
区别：
row_number()：遇到相同的值时，根据顺序排序。
rank()：遇到相同的值，重复排序（即序号相同），总数不变
dense_rank()：遇到相同的值，重复排序（即序号相同），但总数减少（毕竟是dense)
多说无益，举个例子更加直观：

select *,
row_number() over(partition by department order by cost desc) row_number_result,
rank() over(partition by department order by cost desc) rank_result,
dense_rank() over(partition by department order by cost desc)  dense_rank_result
from test1;

结果如下（重点在红框内）

注意：专用窗口函数括号内不需要参数

2.聚合类函数

用法：
此时的聚合类函数将统计每个分组内截至当前行的聚合值
注意：
聚合类函数后面括号里面不能为空，需要指定聚合的列名。
多说无益，举个例子更加直观：
直接使用聚合类函数

select sum(cost) as sum_cost from test1 

使用窗口函数格式

select *,
sum(cost) over(partition by department order by cost desc) as current_sum_cost 
from test1 

注意事项

窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
专用窗口函数可省略partition，结果为只排序不分组：

select *,
row_number() over(order by cost) as rank
from test1

聚合类窗口函数可省略partition或order by,结果如下：
只排序不分组，累计聚合：

select *,
sum(cost) over(order by cost) as current_sum
from test1

只分组不排序，直接统计组内的聚合值

select *,
sum(cost) over(partition by department) as per_department_sum_cost 
from test1 

尚未弄清楚的点

聚合类函数的窗口函数：

重复值的聚合是直接计算全部重复值的聚合，如上图
直接计算：50+100+100 = 250，而缺乏50+100 = 150的值