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
3 FROM T_TEST T
4
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等。