目录
1.开窗函数的定义
2.数据准备
3.开窗函数之排序
需求:用三种排序方法查询学生的语文成绩排名,并降序显示
4.开窗函数分组
需求:按照科目来分类,使用三种排序方式来排序学生的成绩
5.聚合函数与分组配合使用
6.聚合函数同时和分组以及排序关键字配合使用
--需求1：求出每个用户的总pv数,展示所有信息 默认第一行到最后一行
--需求2：求出每个用户截止到当天，累积的总pv数 默认第一行到当前行
做题思路,开窗函数核心:保证输出结果的记录数和输入的数据记录数一致
7.窗口范围控制
1.默认第一行到当前行
2.第一行到当前行,等效于rows between ..不写,默认就是第一行到当前行
3.向前3行到当前行
4.向前3行 向后1行
5.当前行到最后一行,第一行到最后一行
8.其他函数
1.ntile平分:
注意ntile规则:尽量平均分配 ，优先满足最小(编号1)的桶，彼此最多不相差1个。
--需求：统计每个用户pv数最多的前3分之1天。--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
- 窗口：可以理解为操作数据的范围，窗口有大有小，本窗口中操作的数据有多有少。
- 可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行
-而窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。
开窗函数格式: select ... 开窗函数 over(partition by 分组字段名 order by 排序字段名 asc|desc) ... from 表名;
-- 如果有分组操作,select后的字段要么在聚合函数内,要么在group by 后出现
-- 开窗函数: hive和mysql8都能使用
-- 开窗函数本质在表后新增了一列
-- 聚合开窗函数: max min sum avg count
数据文件score.txt
-- 排序开窗函数: row_number rank dense_rank
-- 排序函数必须配合over(order by 排序字段 asc|desc)
row_number: 巧记: 1234 特点: 唯一且连续
dense_rank: 巧记: 1223 特点: 并列且连续
rank : 巧记: 1224 特点: 并列不连续
-- 开窗函数分组
-- 注意不能用group by ,需要使用partition by,可以理解成partition by是group by的子句
-- 演示排序函数和分组配合使用: 先分组再组内排序
查询每个学生的信息,按照文理科分类,以及平均分
cookie是记住用户记录的一个文件,代表一个用户
--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
做题思路,开窗函数核心:保证输出结果的记录数和输入的数据记录数一致
rows between
- preceding：往前
- following：往后
- current row：当前行
- unbounded：起点
- unbounded preceding 表示从前面的起点 第一行
- unbounded following：表示到后面的终点 最后一行
1+5+7=13 , 1+5+7+3=16 , 5+7+3+2=17 , 7+3+2+4=16 , 3+2+4+4=13
相当于查询今天以及前三天的总浏览量,在现实中常称为网站的'最近3天访问量'.
1+5+7+3+2=18 , 5+7+3+2+4=21
其他开窗函数: ntile lag和lead first_value和last_value
ntile(x)功能: 将分组排序之后的数据分成指定的x个部分（x个桶）
注意ntile规则:尽量平均分配 ，优先满足最小(编号1)的桶，彼此最多不相差1个。
lag: 用于统计窗口内往上第n行值
lead:用于统计窗口内往下第n行值
first_value: 取分组内排序后，截止到当前行，第一个值
last_value : 取分组内排序后，截止到当前行，最后一个值
注意: 窗口函数结果都是单独生成一列存储对应数据