sql的开窗函数

1.背景

在没有开窗函数之前，我们知道，用了分组之后，查询字段就只能是分组字段和聚合的字段，这带来了极大的不方便；
有时我们查询时需要分组，有需要查询分组的字段，每次都要用到子查询，这样显得sql语句复杂难懂；
给维护代码的人带来了很大的痛苦，然而开窗函数出现了，曙光也来临了。

2.定义

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。
它用于为行定义一个窗口(这里的窗口指运算将要操作的行的集合)，它对一组值进行操作，不需要使用group by语句对数据进行分组，
能够在同一行中同时返回基础行和聚合列。

3.举例

(1)建立数据表，插入数据

#a. 在本地DataBase新建一个Table,用于存储练习数据
Create Table Product
(product_id CHAR(4) not full,
product_name VARCHAR(100) not full ,
product_type VARCHAR(32) not full ,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date Date,
primary KEY (product_id));
# b.插入练习数据
# 插入数据
insert into Product values ('0001','T恤衫','衣服',1000,500,'2009-09-20');
insert into Product values ('0002','打孔器','办公用品',500,320,'2009-09-11');
insert into Product values ('0003','运动T恤衫','衣服',400,280,null);
insert into Product values ('0004','菜刀','厨房用具',3000,2800,'2009-01-20');
insert into Product values ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
insert into Product values ('0006','叉子','厨房用具',500,null,'2009-09-20');
insert into Product values ('0007','擦菜板','厨房用具',880,790,'2008-04-08');
insert into Product values ('0008','圆珠笔','办公用品',100,null,'2009-11-11');

(2)专用窗口函数使用

1.使用方法

rank函数是记录排序顺序的函数

# 将表中8种商品，根据product_type,按照sale_price进行排序
select product_id,product_name,product_type,sale_price,
rank() over (PARTITION by product_type 
order by sale_price asc ) as '排序'
from product

语句中，partition by指定排序的对象范围(横向上对表进行分组)，
order by 指定了按照哪一列何种顺序进行排列(纵向定义排序规则).
如果不适用partition by,效果如何？

# 将PARTITION  By 函数组置为沉默项
select product_id,product_name,product_type,sale_price,
rank() over(order by sale_price asc)  as '排序'
from product

3)函数区别

#下面将3种函数排序结果分被定义为 排序1，排序2，排序3，进行结果输出：
select product_id,product_name,product_type,sale_price,
rank() over  (order by sale_price asc)  as '排序1',
dense_rank() over  (order by sale_price asc)  as '排序2',
row_number() over  (order by sale_price asc)  as '排序3'
from product

4)聚合函数作开窗函数

#sum 聚合函数的使用
select product_id,product_name,product_type,sale_price,
sum(sale_price) over  (order by product_id)  as '排序1'
from product

5)保证排序结果的顺序

# 对计算结果再次进行排列
select product_id,product_name,product_type,sale_price,
rank() over  (order by sale_price )  as '排序1'
from product
order by 排序1