扫码加入数据分析学习群

#第六大部分

-- 开窗函数

##准备数据

create table order_tab(

order_id int,

user_no varchar(3),

amount int,

create_date date

);

insert into order_tab values

(101,'001',100,'2019-01-01'),

(211,'001',300,'2019-01-02'),

(308,'001',500,'2019-01-02'),

(415,'001',800,'2019-01-03'),

(523,'001',900,'2019-01-04'),

(612,'002',500,'2019-01-03'),

(718,'002',600,'2019-01-04'),

(804,'002',300,'2019-01-10'),

(911,'002',800,'2019-01-16'),

(1109,'002',800,'2019-01-22');

select * from order_tab;

-- 43 开窗聚合函数

# 先看普通(分组)聚合函数, 按user_no分组,按sum(amount)聚合, 查询不同客户的订单总金额

select user_no ,create_date, sum(amount) #订单总金额

from order_tab

group by user_no

order by create_date;

# 再看开窗(分组)聚合函数, 按user_no分组,按sum(amount)聚合,查询不同客户的订单累计金额

select user_no, create_date,sum(amount) over (partition by user_no order by create_date ) #as 累计金额

from order_tab;

#** 普通分组聚合函数,每组只sum返回一个结果,开窗聚合函数,每组可sum返回多个结果 **

##课堂练习1, 查询不同客户的订单累计数量

select user_no, create_date,count(amount) over (partition by user_no order by create_date ) as 累计数量

from order_tab;

##课堂练习2, 查询不同客户的每日累计订单均价

select user_no, create_date,sum(amount) over (partition by user_no order by create_date ) as 累计金额,

count(amount) over (partition by user_no order by create_date ) as 累计数量,

avg(amount) over (partition by user_no order by create_date ) as 累计均值

from order_tab;