Oracle分析函数OVER的用法

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等。