分析函数

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 plaincopy

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 plaincopy

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 plaincopy

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;