目录
=========================================
1.报表函数简介
2.RATIO_TO_REPORT函数
一、报表函数简介:
回顾一下前面《Oracle开发专题之:窗口函数》中关于全统计一节,我们使用了Oracle提供的:
sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)
来统计全年的订单总额,这个函数会在记录集形成的过程中,每检索一条记录就执行一次,它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法:
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and unbounded following) win_sales,
5 sum(sum(tot_sales)) over() rpt_sales
6 from orders
7 group by month;
MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
1 610697 6307766 6307766
2 428676 6307766 6307766
3 637031 6307766 6307766
4 541146 6307766 6307766
5 592935 6307766 6307766
6 501485 6307766 6307766
7 606914 6307766 6307766
8 460520 6307766 6307766
9 392898 6307766 6307766
10 510117 6307766 6307766
11 532889 6307766 6307766
12 492458 6307766 6307766
已选择12行。
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and unbounded following) win_sales,
5 sum(sum(tot_sales)) over() rpt_sales
6 from orders
7 group by month;
MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
1 610697 6307766 6307766
2 428676 6307766 6307766
3 637031 6307766 6307766
4 541146 6307766 6307766
5 592935 6307766 6307766
6 501485 6307766 6307766
7 606914 6307766 6307766
8 460520 6307766 6307766
9 392898 6307766 6307766
10 510117 6307766 6307766
11 532889 6307766 6307766
12 492458 6307766 6307766
已选择12行。
over函数的空括号表示该记录集的所有记录都应该被列入统计的范围,如果使用了partition by则先分区,再依次统计各个分区。
二、RATIO_TO_REPORT函数:
报表函数(窗口函数)特别适合于报表中需要同时显示详细数据和统计数据的情况。例如在销售报告中经常会出现这样的需求:列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例:
方法①:
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;
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;
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;
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;
Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。
转载自:http://www.blogjava.net/pengpenglin/archive/2008/06/29/211462.html
相关推荐
5 Oracle开发专题之:报表函数 6 Oracle开发专题之:分析函数总结 7 Oracle开发专题之:26个分析函数 8 分析函数简述">1 Oracle开发专题之:分析函数 OVER 2 Oracle开发专题之:分析函数 Rank Dense rank row ...
目录 Oracle开发专题之:分析函数(OVER) Oracle开发专题之:分析函数2(Rank, ...报表函数 Oracle开发专题之:分析函数总结 Oracle开发专题之:26个分析函数 PLSQL开发笔记和小结 分析函数简述
在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大,对实时响应的要求不高...
Oracle开发专题之:分析函数Oracle开发专题之:分析函数
oracle分析函数,窗口函数,报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)
ORACLE 报表分析 利剑——分析 函数
深入浅出Oracle_EBS之Excel报表开发.pdf
oracle函数介绍 1 著名函数之单值函数 pdf oracle函数介绍 2 非著名函数之单值函数 pdf oracle函数介绍 3 著名函数之聚合函数 pdf oracle函数介绍 4 非著名函数之聚合函数 pdf oracle函数介绍 5 分析函数简述 ...
一、回顾一下前面《Oracle开发之窗口函数》中关于全统计一节,我们使用了Oracle提供的: 代码如下:sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) 来统计...
Oracle 函数分类 :单行函数 分组函数 分析函数单行函数分为:日期函数 数字函数 字符函数 转换函数 其他函数分组函数分为:max(最大值) min(最小值) sum(求和) avg(平均) count(求个数) 分析函数分为:rank() 具有相同...
Oracle数据库开发之函数概述,包括经常使用的数值函数、字符函数、日期函数、转换函数等。
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....
oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全
Oracle开发的over函数
oracle之占比函数 oracle之占比函数 oracle之占比函数
oracle函数大全 oracle函数大全 oracle函数大全
Oracle分析函数——函数列表 SUM :该函数计算组中表达式的累积和 MIN :在一个组中的数据窗口中查找表达式的最小值 MAX :在一个组中的数据窗口中查找表达式的最大值 AVG :用于计算一个组和数据窗口内表达式的...
Oracle_plsql讲义:第3章 单行函数.ppt