分析函数实例3

/*create table orders (
month number(2),
tot_sales number);
*/

/*
insert into orders values (1,610697);
insert into orders values (2,428676);
insert into orders values (3,637031);
insert into orders values (4,541146);
insert into orders values (5,592935);
insert into orders values (6,501485);
insert into orders values (7,606914);
insert into orders values (8,460520);
insert into orders values (9,392898);
insert into orders values (10,510117);
insert into orders values (11,532889);
insert into orders values (12,492458);
commit;

select * from orders;*/

--列出每月的订单总额以及全年的订单总额
--① sum() over () 函数会在记录集形成的过程中，每检索一条记录就执行一次，在此例中总共执行12次，这是非常耗时的。
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) total_sales
from orders
group by month;

--② over（）中可以加入partition by ，则先分区，再依次统计各个分区。
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over() rpt_sales
from orders
group by month;


--列出每月的订单总额以及截止到当前月的订单总额
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over (order by month rows between unbounded preceding and current row) current_total_sales
from orders
group by month;

--显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值
select month,
first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,
sum(tot_sales) monthly_sales,
last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avg
from orders_tmp
where year=2001 and region_id=6
group by month
order by month;

--显示当前月的销售额和上个月的销售额
select * from orders;
--①
select month,
sum(tot_sales) curr_month_sales,
first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 0 following) prev_month_sales
from orders
group by month;

--②
select month 月份,
sum(tot_sales) 当月总销售额,
lag(sum(tot_sales),1,0) over (order by month) 上个月总销售额
from orders
group by month;

--列出上一年度每个月[地区]的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例
select * from orders_tmp;

--1.
select all_sales.*,
100*round(cust_sales/region_sales,2) || '%' Percent
from (
select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over (partition by o.region_id) region_sales
from orders_t

mp o
where o.year=2001
group by o.cust_nbr,o.region_id) all_sales
where all_sales.cust_sales > all_sales.region_sales*0.2;

--2.
select region_id,
salesperson_id,
sum(tot_sales) sp_sates，
round(sum(tot_sales)/sum(sum(tot_sales)) over (partition by region_id),2) percent_of_region
from orders_tmp
where year=2001
group by region_id,salesperson_id
order by region_id,salesperson_id;

--3.
select region_id,
salesperson_id,
sum(tot_sales) sp_sates，
round(ratio_to_report(sum(tot_sales)) over (partition by region_id),2) sp_ratio
from orders_tmp
where year=2001
group by region_id,salesperson_id
order by region_id,salesperson_id;