开窗函数的使用

开窗函数

开窗函数是满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具有灵活性，他对数据的每一行，都使用与该行相关的行进行计算并返回计算结果

开创函数名 ([字段名]) 
over ( [partition by <分组字段>]  
       [order by <排序字段> [desc]]
       [滑动窗口])

• 开窗函数的一个概念是当前行，当前行属于某鱼窗口
• 窗口由over关键字来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行
• 开窗函数基于所有行进行计算，则有三个参数来设置窗口

partition by <分组字段>

patition by :按照指定字段进行分区，两个分区由边界分割，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。

order by <排序字段>

order by ：按照指定字段进行排序，开创函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。

frame 子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用

• 对于滑动窗口的范围指定，between frame_start and frame_end
  语法来表示行范围，frame_start 和frame_end可以支持如下关键字，来确定不同的动态行记录：

current  row:边界就是当前行，一般和其他范围关键字一起使用
unbounded preceding 边界时分区中的第一行
unbounded following 边界是分区中的最后一行
expr preceding 边界是当前行减去expr的值
expr following 边界是当前行加上expr的值

窗口范围

‘窗口范围是当分区中的当前行、前一行、后一行共三行记录，如果在始末位置则只记录2行’
row between 1 preceding and 1 following

‘分区中的第一行当前行’
row unbounded preceding

'窗口范围是当前分区中的所有行，等同于不写'
row between unbounded preceding and unbounded following

序号函数

row_number():显示分区中不重复不间断的序号（1，2，3，4）
dense_rank():显示分区中重复不间断的序号(1,2,2,3)
rank():显示分区中重复间断的序号（1，2，2，4）

 '查询所有员工的工资排名'(无分区)
 select * row_number() over(order by desc) 排名 
 from emp;
 '查询各部门员工的工资排名'（有分区）
 select * ,
 row_number() over(partition by deptno order by sal desc) 排名
 from emp;

开窗函数求累计值

（表数据可在专栏数据库50题中找到）

select * ,sum(score) 
		over(partition by s_id order by c_id) 累计求和
		from sc

-- 20 查询学生的总成绩并进行名
#先计算出学生的总成绩，再在表中进行排名
select *,rank() over(order by 总成绩 desc) from 
(select stu.s_name, sum(score)总成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id) t;