SQL牛客网刷题易错点3---窗口函数

先知识点（scdn）–再视频B站视频----练习题

窗口函数

0、用处：在组内排名、组内取topn值等需求。处理相对复杂的报表统计分析场景
排名问题：每个品牌的商品按销售额来排名
topN问题：找出每个品牌排名前N的商品

1、定义：窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），作用于一个数据集合。窗口函数的一个概念就是当前行，当前行属于某个窗口就是从整个数据集选取一部分数据进行聚合/排名等操作。
窗口–类似窗户，限定一个空间范围
窗口函数–应用在窗口内的函数

2、语法:
基本用法：函数名（）over（）
over 是关键字，用来指定函数执行的窗口范围，包括3个子句：分组（partition by）,排序（order by）,窗口（rows）,如果后面括号什么都不写，则窗口包含满足where条件的所有行

window_function_name(window_name/expression)
OVER (
[partition_defintion]
[order_definition]
[frame_definition])

窗口函数名（窗口名/)
over(partition by <要分列的组>  order by <要排序的列> rows between <数据范围> ）

3、语法中的元素：
3.1、window_function_name窗口函数名
静态窗口函数 vs 滑动窗口函数
静态窗口函数：rank() , dense_rank() , row_number()

滑动窗口函数：

• 聚合函数 sum,avg ,count , max, min , percent_rank(), cum_dist()
• 取值函数 first_value() ,last_value() , nth_value() ,lag() , lead() ,ntile()
  
  
  3.2、分区partition_defintion
  partition by 指定字段 ：窗口函数功能在分区内执行，并在跨越分区边界时重新初始化。
  如果没有指定 partition by 语句，且没有后面的frame元素限制，就把所有数据当做一整个区。

3.3、排序order_definition
order by 指定字段 ：和partition by 子句配合使用，就是对分区后的数据进行排序

3.3、框架frame_definition
窗口框架的作用是对分区进一步细分
frame_unit有两种，分别是rows和range，ROWS是基于行号，RANGE是基于值的范围

between frame_start and frame_end :用来表示行范围，frame_start和frame_end可以支持如下关键字：

• current row 当前行
• unbounded preceding 区间的第一行 （unbounded无边界）
• unbounded following 区间最后一行
• expr npreceding :当前行之前的N行，可以是数字，也可以是一个能计算出数字的表达式
• expr nfollowing : 当前行之后的N行，可以是数字，也可以是一个能计算出数字的表达式
  例子：
  rows between 2 preceding and current row #每次都取当前行和前面两行，然后不断移动
  rows between unbounded preceding and current row #包括本行和之前的所有行
  rows between current row and unbounded following # 包括本行和之后的所有行
  rows between 3 preceding and 1 following # 包括前三行和下一行，一共5行

rows类似分区中的子集

3.4 、案例
创建表

drop table if exists score;
create table if not exists 'score' (
'学号' int(5) zerofill not null,
'姓名' varchar(10) not null,
'课程号' varchar(15) not null,
'成绩' int(5) not null default 0)
ending = InnoDB default charset=utf8 collate=utf8_unicode_ci comment='成绩单'；

插入数据

insert into 'score'(`学号`, `姓名`, `课程号`, `成绩`)
values
( '0005', '范若若', '00001', '99' ),( '0005', '范若若', '00002', '81' ),..

查询表

select * from score order by 学号;

1. rank(),dense_rank(),row_number() 静态窗口 （这三个是专有的窗口函数）

select 姓名，课程号，成绩
rank() over (partition by 姓名