oracle over()函数及ratio_to_report()函数学习

今儿要用到一个统计查询，自己写了个，给经理一看，say No,子查询太多，影响效率，下午扔给了我一个sql，顿时懵了，啥玩意啊，没见过，好吧，百度一下，哈哈

SELECT A.EVALUATION,
       COUNT(A.REC_ID),
       RATIO_TO_REPORT(COUNT(A.REC_ID)) OVER()
  FROM ECM_ORDER_GOODS A, ECM_ORDER B
 WHERE A.ORDER_ID = B.ORDER_ID
   AND B.STORE_ID = '1'
 GROUP BY A.EVALUATION

先把sql贴出来，再慢慢研究哈。。

主要涉及到2个函数，主要理解ver()函数

        一般是在做统计的时候与统计函数一块使用，官方的解释没找到，贴两个例子(来源网络)，自己慢慢读理解，你们感受一下

SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和,             -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)" --同上
5 from emp
6 /

    DEPTNO ENAME             SAL   连续求和       总和    份额(%)
---------- ---------- ---------- ---------- ---------- ----------
        20 ADAMS            1100       1100      29025       3.79
        30 ALLEN            1600       2700      29025       5.51
        30 BLAKE            2850       5550      29025       9.82
        10 CLARK            2450       8000      29025       8.44
        20 FORD             3000      11000      29025      10.34
        30 JAMES             950      11950      29025       3.27
        20 JONES            2975      14925      29025      10.25
        10 KING             5000      19925      29025      17.23
        30 MARTIN           1250      21175      29025       4.31
        10 MILLER           1300      22475      29025       4.48
        20 SCOTT            3000      25475      29025      10.34
        20 SMITH             800      26275      29025       2.76
        30 TURNER           1500      27775      29025       5.17
        30 WARD             1250      29025      29025       4.31

已选择14行。

主要对比 “连续求和” 和 “总和” 两列 ！

下面还有一个例子，你们再感受一下

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /

DEPTNO ENAME    SAL 部门连续求和   部门总和 部门份额(%)   连续求和   总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
    10 CLARK   2450         2450       8750          28       2450   29025       8.44
           KING    5000         7450       8750       57.14       7450   29025      17.23
       MILLER    1300         8750       8750       14.86       8750   29025       4.48

    20 ADAMS   1100         1100     10875       10.11       9850   29025       3.79
          FORD    3000         4100      10875       27.59      12850   29025      10.34
         JONES   2975         7075      10875       27.36      15825   29025      10.25
         SCOTT   3000        10075      10875       27.59      18825   29025      10.34
          SMITH    800        10875      10875        7.36      19625   29025       2.76

    30 ALLEN   1600         1600       9400       17.02      21225   29025       5.51
       BLAKE   2850         4450       9400       30.32      24075   29025       9.82
       JAMES    950         5400       9400       10.11      25025   29025       3.27
       MARTIN 1250         6650       9400        13.3      26275   29025       4.31
       TURNER 1500         8150       9400       15.96      27775   29025       5.17
       WARD    1250         9400       9400        13.3      29025   29025       4.31


已选择14行。

主要对比 “部门连续求和” 与 “部门总和” 以及 “连续求和” 与 “总和”  以及  “部门份额” 和 "总份额" 之间的关系，主要在于over()函数里面order by和partition by的使用

有order by的就是连续求和。当然partition by是用来分组显示！最外层的循环order by不能随便加，在里面用partition by分组就可以，当然分组规则一样也行！

 

 

RATIO_TO_REPORT

 

Oracl 8i中引入的该函数，格式如下：
RATIO_TO_REPORT (expr) OVER(query_partition_clause)
Oracle计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。你还可以用一个子查询作为SELECT语句表达式：
RATIO_TO_REPORT解析函数使得这种类型的查询更容易编码。

 

还是贴两个网上的实例，大家一块感受一下

 

SELECT

   sales_rep_id, customer_id, order_total,

   ratio_to_report(order_total) OVER () pct_total

FROM

   orders

WHERE

   sales_rep_id = 163

ORDER BY

   sales_rep_id, customer_id, order_id

/

SQL> @ratioreport_a

SALES_REP_ID CUSTOMER_ID ORDER_TOTAL  PCT_TOTAL                                 

------------ ----------- ----------- ----------                                 

         163         102      5610.6 .043747539                                 

         163         102       10523 .082051002                                 

         163         103          78  .00060819                                 

         163         103       13550 .105653433                                 

         163         105      1926.6 .015022281                                 

         163         106      5546.6 .043248512                                 

         163         117      3878.4 .030241054                                 

         163         147      1500.8  .01170219                                 

         163         149        9055 .070604564                                 

         163         156       68501  .53412294                                 

         163         157      7110.3 .055441152                                 

         163         160       969.2 .007557144                                 

12 rows selected.

SQL>

 

col order_total format 999,999.00

 

col pct_total format 999.00

SELECT

   sales_rep_id, 

   customer_id, 

   order_total,

   ROUND(100*ratio_to_report(order_total) 

      OVER (PARTITION BY customer_id),2) pct_total --注意上面讲到的over()函数的用法，以customer_id分组

FROM

   orders

WHERE

   sales_rep_id = 163

ORDER BY

   sales_rep_id, customer_id, order_id/

SQL> @ratioreport_b

SALES_REP_ID CUSTOMER_ID ORDER_TOTAL PCT_TOTAL                                  

------------ ----------- ----------- ---------                                  

         163         102    5,610.60     34.78                                  

         163         102   10,523.00     65.22                                  

         163         103       78.00       .57                                  

         163         103   13,550.00     99.43                                  

         163         105    1,926.60    100.00                                  

         163         106    5,546.60    100.00                                  

         163         117    3,878.40    100.00                                  

         163         147    1,500.80    100.00                                  

         163         149    9,055.00    100.00                                  

         163         156   68,501.00    100.00                                  

         163         157    7,110.30    100.00                                  

         163         160      969.20    100.00                                  

12 rows selected.

SQL> spool off