08.窗口函数

-- 1.窗口函数的作用 - 给查询数据添加字段
-- 2.窗口函数的语法 ： 函数名(参数) over(over子句)
-- 1）函数名 a.聚合函数（max\min\sum等等）
--           b.专用窗口函数：
--              rank()、dense_rank()、row_number() --和排名相关
--              lag() -延后  lead() -领先
-- 2）参数：可能有，也可能没有。跟使用的具体的函数有关
-- 3）over():	固定写法
-- 4）over子句：over子句分为三种
--           a. partition by    -  分组 
--			 b. order by        -  排序 
--           c. rows		    -  行数
--           n preceding  - 前n行(包含当前行)
--           n following - 后n行(包含当前行)
--		     current row  - 当前行 
--           unbounded preceding - 窗口第一行
--           unbounded following - 窗口最后一行


use school;

select stu_name,stu_sex,min(stu_birth) over(partition by stu_sex) from students;
select stu_id,max(score) from records group by stu_id;
select stu_id,max(score) over(partition by stu_id) from	records;

use hrs;
select ename,sal,rank() over(order by sal desc) from tb_emp;		-- rank() 根据over子句里排序方式返回排名结果
select ename,sal,dense_rank() over(order by sal desc) from tb_emp;	-- 同上，排名结果都有并列排名，区别在并列之后是否连续排名
select row_number() over() as 行号 ,ename,sal from tb_emp;          -- 添加行号 也可以在voer子句里添加排序方式等

-- 窗口函数补充练习
create table tb_sale
(
sale_month integer not null,
sale_amount integer not null
);

insert into tb_sale
values    (1, 182),
    (2, 169),
    (3, 325),
    (4, 287),
    (5, 266),
    (6, 355),
    (7, 349),
    (8, 320),
    (9, 297),
    (10, 288),
    (11, 423),
    (12, 311);
-- 统计从1月到12月的累计销售额（可以先不用窗口函数）
select sale_month,sale_amount,sum(sale_amount) over(rows between unbounded preceding and current row ) from tb_sale;

-- 计算逐月环比（如：(二月 - 一月) / 一月
select sale_month,sale_amount as 本月,lag(sale_amount) over() as 上月 from tb_sale;
select sale_month, concat((本月-上月)/上月 * 100,'%') as 环比增长 from (select sale_month,sale_amount as 本月,lag(sale_amount) over() as 上月 from tb_sale) as t1;