【SQL实用技巧】-- 窗口函数的使用

窗口函数问题

数据准备

用来学习窗口函数

create table student (
    class_id int comment '班级编号',
    name varchar(10) comment '学生姓名',
    score int comment '考试分数'
);
insert into student values
( 1,'张三',60),
( 1,'李四',70),
( 1,'王五',80),
( 1,'赵六',90),
( 2,'小明',80),
( 2,'小强',90);
select * from student;

窗口函数语法：

窗口函数名(参数) over(partition by 分组字段 order by 排序字段 rows between 起点 and 终点) as 新字段名

聚合类窗口函数

问题1：让每个学生知道各自班级内的总分是是多少：

select *,
       -- 班级内总分是多少
       -- 写法1
       sum(score) over(partition by class_id order by score rows between unbounded preceding and unbounded following) as sum1,
       -- 简化写法2
       sum(score) over(partition by class_id) as sum2
 from student;

rows between unbounded preceding and unbounded following意思是对上面排序后的数据，从 unbounded preceding 直译为向前最远边界的那行，也就是张三 60 分那行，到 unbounded following 直译为向后最远边界的那行，也就是赵六 90 分那行，形成一个子窗口

问题2：让每个学生知道，班级内小于等你与自己的同学的总分是多少：

select *,
       -- 班级内分数小于等于自己的同学的总分是多少
       -- 写法1
       sum(score) over(partition by class_id order by score rows between unbounded preceding and current row) as sum3,
       -- 简化写法2
       sum(score) over(partition by class_id order by score) as sum4
 from student;

和上一个问题相比是把 unbounded following换为了 current row。

rows between unbounded preceding and current row，意思是对上面排序后的数据，从 unbounded preceding （向前最远边界的那行），也就是张三 60 分那行，到 current row 直译为当前行，也就是李四 70 分本行，形成一个子窗口，也就是张三 60、李四 70 这 2 行

注意：需要记住省略的 rows between unbounded preceding and current row，一般用在累加场景

问题3：如何让每个学生知道，比自己高和低1名的同学及自己三人的总分是多少：

select *,
       -- 班级内每个学生，比他分数高1名的同学、低1名的同学，和自己3人的总分是多少
       sum(score) over(partition by class_id order by score rows between 1 preceding and 1 following) as sum5
 from student;

聚合类的 count、avg、max、min使用方式与sum类似。

排序类窗口函数

排序类窗口函数有3个，分别是 row_number, rank,dense_rank

三者的不同点： row_number ：排序后给连续的序号，不考虑并列名次。 rank ：排序后，如果有并列名次，则序号一样，且会跳过下一序号，使序号不连续。 dense_rank ：排序后，如果有并列名次，则序号一样，且不会跳过下一序号，序号是连续的

在上个案例基础上补充数据：

insert into student values
( 1,'小乔',70);
select * from student order by class_id,score;

问题：看每个班级内，按考试分数排序情况

-- 排序类的窗口函数
select *,
       -- 用row_number连续排序
       row_number() over(partition by class_id order by score) rn,
       -- 用rank并列排序，考虑并列且序号不连续
       rank() over(partition by class_id order by score) rk,
       -- 用dense_rank并列排序，考虑并列且序号连续
       dense_rank() over(partition by class_id order by score) drk
from student;

排序窗口函数使用时，不用写rows between 起始 and 结束

偏移类的，跨行的窗口函数

一共有2个，分别是 lag， lead 函数，分别是向前，向后。

函数语法：

LAG(expression,offset,default_value) OVER (
    PARTITION BY expr,...
    ORDER BY expr [ASC|DESC],...
)

都有三个参数，第一个参数是列名（需要偏移的字段），第二个参数是偏移量（offeset）,第三个参数是超出记录窗口时的默认值(默认为null，可设置为0)

问题：如何让每个学生知道，在班级内，考试分数比自己低1名的同学的分数，以及比自己高一名的同学的分数，找不到就显示空。

避免上一个案例的干扰，这里先删除小乔同学。

delete from student where name='小乔';
select * from student ;

实现：

select *,
       -- 获取比自己低2个名次的学生的分数，若找不到则显示0
       lag(score,2,0) over(partition by class_id order by score) lag2,
       -- 获取比自己高2个名次的学生的分数，若找不到则显示0
       lead(score,2,0) over(partition by class_id order by score) lead2
from student;

其它窗口函数

获取第N行的值

这类窗口函数有 3 个，分别是 first_value 、 last_value 、 nth_value 。

first_value() ：返回窗口中第 1 个值。

last_value() ：返回窗口中最后的值。

nth_value(expr,n) ：返回窗口中第 N 个值。

问题：

如何让每个学生都知道，本班级内，考试分数最低的分数，以及最高的分数

实现：

select *,
       -- 获取本班级内，考试分数最低的分数
       first_value(score) over(partition by class_id order by score) first,
       -- 获取本班级内，考试分数最高的分数 ，的正确用法
       last_value(score) over(partition by class_id order by score rows between unbounded preceding and unbounded following) last
from student;

分布函数

目前是简单了解

percent_rank()

• 用途：和之前的 RANK() 函数相关，每行按照如下公式进行计算：
  • (rank - 1) / (rows - 1)
  • 其中，rank 为 RANK() 函数产生的序号，rows 为当前窗口的记录总行数。
• 应用场景：用的比较少，了解就好。

cume_dist()

• 用途：分组内小于等于当前 rank 值的行数/分组内总行数，这个函数比 percen_rank 使用场景更多。

使用

select *,
    rank() over w as rank_num,
    percent_rank() over w as percent,
    cume_dist() over w as cume
from student window w as (
        partition by class_id
        order by score
    );

省略了 rows between unbounded preceding and current row 。这会造成什么呢？比如以李四行做为分析对象，子窗口只圈定了从张三到自己之间的行，窗口内末尾就是李四自己，返回了 70，而不是 90。若要想返回 90，则必须将 current row 替换成 unbounded following 。

select *,
       -- 获取本班级内，考试分数最高的分数 ,之错误用法
       last_value(score) over(partition by class_id order by score ) last1,
       -- 因为上句等价于：
       last_value(score) over(partition by class_id order by score rows between unbounded preceding and current row) last2
from student;

思考在班级内按分数排序之后，如何获取截至目前，排名第二和第三的分数。

select *,
    nth_value(score,2) over w second_score,
    nth_value(score,3) over w third_score
from student window w as (
        partition by class_id
        order by score
    );

分组 ntile

NTILE(n) 函数用于将一组分区中的有序数据再尽量平均划分为 n 个小组 。

select *,
       ntile(2) over(partition by class_id order by score) subgroup
from student;