-- 创建表
create table sales
(
year int,
country varchar(20),
goods varchar(50),
profit int
);
insert into sales values(2018,'c1','g1',314),(2016,'c1','g1',33),
(2017,'c1','g2',31),
(2016,'c2','g1',331),
(2017,'c2','g2',343),
(2018,'c2','g1',32),
(2016,'c3','g1',52),
(2017,'c3','g2',312),
(2018,'c3','g1',53);

--正常情况下的计算不同国家的总利润：
select country,sum(profit) as all_profit
from sales group by country
order by country;
-- 分组聚合函数的写法，不需要手动分组
select year,country,goods,profit,
sum(profit) OVER (partition by country) as all_profit
from sales
order by country,year,goods,profit;

-- 准备数据
create table numbers(val int);
insert into numbers values(1),(2),(3),(3),(3),(3),(4),(5),(5),(6),(6),(7),(8),(8),(9);

-- 正常排序
select val,row_number() over (order by val) as 'row_number' from numbers;
-- 如果val相同，则排序相同，下一个排序号会出现间隙，步长不一定为1
select val,rank() over (order by val) as 'rank' from numbers;
-- 如果val相同，则排序相同，排序号之间没有间隙，步长为1
select val,dense_rank() over (order by val) as 'dense_rank' from numbers;

select val,first_value(val) over (order by val) as 'first'
,last_value(val)  over (order by val) as 'last'
,lead(val,1) over (order by val) as 'lead'
,lag(val,1) over (order by val)  as  'lag'
from numbers;

select val,rank() over (order by val) as 'rank'
,percent_rank() over (order by val) as 'percent_rank'
,cume_dist() over (order by val)  as 'cume_dist'
from numbers;

select year,profit,nth_value(profit,1)  over w as 'nth_1'
,nth_value(profit,2) over w as 'nth_2'
,nth_value(profit,3) over w as 'nth_3'
from sales
WINDOW w as (partition by year order by profit)
order by year,profit;

select year,profit,ntile(1)  over w as 'ntile_1'
,ntile(2) over w as 'ntile_2'
,ntile(3) over w as 'ntile_3'
,ntile(5) over w as 'ntile_3'
from sales
WINDOW w as (partition by year order by profit)
order by year,profit;