0基础跟德姆（dom）一起学AI 基础阶段 MySQL数据库05-窗口函数

/*
窗口函数介绍:
    概述:
        窗口函数也叫 开窗函数, 它是MySQL8.X的新特征, 主要是用于 做分组排名, 聚合, 获取指定值的.
        其中用到最多的函数就是:
            排序相关的函数: row_number(), rank(), dense_rank(), ntile(n)
        除此以外, 还有其他的一些窗口函数, 例如:
            聚合函数相关:
                sum() over(...)
                max() over(...)
                min() over(...)
                count() over(...)
                avg() over(...)
            其它函数相关:
                lag(), lead(), first_value(), last_value()
    精髓:
        窗口函数 = 给表新增1列, 至于新增的内容是什么, 取决于和什么函数一起用.
    排序类函数介绍:
        假设初始数据为: 100, 90, 90, 80, 则三个排序函数结果为:
            row_number(): 1, 2, 3, 4
            rank():       1, 2, 2, 4
            dense_rank(): 1, 2, 2, 3
    格式:
        窗口函数 over(partition by 分组字段 order by 排序的列 [asc | desc])
*/
# 1. 建库, 切库, 查表.
create database day04;
use day04;
show tables;

# 2. 建表, 添加表数据.
create table employee (     # 员工表
    empid int,              # 员工id
    ename varchar(20) ,     # 员工名
    deptid int ,            # 员工部门id
    salary decimal(10,2)    # 员工工资
);

insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);

insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);

insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);

# 3. 查看表数据
select * from employee;

# 4. 窗口函数经典需求1: 分组排名.
# 需求: 按照 deptid(部门id)分组, 并对组内的员工按照薪资进行排名.
# 额外的话, 怎么给表新增1列.
select
    *,
    # 按照部门id分组, 组内按照 工资降序排名
    row_number() over(partition by deptid order by salary desc) as rn,      # 1, 2, 3, 4
    rank() over(partition by deptid order by salary desc) as rk,            # 1, 2, 2, 4
    dense_rank() over(partition by deptid order by salary desc) as dr,      # 1, 2, 2, 3
    # ntile(数字), 它表示把组内的数据分成几份, 优先参考最小分区, 例如: 7条数据, 分成3份, 结果为: 1,1,1   2,2   3,3
    ntile(3) over(partition by deptid order by salary desc) as nt           # 1, 1, 2, 3
from employee;

# 5. 窗口函数经典需求2: 分组排名, 求TopN.
# 需求2: 获取每个部门, 薪资最高的2个人的信息.
# 写法1: 思路是OK的, 但是语法不支持, 因为: where后边的字段必须是表中已有的字段.
select
    *,
    rank() over(partition by deptid order by salary desc) as rk
from employee
where rk <= 2;

# 写法2: 改造成: 子查询的写法即可.
select * from (
    select *,
            rank() over (partition by deptid order by salary desc) as rk
    from
        employee
) t1 where rk <= 2;