sql自学笔记（二）窗口函数

一. 窗口函数语法
over (partition by <用于分组的列名> order by <用于排序的列名>
不减少原表的函数，经常用来在每组内排名
同时具有分组和排序的功能

• 窗口函数只能写在select子句中

业务需求在“每组内排名”，比如：

• 排名问题：每个部门按业绩来排名
• 找出每个部门排名前N的员工进行奖励
• group by 分组汇总后改变了表函数，一行只有一个类别，而partition by 和rank 不会减少原表中函数
  

二、 标准聚合函数

• 当聚合函数只用了partition by,没使用order by时，聚合函数计算按照每个分区全部数值计算，同一分区内的数值返回相同的数
• 当聚合函数同时使用partition by 和 order by时，按照每个分区的顺序逐行叠加

1. 移动平均窗口函数
   avg(字段名） over(partition by 用于分组的列名 order by 用于排序的列名 asc|desc rows betweeen A and B

移动平均
selec *,avg(grade) over(order by stu_no rows
between 2 preceding and current row) as ‘三移动平均’ from v_info

2. 计数函数（count）

select ,count() over(partition by c_no) as 'ct' from v_info where grade>=80

3. 累计求和窗口函数sum
   sum(字段名） over(partition by 字段名2 order by 字段名3 asc|desc）

(1)根据学号排序，对学生成绩进行累计求和
select *,sum(grade) over(order by stu_no) as '累计求和' from v_info

(2)按照课程号分组，然后根据学号对成绩进行累计求和
selece*,sum(grade)over(partition by c_no order by stu_no) as '累计求和’ from v_info

(3）若不根据学号排序，则最后得到的是总和结果
select *，sum(grade) over(partition by c_no)as '累计求和’ from v_info

4.最大（max）、最小（min）窗口函数
max（字段名1）over(partition by 字段名2 order by 字段名3 asc|desc

按照学号进行排序，依次向下寻找最大值，若有比当前值大的就更新，若去掉order，则找每个分区的最大，最小值

三、排序窗口函数

1. row_number
   row_number()over(partition by 字段1 order by 字段2
   依次排序且不会重复
   

2. rank（）
   rank() over(partition by 字段1 order by 字段2
   使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名排下一个，rank函数生成的序号有可能是不连续的，即排名可能为1，1，3，是跳跃式排名，有两个第一名时接下来就是第三名

3.dense_rank()
dense_rank() over(partition by 字段1 order by 字段2

四、分组排序窗口
ntile(n) over(partition by 字段名2 order by 字段名3 asc|desc
可以按照销售额的高低、点击次数的高低，以及成绩的高低为对用户和学生进行分组，这里的考点是：取销售额最高的25%的用户（将用户分成4组，取出第一组）、取成绩高的前10%的学生（将学生分成10组，取出第一组）等等

• ntile（n）用于将分组数据按照顺序分为n片

取出成绩前25%的学生
（第一步）按照成绩高低，将学生按照成绩进行切片
SELECT ,ntile(4) OVER(ORDER BY grade DESC) AS 'rank' FROM v_info

（第二步）按照rank筛选出第一组
SELECT a.
FROM
(SELECT *,
ntile(4) OVER(ORDER BY grade DESC) AS 'rank'
FROM v_info) AS a
WHERE a.rank=1

五、偏移分析窗口
lag(exp,offset)over(partition by```order by```)
exp表示字段名称，offset表示偏移量

（1）select*，lag（birth，1）over(partition by sex)as lag_1 from v_info

(2)select*，lead（birth，1）over(partition by sex)as lag_1 from v_info