Oracle分析函數OVER的用法

Oracle分析函數OVER使用方法舉例

---建立測試表
 
SQL> desc t_test;
  名稱                                      是否為空白? 類型
  ----------------------------------------- -------- --------------------------
 
 T_ID                                                      NUMBER
  T_GROUP                                            NUMBER
  T_NUM                                                  NUMBER
 

SQL> select * from t_test;
 
      T_ID    T_GROUP      T_NUM
 ---------- ---------- ----------
        23          1      5500
        23          1      6600
        25          1      4900
        26          3      5800
        27          3      4700
        28          6      6900
        29          6      7800
        30          8      5900
        30          8      6000
        32          8      6000
        33          8      7000
 

已選擇11行。

SQL> SELECT T_ID,T_GROUP,SUM(T_NUM) T_SUM,
  2    SUM(SUM(T_NUM)) OVER(PARTITION BY T_GROUP ) AGG_SUM
  3  FROM T_TEST T
  4  GROUP BY T_ID,T_GROUP;
 
      T_ID    T_GROUP      T_SUM    AGG_SUM
 ---------- ---------- ---------- ----------
        23          1      12100      17000
        25          1      4900      17000
        26          3      5800      10500
        27          3      4700      10500
        28          6      6900      14700
        29          6      7800      14700
        30          8      11900      24900
        32          8      6000      24900
        33          8      7000      24900
 
已選擇9行。

OVER分析函數詳解

上例中，是根據T_ID,T_GROUP來進行彙總，可以得到SUM(T_NUM)的值。
 
OVER函數的作用在於，在彙總後的結果集上，根據T_GROUP再次進行SUM(SUM(T_NUM))操作！

Use OVER analytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
 
Notes on the analytic_clause: The following notes apply to the analytic_clause:
 
    1.You cannot nest analytic functions by specifying any analytic function in any part of the analytic_clause. However, you can specify an analytic function in a subquery and compute another analytic function over it.
 
    2.You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions.
 

SUMMARY
 
1.Over函數指明在那些欄位上做分析，其內跟Partition by表示對資料進行分組。注意Partition by可以有多個欄位。
 
2.Over函數可以和其它聚集合函式、分析函數搭配，起到不同的作用。例如這裡的SUM，還有諸如Rank，Dense_rank等。