Oracle開發專題之：分析函式的使用

分析函式是oracle816引入的一個全新的概念,為我們分析資料提供了一種簡單高效的處理方式.在分析函式出現以前,我們必須使用自聯查詢,子查詢或者內聯檢視,甚至複雜的儲存過程實現的語句,現在只要一條簡單的sql語句就可以實現了,而且在執行效率方面也有相當大的提高.下面我將針對分析函式做一些具體的說明.基礎資料

除本文內容外，你還可參考：
ROLLUP與CUBE http://xsb.itpub.net/post/419/29159
分析函式參考手冊：http://xsb.itpub.net/post/419/33028

今天我主要給大家介紹一下以下幾個函式的使用方法
1. 自動彙總函式rollup,cube,
2. rank 函式, rank,dense_rank,row_number
3. lag,lead函式
4. sum,avg,max,count 的移動增加,移動平均數
5. ratio_to_report報表處理函式
6. first,last取基數的分析函式

1. 使用rollup函式的介紹

    Oracle的GROUP BY語句除了最基本的語法外，還支援ROLLUP和CUBE語句。如果是ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，

然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，

則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。

 grouping_id()可以美化效果：

===========================================================================
(1). rollup 配合goup by 命令使用，可以提供資訊彙總功能（類似於"小計"）  
 
  下面是一個簡單例子：  
   
  SQL>   select   job,deptno,sal   from   emp;  
   
  JOB                     DEPTNO               SAL  
  ---------                    ---------            ---------  
  CLERK                          20              800  
  SALESMAN                   30             1600  
  SALESMAN                   30             1250  
  MANAGER                    20             2975  
  SALESMAN                   30             1250  
  MANAGER                    30             2850  
  MANAGER                    10             2450  
  ANALYST                      20             3000  
  PRESIDENT                  10             5000  
  SALESMAN                   30             1500  
  CLERK                         20             1100  
  CLERK                        30              950  
  ANALYST                    20             3000  
  CLERK                       10             1300  
   
  已選擇14行。  
   
  SQL>   select job,deptno,sum(sal)  total_sal   from   emp   group   by   rollup(job,deptno);  
   
  JOB                         DEPTNO     TOTAL_SAL  
  ---------                        ---------              ---------  
  ANALYST                       20             6000  
  ANALYST                                       6000  
  CLERK                         10              1300  
  CLERK                         20              1900  
  CLERK                         30               950  
  CLERK                                            4150  
  MANAGER                   10               2450  
  MANAGER                    20               2975  
  MANAGER                    30               2850  
  MANAGER                                        8275  
  PRESIDENT                     10             5000  
  PRESIDENT                                      5000  
  SALESMAN                      30             5600  
  SALESMAN                                     5600  
                                                      29025  
   
  已選擇15行。  

文章出處：http://www.diybl.com/course/7_databases/oracle/oraclejs/2008224/101034.html

2
========================================================================
  rollup

  表示的意思是：除了分組的功能外，還進行累加的的，多了一個彙總，比如：

SQL>  select a,sum(b) from test group by a;

         A         SUM(B)
----------           ----------
         0              444
         1          40000
        12            128
       324              4
      1010          413
      1111      40000
     12345      40000
    123456
1.1111E+11          4

已選擇9行。

SQL> select a,sum(b) from test group by rollup (a);

         A     SUM(B)
----------         ----------
         0           444
         1        40000
        12          128
       324             4
      1010         413
      1111      40000
     12345      40000
    123456
1.1111E+11          4
                  120993

已選擇10行。

而cube提供了按照多欄位彙總的功能，希望下面的例子能給你一些提示：

SQL> select a,b,sum(c) from aa group by rollup(a,b);

         A          B     SUM(C)
---------- ---------- ----------
         1          1          2
         1                     2
         2          2          6
         2                     6
         3          3         15
         3                    15
                              23

已選擇7行。

SQL> select a,b,sum(c) from aa group by cube(a,b);

         A          B     SUM(C)
---------- ---------- ----------
         1          1          2
         1                     2
         2          2          6
         2                     6
         3          3         15
         3                    15
                    1          2
                    2          6
                    3         15
                              23

已選擇10行。

SQL> select  * from aa;

         A          B          C
---------- ---------- ----------
         1          1          1
         1          1          1
         2          2          2
         2          2          2
         2          2          2
         3          3          3
         3          3          3
         3          3          3
         3          3          3
         3          3          3

已選擇10行。

SQL> select grouping(a),grouping(b),sum(c) from aa group by cube(a,b);

GROUPING(A) GROUPING(B)     SUM(C)
----------- ----------- ----------
          0           0          2
          0           1          2
          0           0          6
          0           1          6
          0           0         15
          0           1         15
          1           0          2
          1           0          6
          1           0         15
          1           1         23

已選擇10行。

使用 GROUPING 區分空值:

    CUBE 操作所生成的空值帶來一個問題：如何區分 CUBE 操作所生成的 NULL 值和從實際資料中返回的 NULL 值？

這個問題可用 GROUPING 函式解決。如果列中的值來自事實資料，則 GROUPING 函式返回 0；如果列中的值是 CUBE 操作所生成的 NULL，

則返回 1。在 CUBE 操作中，所生成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函式將所生成的 NULL 替換為字串 ALL。

因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可譯碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。

 

 

============================================================
**********************************************************************************************
===========================================================

用 CUBE 彙總資料

例如，一個簡單的表 Inventory 中包含：

Item                 Color                Quantity                 
-------------------- -------------------- ----------
Table                Blue                 124                      
Table                Red                  223                      
Chair                Blue                 101                      
Chair                Red                  210                      

下列查詢返回的結果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

下面是結果集：

Item                 Color                QtySum                   
-------------------- -------------------- ---------
Chair                Blue                 101.00                   
Chair                Red                  210.00                   
Chair                (null)               311.00                   
Table                Blue                 124.00                   
Table                Red                  223.00                   
Table                (null)               347.00                   
(null)               (null)               658.00                   
(null)               Blue                 225.00                   
(null)               Red                  433.00                   

我們著重考查下列各行：

Chair                (null)               311.00                   

這一行報告了 Item 維度中值為 Chair 的所有行的小計。對 Color 維度返回了 NULL 值，表示該行所報告的聚合包括 Color 維度為任意值的行。

Table                (null)               347.00                   

這一行類似，但報告的是 Item 維度中值為 Table 的所有行的小計。

(null)               (null)               658.00                   

這一行報告了多維資料集的總計。Item 和 Color 維度的值都是 NULL，表示兩個維度中的所有值都彙總在該行中。

(null)               Blue                 225.00                   
(null)               Red                  433.00                   

這兩行報告了 Color 維度的小計。兩行中的 Item 維度值都是 NULL，表示聚合資料來自 Item 維度為任意值的行。

 

使用 GROUPING 區分空值:

CUBE 操作所生成的空值帶來一個問題：如何區分 CUBE 操作所生成的 NULL 值和從實際資料中返回的 NULL 值？這個問題可用 GROUPING 函式

解決。如果列中的值來自事實資料，則 GROUPING 函式返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。在 CUBE 操作中，所生

成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函式將所生成的 NULL 替換為字串 ALL。因為事實資料中的 NULL 表明資料

值未知，所以 SELECT 語句還可譯碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。例如：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

 

多維資料集
CUBE 運算子可用於生成 n 維的多維資料集，即具有任意數目維度的多維資料集。只有一個維度的多維資料集可用於生成合計，例如：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO

此 SELECT 語句返回的結果集既顯示了 Item 中每個值的小計，也顯示了 Item 中所有值的總計：

Item                 QtySum                   
-------------------- ----------
Chair                311.00                   
Table                347.00                   
ALL                  658.00                   

 

包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結果集，因為這些語句會為所有維度中值的所有組合生成行。這些大結果集包含的資料可能過多而不易於閱讀和理解。

這個問題有一種解決辦法是將 SELECT 語句放在檢視中：

CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

然後即可用該檢視來只查詢您感興趣的維度值：

SELECT *
FROM InvCube
WHERE Item = 'Chair'
  AND Color = 'ALL'

Item                 Color                QtySum                   
-------------------- -------------------- ---------
Chair                ALL                  311.00                   

(1 row(s) affected)

 

用 ROLLUP 彙總資料在生成包含小計和合計的報表時，ROLLUP 運算子很有用。ROLLUP 運算子生成的結果集類似於 CUBE 運算子所生成的結果集。有關更多資訊，請參見用 CUBE 彙總資料。

CUBE 和 ROLLUP 之間的區別在於： CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合。 例如，簡單表 Inventory 中包含：

Item                Color                Quantity                 
-------------------- -------------------- ------
Table                Blue                 124                      
Table                Red                  223                      
Chair                Blue                 101                      
Chair                Red                  210                      

下列查詢將生成小計報表：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                   
-------------------- -------------------- ------
Chair                Blue                 101.00                   
Chair                Red                  210.00                   
Chair                ALL                  311.00                   
Table                Blue                 124.00                   
Table                Red                  223.00                   
Table                ALL                  347.00                   
ALL                  ALL                  658.00                   

(7 row(s) affected)

如果查詢中的 ROLLUP 關鍵字更改為 CUBE，那麼 CUBE 結果集與上述結果相同，只是在結果集的末尾還會返回下列兩行：
ALL                  Blue                 225.00                   
ALL                  Red                  433.00  
                
CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合

（Red、Blue 和 Red + Blue），而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。

對於 GROUP BY 子句中右邊的列中的每個值，ROLLUP 操作並不報告左邊一列（或左邊各列）中值的所有可能組合。

例如，ROLLUP 並不對每個 Color 值報告 Item 值的所有可能組合。ROLLUP 操作的結果集具有類似於 COMPUTE BY 所返回結果集的功能；

然而，ROLLUP 具有下列優點： ROLLUP 返回單個結果集；COMPUTE BY 返回多個結果集，而多個結果集會增加應用程式程式碼的複雜性。

ROLLUP 可以在伺服器遊標中使用；COMPUTE BY 不可以。有時，查詢優化器為 ROLLUP 生成的執行計劃比為 COMPUTE BY 生成的更為高效。

 

============================================================
**********************************************************************************************
===========================================================

1.報表合計專用的Rollup函式

銷售報表

廣州 1月 2000元

廣州 2月 2500元

廣州     4500元

深圳 1月 1000元

深圳 2月 2000元

深圳     3000元

所有地區 7500元

 

以往的查詢SQL:

Select area,month,sum(money) from SaleOrder group by area,month

然後廣州，深圳的合計和所有地區合計都需要在程式裡自行累計

1.其實可以使用如下SQL:

Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)

就能產生和報表一模一樣的紀錄

2.如果year不想累加，可以寫成

Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)

另外Oracle 9i還支援如下語法:

Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)

3.如果使用Cube(area,month)而不是RollUp(area,month)，除了獲得每個地區的合計之外，還將獲得每個月份的合計，在報表最後顯示。

4.Grouping讓合計列更好讀

RollUp在顯示廣州合計時，月份列為NULL，但更好的做法應該是顯示為"所有月份"

Grouping就是用來判斷當前Column是否是一個合計列，1為yes，然後用Decode把它轉為"所有月份"

Select Decode(Grouping(area),1,'所有地區',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);

 

2. rank 函式, rank,dense_rank,row_number

 

首先,要了解rank在英語的意思:等級.也就是說這是一個給資料確定等級的函式.

以銷售為例,有地區,年,月,銷售員,銷售額,記錄這五個欄位.我們可以按地區,年,月,銷售額對銷售員進行排序,這樣對銷售員來說就相當於有一個等級概念了,第一名就是銷售最高的......,如果我們要找出每個地區,年,月,銷售額的前三名銷售員.SQL如何寫?

現在RANK 就是1,2,3,3,3,6,有了這個欄位,就很容易得到前三名的銷售員了.

新問題:銷售額50000塊在深圳,2007年5月能排到第幾?

3. lag,lead函式

使用LAG和LEAD函式統計

Lag和Lead函式可以在一次查詢中取出同一欄位的前N行的資料和後N行的值。這種操作可以使用對相同表的表連線來實現，

不過使用LAG和LEAD有更高的效率。以下是LAG和LEAD的例子：

SQL> select year,region,profit,lag (profit,1) over(order by year) as last_year_exp from test;

YEAR REGION      PROFIT LAST_YEAR_EXP
---- ------- ---------- -------------
2003 West            88

2003 West            88            88

2003 Central        101            88

2003 Central        100           101

2003 East           102           100

2004 West            77           102

2004 East           103            77

2004 West            89           103

 

SQL> select  year,region,profit ,lead (profit,1) over  (order by year)  as next_year_exp from test;

YEAR REGION      PROFIT NEXT_YEAR_EXP
---- ------- ---------- -------------
2003 West            88            88

2003 West            88           101

2003 Central        101           100

2003 Central        100           102

2003 East           102            77

2004 West            77           103

2004 East           103            89

2004 West            89

Lag函式為Lag(exp,N,defval)，defval是當該函式無值可用的情況下返回的值。Lead函式的用法類似。

Lead和Lag函式也可以使用分組，以下是使用region分組的例子：

SQL> select  year,region,profit,lag (profit,1,0) over (PARTITION BY region order by year) as last_year_exp from test;

 

YEAR REGION      PROFIT LAST_YEAR_EXP
---- ------- ---------- -------------
2003 Central        101             0

2003 Central        100           101

2003 East           102             0

2004 East           103           102

2003 West            88             0

2003 West            88            88

2004 West            77            88

2004 West            89            77

 

========================================================
還可以進一步統計一下兩者的相差天數

select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from (
 select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,
        lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,
        lag(stepid) over (partition by caseid order by actiondate) prestepid,
               lag(actiondate) over (partition by caseid order by actiondate) preactiondate
        from lead_table)
結果如下：

Case1    Step1    2007-1-1    2007-1-2    1
Case1    Step2    2007-1-2    2007-1-3    1
Case1    Step3    2007-1-3    2007-1-4    1
Case1    Step4    2007-1-4    2007-1-5    1
Case1    Step5    2007-1-5    2007-1-6    1
Case1    Step4    2007-1-6    2007-1-7    1
Case1    Step6    2007-1-7       
Case2    Step1    2007-2-1    2007-2-2    1
Case2    Step2    2007-2-2    2007-2-3    1
Case2    Step3    2007-2-3       

4. sum,avg,的移動增加,移動平均數

 

SUM
==========================================================================
SELECT SUM(salary) "Total"
     FROM employees;
 
     Total
----------
    691400

 

SELECT manager_id, last_name, salary,
   SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
   RANGE UNBOUNDED PRECEDING) l_csum
   FROM employees;

MANAGER_ID LAST_NAME           SALARY     L_CSUM
---------- --------------- ---------- ----------
       100 Mourgos               5800       5800
       100 Vollman               6500      12300
       100 Kaufling              7900      20200
       100 Weiss                 8000      28200
       100 Fripp                 8200      36400
       100 Zlotkey              10500      46900
       100 Raphaely             11000      68900
       100 Cambrault            11000      68900
       100 Errazuriz            12000      80900
.
.
.
       149 Taylor                8600      30200
       149 Hutton                8800      39000
       149 Abel                 11000      50000
       201 Fay                   6000       6000
       205 Gietz                 8300       8300
           King                 24000      24000

AVG
=======================================================================
SELECT AVG(salary) "Average" FROM employees;

 Average
--------
    6425

SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      15000
       100 Raphaely                  07-DEC-94      11000 11966.6667
       100 Kaufling                  01-MAY-95       7900 10633.3333
       100 Hartstein                 17-FEB-96      13000 9633.33333
       100 Weiss                     18-JUL-96       8000 11666.6667
       100 Russell                   01-OCT-96      14000 11833.3333
.
.
.

Count
==========================================================================
SELECT COUNT(*) "Total" FROM employees;

     Total
----------
       107

SELECT last_name, salary,
   COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count FROM employees;

LAST_NAME                     SALARY  MOV_COUNT
------------------------- ---------- ----------
Olson                           2100          3
Markle                          2200          2
Philtanker                      2200          2
Landry                          2400          8
Gee                             2400          8
Colmenares                      2500         10
Patel                           2500         10
.
.
.

Max
==============================================================================
SELECT MAX(salary) "Maximum" FROM employees;
 
   Maximum
----------
      24000

SELECT manager_id, last_name, salary,
   MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
   FROM employees;

MANAGER_ID LAST_NAME                     SALARY    MGR_MAX
---------- ------------------------- ---------- ----------
       100 Kochhar                        17000      17000
       100 De Haan                        17000      17000
       100 Raphaely                       11000      17000
       100 Kaufling                        7900      17000
       100 Fripp                           8200      17000
       100 Weiss                           8000      17000
...

 

SELECT manager_id, last_name, salary
   FROM (SELECT manager_id, last_name, salary,
      MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
      FROM employees) WHERE salary = rmax_sal;

MANAGER_ID LAST_NAME                     SALARY
---------- ------------------------- ----------
       100 Kochhar                        17000
       100 De Haan                        17000
       101 Greenberg                      12000
       101 Higgens                        12000
       102 Hunold                          9000
       103 Ernst                           6000
       108 Faviet                          9000
       114 Khoo                            3100
       120 Nayer                           3200
       120 Taylor                          3200
       121 Sarchand                        4200
       122 Chung                           3800
       123 Bell                            4000
       124 Rajs                            3500
       145 Tucker                         10000
       146 King                           10000
       147 Vishney                        10500
       148 Ozer                           11500
       149 Abel                           11000
       201 Goyal                           6000
       205 Gietz                           8300
           King                           24000

 

5. ratio_to_report報表處理函式

 

資料庫如何用Oracle RATIO_TO_REPORT計算總數百分比

除報告詳細資料外，許多報告中還包括每行總數的百分比。例如，每名客戶的訂單相對於總訂單的百分比，或每位銷售代表的銷售額相對於總銷售額的百分比。

傳統上，Oracle計算百分比的方法是在總計報告的子查詢中使用SUM函式總計報告，然後把那個結果放到細節表中相除來計算百分比。你還可以用一個子查詢作為SELECT語句表示式：

RATIO_TO_REPORT解析函式使得這種型別的查詢更容易編碼。Oracle 8i中引入了這個函式，它的格式如下：

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

  列表A說明一個簡單的例子。對163號銷售代表來說，每位客戶的銷售額佔這名銷售代表的總銷售額的百分比是多少呢？在這種情況下，查詢分割槽子句（partition clause）為空，因此得到的結果是對返回的所有行計算得出。

  列表B增加了一個查詢分割槽子句，進一步按客戶細分報告。PARTITION BY customer_id子句重新安排每位客戶的總數，進而說明每名客戶的哪次訂單最為關鍵。你可以檢視客戶102的情況，他的兩個訂單相對平衡；

但客戶103的一個訂單佔這名客戶總訂單額的很大一部分。

Listing A

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.

===========================================================

Listing B

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
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.

 

===============================================================

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
   FROM employees
   WHERE job_id = 'PU_CLERK';

LAST_NAME                     SALARY         RR
------------------------- ---------- ----------
Khoo                            3100 .223021583
Baida                           2900 .208633094
Tobias                          2800 .201438849
Himuro                          2600  .18705036
Colmenares                      2500 .179856115

 

6. first,last取基數的分析函式

 

SELECT last_name, department_id, salary,
   MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Worst",
   MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Best"
    FROM employees
    ORDER BY department_id, salary;

LAST_NAME           DEPARTMENT_ID     SALARY      Worst       Best
------------------- ------------- ---------- ---------- ----------
Whalen                         10       4400       4400       4400
Fay                            20       6000       6000      13000
Hartstein                      20      13000       6000      13000
.
.
.
Gietz                         110       8300       8300      12000
Higgins                       110      12000       8300      12000
Grant                                   7000       7000       7000