Oracle Reporting 1 - Ratio_to_Report Function
来源:互联网 发布:as3与js交互 编辑:程序博客网 时间:2024/04/30 15:50
The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] ).
In this series of articles, I'm using Oracle's SH schema. It is modeled in star schema. The following query returns top 3 best-selling products in 2000, their sales amount, percentage of total amount, and the rank.
Ratio_to_report() Performance Analysis:
Consider this query:
Statistics
----------------------------------------------------------
2 recursive calls
8 db block gets
1730 consistent gets
1 physical reads
864 redo size
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1721 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
In this series of articles, I'm using Oracle's SH schema. It is modeled in star schema. The following query returns top 3 best-selling products in 2000, their sales amount, percentage of total amount, and the rank.
select * from (SELECT prod.prod_name, TO_CHAR(SUM(amount_sold),'9,999,999,999') "SALES($)",to_char(RATIO_TO_REPORT(SUM(amount_sold)) OVER () * 100, '99.99' ) || '%' Percentage_of_Taotal_Amount,rank() over(order by sum(amount_sold) desc) rankFROM sales s, products prodWHERE s.prod_id=prod.prod_id AND trunc(s.time_id,'yyyy')=to_DATE('01-JAN-2000')GROUP BY (prod.prod_name))where rank < br style='font-size:12px;font-style:normal;font-weight:normal;color:rgb(102, 102, 102);'
Ratio_to_report() Performance Analysis:
Consider this query:
WITH channel_summary AS ( SELECT channels.channel_desc, SUM(amount_sold) AS channel_total FROM sales, channels WHERE sales.channel_id = channels.channel_id GROUP BY channels.channel_desc) SELECT channel_desc, channel_total FROM channel_summary WHERE channel_total < (SELECT SUM(channel_total) * 1/3 FROM channel_summary);
Statistics
----------------------------------------------------------
2 recursive calls
8 db block gets
1730 consistent gets
1 physical reads
864 redo size
622 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Oracle needs to use temp tablespace to save the data of with-clause, that's the reason of 2 recursive calls and 864 redo size. However, we can eliminate the writes to/reads from temp tablespace by using ratio_to_report.
Both queries run twice to avoid the effect of hard-parse.
Both queries run twice to avoid the effect of hard-parse.
SELECT channel_desc, amount_sold FROM ( SELECT ch.channel_desc, sum(s.amount_sold) amount_sold, ratio_to_report(sum(s.amount_sold)) over() ratio FROM channels ch, sales s WHERE ch.channel_id=s.channel_id GROUP BY ch.channel_desc) WHERE ratio <= 0.33;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1721 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
0 0
- Oracle Reporting 1 - Ratio_to_Report Function
- Reporting Function
- oracle分析函数Ratio_to_report使用说明
- oracle ratio_to_report 占比函数
- oracle ratio_to_report 占比函数
- ratio_to_report
- Oracle占比函数: oracle ratio_to_report
- ORACLE 分析函数ratio_to_report的使用
- 用Oracle RATIO_TO_REPORT计算总数百分比
- oracle over()函数及ratio_to_report()函数学习
- Oracle分析函数之ratio_to_report()详解
- oracle 求男女比例 sql 函数ratio_to_report
- Oracle SQL ratio_to_report函数、占比统计
- Oracle Reporting 5 - Windowing
- Oracle Reporting 6 - Model
- oracle分析函数系列之ratio_to_report:计算占总数百分比
- oracle 求占百分比的分析函数 ratio_to_report() over();
- ORACLE实用函数之一 ratio_to_report的简单使用
- inuxDVB 在CA系统上的原理和实践
- Export with Spool and Parallel Utl_File
- 实现接口回调
- DWZ (JUI) 教程 DWZ LookUp Suggest 教程
- android下载项目程序导入eclipse报错(如项目显示感叹号等)解决方案
- Oracle Reporting 1 - Ratio_to_Report Function
- Android文档学习09_多媒体2
- DWZ (JUI) 教程 DWZ table 分页排序教程
- 入门HTML之2.08
- sicily 1020 大数求模
- Oracle Reporting 2 - Subtotals and Grand Total
- DWZ (JUI) 教程 修正 Tab 选项卡多次加载
- RegistryKey类
- DWZ (JUI) 教程 主题切换原理