function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

select * from orders_tmp;

select cust_nbr, region_id, cust_sales, region_sales,  -- 此處可以用tmptb.* , 但不能用 *
100 * round(cust_sales / region_sales, 2) || '%' Percent from 
 (select cust_nbr, region_id,
    sum(TOT_SALES) cust_sales,
    sum(sum(tot_sales)) over(partition by REGION_ID) as region_sales
  from orders_tmp where o.year = 2001 group by CUST_NBR, REGION_ID order by REGION_ID) tmptb
 where cust_sales > region_sales * 0.2;

Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])
Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])
Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])
Ntile() Over ([Partition by ] [Order by ])

select rownum, tmptb.* from 
 (select * from user_order order by CUSTOMER_sales desc) tmptb
where rownum <= 12;

select region_id, customer_id, 
  sum(customer_sales) total,
  rank() over(partition by region_id order by sum(customer_sales) desc) rank,
  dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,
  row_number() over(partition by region_id order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;

select region_id, customer_id,
  sum(customer_sales) cust_sales,
  sum(sum(customer_sales)) over(partition by region_id) ran_total,
  rank() over(partition by region_id order by sum(customer_sales) desc /* nulls last */) rank
from user_order
group by region_id, customer_id;

select * from 
(select region_id,
    customer_id,
    sum(customer_sales) cust_total,
    rank() over(order by sum(customer_sales) desc NULLS LAST) rank
  from user_order
  group by region_id, customer_id)
 where rank <= 3;

select *
from (select region_id,
    customer_id,
    sum(customer_sales) cust_total,
    sum(sum(customer_sales)) over(partition by region_id) reg_total,
    rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank
 from user_order
 group by region_id, customer_id)
where rank <= 3;

Sum() Over ([Partition by ] [Order by ])
Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And  Following)   
Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And Current Row)
Sum() Over ([Partition by ] [Order by ]     Range Between Interval '' 'Day' Preceding    And Interval '' 'Day' Following )

Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])

select 
   min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first,
   min(customer_id) keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;

select region_id,customer_id,
 sum(customer_sales) sales,
 ntile(5) over(order by sum(customer_sales) desc nulls last) tile
from user_order
group by region_id, customer_id;

select * from 
(select region_id,customer_id,
   sum(customer_sales) sales,
   ntile(5) over(order by sum(customer_sales) desc nulls last) tile
 from user_order
 group by region_id, customer_id)
where tile = 1;

select cust_nbr,
 max(month) keep(dense_rank first order by month desc) max_month
from orders_tmp 
group by cust_nbr;

delete from orders_tmp2 where (cust_nbr, month) not in 
 (select cust_nbr, max(month) keep(dense_rank first order by month desc) max_month
from orders_tmp2 tb 
group by cust_nbr)

First_value / Last_value(Sum() Over ([Patition by ] [Order by ] Rows Between Preceding And Following ))

Lag(Sum(), 1) Over([Patition by ] [Order by ])

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;

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over(/*order by month*/) all_sales  -- 加上Order by month , 則數(shù)逐條記錄遞增
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;

select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over(order by month) all_sales  -- 加上Order by month , 則是前面記錄累加到當(dāng)前記錄
from orders 
group by month;

select trunc(order_dt) day,
 sum(sale_price) daily_sales,
 avg(sum(sale_price)) over (order by trunc(order_dt) range between interval '2' day preceding and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null and order_dt between to_date('01-jul-2001','dd-mon-yyyy') and to_date('31-jul-2001','dd-mon-yyyy')

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  month,            
 sum(tot_sales) monthly_sales,
 lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders_tmp
where year = 2001 and region_id = 6
group by month order by month;

select region_id, salesperson_id,
  sum(tot_sales) sp_sales,
  round(ratio_to_report(sum(tot_sales)) over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;