[Oracle] Oracle开发之报表函数

sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)

SQL> select month,
     sum(tot_sales) month_sales,
     sum(sum(tot_sales)) over(order by month
     rows between unbounded preceding and unbounded following) win_sales,
     sum(sum(tot_sales)) over() rpt_sales
  from orders
   group by month;

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_tmp o
      where o.year = 2001
       group by o.region_id, o.cust_nbr) all_sales
 where all_sales.cust_sales > all_sales.region_sales * 0.2;

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

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;