分析函数

ratio_to_report报表处理函数

除报告详细数据外，许多报告中还包括每行总数的百分比。例如，每名客户的订单相对于总订单的百分比，或每位销售代表的销售额相对于总销售额的百分比。

传统上，Oracle计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。

分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比. 这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null, 则得到的结果也为0.

 

开窗条件query_partition_clause决定被除数的值, 如果用户忽略了这个条件, 则计算查询结果中所有记录的汇总值.

 

用户不能使用其他分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr, 也就是说这个函数不能循环使用. 但我们可以使用其他普通函数作为这个分析函数的查询结果.

 

RATIO_TO_REPORT解析函数使得这种类型的查询更容易编码。它的格式如下：

RATIO_TO_REPORT (expr) OVER(query_partition_clause)

 

[sql]  view plain copy

1. /* FORMATTEDON 2009/11/10 20:24 (FORMATTER PLUS V4.8.8) */  
2. SELECT ENAME, SAL, DEPTNO, RATIO_TO_REPORT (SAL) OVER () AS RR  
3.  FROM SCOTT.EMP  
4.  WHERE DEPTNO = 10;  

 

ENAME	SAL	DEPTNO	RR
CLARK	2,450	10	0.28
KING	5,000	10	0.571428571428571
MILLER	1,300	10	0.148571428571429

 

[sql]  view plain copy

1. /* FORMATTEDON 2009/11/10 20:36 (FORMATTER PLUS V4.8.8) */  
2. SELECT ENAME, SAL, DEPTNO,  
3.        RATIO_TO_REPORT (SAL) OVER (PARTITION BY DEPTNO) AREA_PCT  
4.  FROM SCOTT.EMP;  

ENAME	SAL	DEPTNO	AREA_PCT
CLARK	2,450.0000000000	10	0.2800000000
KING	5,000.0000000000	10	0.5714285714
MILLER	1,300.0000000000	10	0.1485714286
JONES	2,975.0000000000	20	0.2735632184
FORD	3,000.0000000000	20	0.2758620690
ADAMS	1,100.0000000000	20	0.1011494253
SMITH	800.0000000000	20	0.0735632184
SCOTT	3,000.0000000000	20	0.2758620690
WARD	1,250.0000000000	30	0.1329787234
TURNER	1,500.0000000000	30	0.1595744681
ALLEN	1,600.0000000000	30	0.1702127660
JAMES	950.0000000000	30	0.1010638298
BLAKE	2,850.0000000000	30	0.3031914894
MARTIN	1,250.0000000000	30	0.1329787234

 

 

First,Last，First_value，Last_value取基数的分析函数

    First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
    Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

     FIRST_VALUE、LAST_VALUE：返回结果集中排在第一位和最后一位的值。

     语法是：FIRST_VALUE (expr) OVER ( analytic_clause)

[sql]  view plain copy

1. /* FORMATTEDON 2009/11/10 20:51 (FORMATTER PLUS V4.8.8) */  
2. SELECT ENAME, DEPTNO, SAL,  
3.        MIN (SAL)KEEP (DENSE_RANK FIRST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO)  "Worst",  
4.        MAX (SAL)KEEP (DENSE_RANK LAST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO) "Best"  
5.   FROM SCOTT.EMP;