over 开窗函数

目录

排序

唯一排序

有跳号并列排名

无跳号并列排名，值并列排序

聚合函数

除了分组排序，还有分组计数

分组求和

min() max()

行操作

前1行

后1行

第一行

最后一行

任意一行

百分比排名，返回0-1间

指定百分比，返回排名值，percent_rank的反函数

向上求和

行分布情况

按值分为几部份

离散分布

计算报告中值的比例

---

排序

唯一排序：

row_number() 

如果我们想知道不同年龄阶段学生id的排名，

那么首先我们是先想把学生按照学生年龄进行分类  然后再在不同类别中按照升序排序，命名为一个新的字段，这个字段里面的内容就是1,2,3,4 代表是这一组的第几个。

开窗函数就像在另外一个窗口中做一些操作，而不影响其它窗口中的操作。

select
student_id,
student_name,
student_age,
row_number()over(partition by studernt_age order by student_id asc) as rn
from student_info

或者降序排序 

select
student_id,
student_name,
student_age,
row_number()over(partition by studernt_age order by student_id desc) as rn
from student_info

此时rn=1时代表该数组中的最大值

有跳号并列排名

rank()

select
student_id,
student_name,
student_age,
rank()over(partition by studernt_age order by student_id asc) as rn
from student_info

跟row_number() 区别在于  如果有相同的数字时row_number() 会增加1；而rank()则是增加重复的条数。

无跳号并列排名，值并列排序：

dense_rank()

跟rank() 区别在于  如果有相同的数字时rank()则是增加重复的条数；而dense_rank()是增加1。

聚合函数

除了分组排序，还有分组计数

count(计数字段) over(partition by 分组字段)

----计算学生在该年龄阶段一共有多少人
select
student_id,
student_name,
student_age,
count(distinct student_id)over(partition by studernt_age) as rn
from student_info

分组求和

sum(求和字段)over(partition by 分组字段) as student_ids

---不同年龄阶段的学生id合计
select
student_id,
student_name,
student_age,
sum(student_id)over(partition by studernt_age) as student_ids
from student_info

min() max()

行操作

前1行：

lag(字段,1)

lag(student_id,1)over(partition by studernt_age order by student_id) as student_id--该生同龄前一个学生

后1行：

lead(字段,1)

lead(student_id,1)over(partition by studernt_age order by student_id) as student_id--该生同龄后一个学生

第一行：

first_value()

first_value(student_id,1)over(partition by studernt_age order by student_id) as student_id--第一个学生

最后一行：

last_value()

last_value(student_id,1)over(partition by studernt_age order by student_id) as student_id--最后一个学生

任意一行：

nth_value(字段,1)

百分比排名，返回0-1间：

percent_rank()

指定百分比，返回排名值，percent_rank的反函数：

percentile_cont()

向上求和：

sum(les_duration) over (

partition by student_id  order by time rows between unbounded preceding and current row

) as add_les_duration

---按照时间升序来看不同学生每次累计记录

行分布情况

按值分为几部份

ntile(等份数) over (排序条件) as num --将学生分为等份

select
student_id,
student_name,
student_age,
ntile(4) over (order by student_id asc) as num --将学生分为等份
from student_info

离散分布：

percentile_disc() 类似percentile_cont()

计算报告中值的比例：

ratio_to_report()  类似 val/sum(val) over()