row_number() over()：等值之间没有区别，等值对应相同的排名，序列号来自1到n连续。
rank() over()：相等值的排名相同，但如果有相等值，则序列号为。1到n不连续的。如果一开始就有两个人3名字，那么就没有第一个4名。
dense_rank() over()：对于相同的值，排名相同，但序列号来自。1到n连续的。如果两个人都在第一位，那么他们就是第一位的。2名称(仅假设1第二)人是第一个3个人。
ntile( n ) over()：可以被认为是将有序的数据集平均分配给指定的数字n的桶中,将存储桶编号分配给每一行，排序对应的编号为存储桶编号。如果不能平均分配，则会为具有较小存储桶的存储桶分配额外的行，并且每个存储桶中可以放置的最大数据条数各不相同。1。

实操范例：

1，首先创建一个表 students_grades：

-- 创建表
create table students_grades
(
student_id int,
student_name varchar(20),
course_name varchar(20),
grades decimal(10,2),
class_name varchar(10)
);

2，插入测试数据：

-- 插入测试数据
insert into students_grades values (1, zhangsan, Chinese, 99, class_one);
insert into students_grades values (1, zhangsan, English, 97, class_one);
insert into students_grades values (1, zhangsan, mathematics, 87, class_one);
insert into students_grades values (1, zhangsan, Physics, 99, class_one);
insert into students_grades values (1, zhangsan, Chemistry, 67, class_one);
insert into students_grades values (1, zhangsan, biology, 98, class_one);

insert into students_grades values (2, lisi, Chinese, 98, class_one);
insert into students_grades values (2, lisi, English, 98, class_one);
insert into students_grades values (2, lisi, mathematics, 97, class_one);
insert into students_grades values (2, lisi, Physics, 96, class_one);
insert into students_grades values (2, lisi, Chemistry, 99, class_one);
insert into students_grades values (2, lisi, biology, 87, class_one);

insert into students_grades values (3, wangwu, Chinese, 99, class_one);
insert into students_grades values (3, wangwu, English, 98, class_one);
insert into students_grades values (3, wangwu, mathematics, 90, class_one);
insert into students_grades values (3, wangwu, Physics, 98, class_one);
insert into students_grades values (3, wangwu, Chemistry, 99, class_one);
insert into students_grades values (3, wangwu, biology, 99, class_one);

insert into students_grades values (4, zhaoliu, Chinese, 97, class_one);
insert into students_grades values (4, zhaoliu, English, 97, class_one);
insert into students_grades values (4, zhaoliu, mathematics, 99, class_one);
insert into students_grades values (4, zhaoliu, Physics, 98, class_one);
insert into students_grades values (4, zhaoliu, Chemistry, 97, class_one);
insert into students_grades values (4, zhaoliu, biology, 98, class_one);

3查询语句如下：

select 
student_name, 
course_name, 
grades, 
row_number() over(partition by course_name order by grades desc) as rownumber,
rank() over(partition by course_name order by grades desc) as rank,
dense_rank() over(partition by course_name order by grades desc) as denserank,
ntile(3) over(partition by course_name order by grades desc) as ntile
from students_grades;

4查询结果如下：

5.聚合窗口函数：

(本节末尾提供测试数据)

1、sum(salary) over(partition by city)

--按城市分组的薪资合计：
select name, city, salary, sum(salary) over(partition by city) from t_person;

结果如下：

2、sum(salary) over(order by name)

--按名称排序，用于工资累计和：
select name, city, salary, sum(salary) over(order by name) from t_person;

结果如下：

3、sum(salary) over(partition by city order by name)

--根据城市分组，每个城市按名称排序，汇总工资(也可以要求每个人一年内的月工资累加)
select name, city, salary, sum(salary) over(partition by city order by name) 
from t_person;

结果如下：

每个人的工资是按月累计统计的：

create table person_salary(
id int not null primary key,
name varchar2(20),
s_month int,
salary decimal(10,2)
);
commit;

insert into person_salary(id,name,s_month,salary) values (1,name1,1,10000);
insert into person_salary(id,name,s_month,salary) values (2,name1,2,10000);
insert into person_salary(id,name,s_month,salary) values (3,name1,3,10000);
insert into person_salary(id,name,s_month,salary) values (4,name1,4,10000);
insert into person_salary(id,name,s_month,salary) values (5,name2,1,20000);
insert into person_salary(id,name,s_month,salary) values (6,name2,2,20000);
insert into person_salary(id,name,s_month,salary) values (7,name2,3,20000);
insert into person_salary(id,name,s_month,salary) values (8,name2,4,20000);
insert into person_salary(id,name,s_month,salary) values (9,name3,1,30000);
insert into person_salary(id,name,s_month,salary) values (10,name3,2,30000);
insert into person_salary(id,name,s_month,salary) values (11,name3,3,30000);
insert into person_salary(id,name,s_month,salary) values (12,name3,4,30000);
commit;

4，其他类似的sum()聚合函数的：

count() over(partition by ... order by ...)：分组后找出总数。
max() over(partition by ... order by ...)：分组后找出最大值。
min() over(partition by ... order by ...)：找出分组后的最小值。
avg() over(partition by ... order by ...)：分组后的平均值。

5、first_value(salary)和last_value(salary)

--first_value：是获取窗口中第一个值
--last_value：是获取窗口中最后一个值

--根据city分组后，取salary第一个值
select name, city, salary, first_value(salary) over(partition by city order by name) 
from t_person;
--根据city分组后，取salary最后一个值
select name, city, salary, last_value(salary) over(partition by city order by name) 
from t_person;

结果如下：

6、lead(salary,1,0)和lag(salary,2,9)

--lag(column, n, 0) over(partition by ... order by ...)：取出前n行数据，没有缺省值为0。　　
--lead(column, n, 0) over(partition by ... order by ...)：取出后n行数据，没有缺省值为0。

----根据city分区(组)、salary在柱子的顶端向前1行，如果不是，则为默认值。0
select name, city, salary, lead(salary,1,0) over(partition by city order by name) 
from t_person;
----根据city分区(组)、salary在柱子下面2行，如果不是，则为默认值。9
select name, city, salary, lag(salary,2,9) over(partition by city order by name) 
from t_person;

结果如下：

测试数据：

--创建数据表t_person
create table t_person (name varchar2(20),city varchar2(20),age int,salary int);

--插入测试数据
insert into t_person(name,city,age,salary)
values(tom,beijing,20,3000);
insert into t_person(name,city,age,salary)
values(tim,chengdu,21,4000);
insert into t_person(name,city,age,salary)
values(jim,beijing,22,3500);
insert into t_person(name,city,age,salary)
values(lily,london,21,2000);
insert into t_person(name,city,age,salary)
values(john,newyork,22,1000);
insert into t_person(name,city,age,salary)
values(yaoming,beijing,20,3000);
insert into t_person(name,city,age,salary)
values(swing,london,22,2000);
insert into t_person(name,city,age,salary)
values(guo,newyork,20,2800);
insert into t_person(name,city,age,salary)
values(yuqian,beijing,24,8000);
insert into t_person(name,city,age,salary)
values(ketty,london,25,8500);
insert into t_person(name,city,age,salary)
values(kitty,chengdu,25,3000);
insert into t_person(name,city,age,salary)
values(merry,beijing,23,3500);
insert into t_person(name,city,age,salary)
values(smith,chengdu,30,3000);
insert into t_person(name,city,age,salary)
values(bill,beijing,25,2000);
insert into t_person(name,city,age,salary)
values(jerry,newyork,24,3300);