oracle的学习笔记：开窗函数（一）

开窗函数

排序函数

使用排序函数把数据按照一定规则分组，每一组的每一行都返回对应的结果。

 排序函数必须要写order by,第一个括号不用写东西
 1. row_number()over()  --不考虑并列情况
 2. rank()over()  --考虑并列情况，不连续
 3. dense_rank()over()  --考虑并列且连续
 其中 partition by 分组 order by 排序，这两个关键字是写进over()里面的，用来进行分组排序

例子：
先创建students表，并插入数据。

CREATE TABLE "STUDENTS" (
  "NAME" VARCHAR2(20 BYTE),
  "CITY" VARCHAR2(20 BYTE),
  "AGE" NUMBER,
  "SALARY" NUMBER
)
INSERT INTO  " STUDENTS" VALUES ('Kebi', 'JiangSu', '20', '3000');
INSERT INTO  " STUDENTS" VALUES ('James', 'ChengDu', '21', '4000');
INSERT INTO  " STUDENTS" VALUES ('Denglun', 'BeiJing', '22', '3500');
INSERT INTO  " STUDENTS" VALUES ('Yangmi', 'London', '21', '2500');
INSERT INTO  " STUDENTS" VALUES ('Nana', 'NewYork', '22', '1000');
INSERT INTO  " STUDENTS" VALUES ('Sunli', 'BeiJing', '20', '3500');
INSERT INTO  " STUDENTS" VALUES ('Dengchao', 'London', '22', '1500');
INSERT INTO  " STUDENTS" VALUES ('Huge', 'JiangSu', '20', '2800');
INSERT INTO  " STUDENTS" VALUES ('Pengyuyan', 'BeiJing', '24', '4500');
INSERT INTO  " STUDENTS" VALUES ('Baoluo', 'London', '25', '8500');
INSERT INTO  " STUDENTS" VALUES ('Huting', 'ChengDu', '25', '3000');
INSERT INTO  " STUDENTS" VALUES ('Hurenxiang', 'JiangSu', '23', '2500');

select * from students

使用 row_number()over() 对 students表中工资情况进行排序

默认是从小到大排序
select name,city,age,salary,row_number()over(order by salary) as a from students

从大到小排序
select name,city,age,salary,row_number()over(order by salary desc) as a from students

题目：查找每个城市的工资并从大到小排名
 
思路：按照城市进行分组，然后排序
--使用row_number()over()
select name,city,age,salary,row_number()over(partition by city order by salary desc) as a from students

查找每个学生的工资排名
--使用rank()over()  对工资进行高低排序时，工资相同的情况下下一位的排名也会相应增加
select name,city,age,salary,rank()over(order by salary desc) as a from students

查找每个学生的工资排名
--使用dense_rank()over() 对工资进行高低排序时，工资相同的情况不影响下一个工资排名
select name,city,age,salary,dense_rank()over(order by salary desc) as a from students

找出每个城市工资前三的员工，不考虑并列
--这是一个分组排序，先根据城市分组，在按工资排序，再利用子查询把前三名筛选出来
select * from 
(select name,city,age,salary,row_number()over(partition by city order by salary desc) as a
from students)
where a<=3

偏移函数

偏移函数必须要写排序
向下偏移: lead(偏移的列,偏移量,默认值)over(分组 排序)

select name,age,lead(age,1,0)over(order by age ) as a from students

也可以使用分组
select name,city,age,lead(age,1,0)over(partition by city order by age ) as a from students

向上偏移: lag(偏移的列,偏移量,默认值)over(分组 排序)

select name,age,lag(age,1,0)over(order by age ) as a from students

使用分组
select name,city,age,lag(age,1,0)over(partition by city order by age ) as a from students

聚合函数

聚合函数
count(列)over() --一般不用写order by，没有意义
max(列)over()  --一般不用写order by，没有意义
min(列)over()  --一般不用写order by，没有意义
avg(列)over() --加order by 则当前行与前n行的平均值，相同的值当做同一行
sum(列)over() --加order by 则当前行与前n行的累加结果，相同的值当做同一行

count(列)over()，根据列的分组情况去进行统计

统计学生总数
select name,count(name)over() from students

根据城市分组，统计每个城市的学生人数
select name,city,count(name)over(partition by city) from students

max(列)over()，根据列的分组情况去进行查找最大值

查找表中最大工资
select name,salary,max(salary)over() from students

根据城市分组，找出所在城市最大工资
select name,city,salary,max(salary)over(partition by city) from students

min(列)over()，根据列的分组情况去进行查找最小值

查找表中最小工资
select name,salary,max(salary)over() from students

根据城市分组，找出所在城市最小工资
select name,city,salary,max(salary)over(partition by city) from students

avg(列)over()，根据列的分组情况去进行查找最平均值，如果加order by 进行排序，就会得出当前行与前n行的平均值，相同的值当做同一行

--查找所有学生工资的平均值
select name,salary,avg(salary)over() from students;

--根据城市分组，查找每个城市学生工资的平均值
select name,city,salary,avg(salary)over(partition by city) from students;

--查找所有学生工资的平均值，加order by 进行排序，
--结果是当前行与前n行的平均值，相同的值被当做同一行
select name,salary,avg(salary)over(order by salary) from students;

--根据城市分组，查找每个城市学生工资的平均值，加order by 进行排序，
--结果是当前行与前n行的平均值，连续相同的值会被一起计算
select name,city,salary,avg(salary)over(partition by city order by salary) from students

sum(列)over() ，根据列的分组情况去进行查找求和，如果加order by则是当前行与前n行的累加结果，连续相同的值会被一起计算

--得出表中所有工资求和的值
select name,salary,sum(salary)over() from students;

--根据城市分组，得出每个城市学生工资求和的值
--连续相同的值会被一起计算
select name,city,salary,sum(salary)over(partition by city) from students;

--加order by，则是得出表中工资当前行与前n行的累加结果
select name,salary,sum(salary)over(order by salary) from students;

--根据城市分组，加order by，则是得出每个城市学生工资当前行与前n行的累加结果
--连续相同的值会被一起计算
select name,city,salary,sum(salary)over(partition by city order by salary) from students;

返回特定行函数

first_value()over() --根据分组排序查找第一条数据
last_value()over()  --根据分组排序查找最后一条数据

first_value()over()，根据分组排序查找第一条数据

--查找表中工资列的第一条数据
select name,salary,first_value(salary)over() from students001;

--加order by进行排序，并倒序，查找表中工资列的第一条数据
select name,salary,first_value(salary)over(order by salary desc) from students001;

--根据城市分组，并查找工资列的第一条数据
select name,city,salary,first_value(salary)over(partition by city) from students001;

--加order by 根据城市分组排序，并查找工资列的第一条数据
select name,city,salary,first_value(salary)over(partition by city order by salary desc) from students001