mysql 自我练习基础 8：开窗函数 charlly练习版（CDA数据库） （排名）

create database CDA;
use CDA;

create table order_tab(
    order_id int, 
    user_no varchar(3), 
    amount int, 
    create_date date 
);

insert into order_tab values 
(1,'001',100,'2019-01-01'), 
(2,'001',300,'2019-01-02'), 
(3,'001',500,'2019-01-02'), 
(4,'001',800,'2019-01-03'), 
(5,'001',900,'2019-01-04'), 
(6,'002',500,'2019-01-03'), 
(7,'002',600,'2019-01-04'), 
(8,'002',300,'2019-01-10'), 
(9,'002',800,'2019-01-16'), 
(10,'002',800,'2019-01-22'); 

select * from order_tab;

-- 查询所有订单的订单总金额
-- 聚合函数用于开窗计算：开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

select *,sum(amount) over() as 订单总金额 from order_tab;
#当over中没有指定分区、排序和滑动窗口范围时，表中所有记录为一个区，默认计算的是分区内第一行到最后一行的值范围内的所有记录

-- 查询每个用户的订单总金额
select *,sum(amount) over(partition by user_no) as 订单总金额 from order_tab;
#当over中指定分区，但是没有指定排序和滑动窗口范围时，默认计算的是分区内所有行

-- 查询每个用户每天的订单总金额
select *,sum(amount) over(partition by user_no,create_date) as 订单总金额 from order_tab;

-- 查询每个用户按照下单时间顺序的累计订单金额
select *,sum(amount) over(partition by user_no order by create_date) as 累计订单金额 from order_tab;
#当over中指定排序，但是没有指定滑动窗口范围时，默认计算的是第一行到当前行的值范围内的所有记录             无滑动窗口指定行时   默认就是指定值来计算

-- 查询每个用户按照订单编号顺序，前一笔到后后一笔订单的平均订单金额(基于行)
select *,avg(amount) over(partition by user_no order by order_id rows between 1 preceding and 1 following) as 平均订单金额 from order_tab;

-- 查询每个用户按照订单编号顺序，前一笔到后两笔订单的平均订单金额(基于值)
select *,avg(amount) over(partition by user_no order by order_id range between 1 preceding and 2 following) as 平均订单金额 from order_tab;
#当over中指定滑动窗口范围，计算的是指定的值范围内的所有记录

-- 查询每个用户按照下单时间顺序的前一天到后一天的平均订单金额(基于值)
select *,avg(amount) over(partition by user_no order by create_date range between interval 1 day preceding and interval 1 day following) as 平均订单金额 from order_tab;
#当over中指定滑动窗口范围，计算的是指定的值范围内的所有记录
#当over中指定了排序 没有指定滑动窗口范围时  默认是是基于值来计算的

-- 查询每个用户订单金额排名
select *,row_number() over(partition by user_no order by amount desc) as 订单金额排名 from order_tab;-- row_number()为静态窗口函数 无需指定滑动窗口范围，指定了滑动窗口后不会报错但是无效
select *,
row_number() over(partition by user_no order by amount desc) as 订单金额排名1, -- 无并列 1234
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名2, -- 有并列 1123
rank() over(partition by user_no order by amount desc) as 订单金额排名3-- 有并列 跳过 1134
from order_tab;



-- 查询每个用户订单金额排名前三的订单
#1
select *,
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名
from order_tab
where 订单金额排名<=3;#报错：where 中不能引用别名，因为别名是在select中产生的 ，标的查询顺序 是from  where  select

#2
select *,
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名
from order_tab
where dense_rank() over(partition by user_no order by amount desc)<=3;#报错：where 中不能使用开窗函数（开窗函数本质上是聚合函数，where中不能用聚合函数），并且本句中开窗函数是在select中产生的 ，标的查询顺序 是from  where  select，两个错误原因

#3.
select *,
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名
from order_tab; -- 先把该表写出来 然后再此表基础上进行筛选即可

select *
from
(select *,
dense_rank() over(partition by user_no order by amount desc) as 订单金额排名
from order_tab) as t
where 订单金额排名<=3;
#与1.不一样的是第一步执行的是括号中子查询，该步骤中    订单金额排名   已经产生



-- 查询每个用户按照日期顺序，订单金额的每日增长率
select
user_no,
create_date,
sum(amount) as 订单总金额,
lag(订单总金额,1) over(partition by user_no order by create_date) 
from order_tab
group by user_no,create_date;# 报错，lag中引用了同是select语句中的别名


select
user_no,
create_date,
sum(amount) as 订单总金额,
lag(sum(amount),1) over(partition by user_no order by create_date) as 前一天的订单总金额
from order_tab
group by user_no,create_date; 


#老师语句
select 
    user_no,
    create_date,
    sum(amount) as 订单总金额,
    lag(sum(amount),1) over(partition by user_no order by create_date) as 前一天的订单总金额,
    (sum(amount)-lag(sum(amount),1) over(partition by user_no order by create_date))/lag(sum(amount),1) over(partition by user_no order by create_date) as 每日增长率
from order_tab
group by user_no,create_date;



select * from order_tab;
-- 查询每个用户上一个订单距离当前订单的间隔天数 （由于是对每个用户每个订单进行数据，则后续不需要进行聚合，否则输出不对）
-- charlly练习 步骤1.每个用户上一个订单时间2.计算上个订单 与当前订单的间隔天数
select *,
lag(create_date,1) over(partition by user_no order by create_date) as 上个订单时间,
timestampdiff(day,lag(create_date,1) over(partition by user_no order by create_date),create_date) as 上一个订单距离当前订单的间隔天数
from order_tab; 
-- 疑问 这里order by 该用create_date还是order_id，有可能存在两种情况1.一个用户在同一天下了两单 create_date是否适用2.这里的order_id比较理想化，如果order_id 实际上不是按照用户来的而是交错的

-- 老师代码
select 
    *,
    lag(create_date,1) over(partition by user_no order by order_id ) as 前一笔订单的下单日期, -- 该计算的是 第一行到当前行还是，整个计算的整个分区？
    timestampdiff(day,lag(create_date,1) over(partition by user_no order by order_id),create_date) as 间隔天数
 from order_tab 
 order by  order_id desc;-- 老师代码没有这句降序，添加这句是为了验证 整个表格的降序是受最后这个order by 影响（对结果不影响，出来了结果之后按照指定顺序再排列），开窗函数里面的升降序只会影响开窗函数的返回值，不影响前面字段的排列方式





-- 查询每个用户第一笔订单和最后一笔订单的下单日期
#老师代码

-- 方法一
select 
    *,
    first_value(create_date) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as 第一笔订单的下单日期,
    last_value(create_date) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as 最后一笔订单的下单日期
from order_tab;

-- 方法二
select 
    *,
    min(create_date) over(partition by user_no) as 第一笔订单的下单日期,
    max(create_date) over(partition by user_no) as 最后一笔订单的下单日期
from order_tab;



-- charlly 练习按照题目要求呈现：每个用户第一笔订单和最后一笔订单的下单日期
select user_no,第一笔订单的下单日期,最后一笔订单的下单日期
from
(select *,
min(create_date) over(partition by user_no ) as 第一笔订单的下单日期,
max(create_date) over(partition by user_no) as 最后一笔订单的下单日期
from order_tab) as t
group by user_no;

-- charlly 练习按照题目要求呈现：每个用户第一笔订单和最后一笔订单的下单日期
# 方法二
select user_no,
min(create_date) as 第一笔订单的下单日期,
max(create_date)  as 最后一笔订单的下单日期
from order_tab
group by user_no;