#第六大部分

-- 開窗函數(shù)

##準(zhǔn)備數(shù)據(jù)

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 開窗聚合函數(shù)

# 先看普通(分組)聚合函數(shù), 按user_no分組,按sum(amount)聚合, 查詢不同客戶的訂單總金額

select user_no ,create_date, sum(amount) #訂單總金額

from order_tab

group by user_no

order by create_date;

# 再看開窗(分組)聚合函數(shù), 按user_no分組,按sum(amount)聚合,查詢不同客戶的訂單累計金額

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

from order_tab;

#** 普通分組聚合函數(shù),每組只sum返回一個結(jié)果,開窗聚合函數(shù),每組可sum返回多個結(jié)果 **

##課堂練習(xí)1, 查詢不同客戶的訂單累計數(shù)量

select user_no, create_date,count(amount) over (partition by user_no order by create_date ) as 累計數(shù)量

from order_tab;

##課堂練習(xí)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 累計數(shù)量,

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

from order_tab;