Oracle analysis function OVER usage

Example of Oracle analysis function OVER

--- Create a test table
 
SQL> desc t_test;
Is the name empty? Type
---------------------------------------------------------------------------
 
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 rows have been selected.

SQL> SELECT T_ID, T_GROUP, SUM (T_NUM) T_SUM,
2 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
 
Nine rows have been selected.

OVER analysis functions

In the preceding example, the aggregate is based on T_ID and T_GROUP to obtain the value of SUM (T_NUM.
 
The OVER function is used to perform the SUM (T_NUM) operation again based on T_GROUP In the aggregated result set!

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 clses. 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. The Over function indicates the fields to be analyzed. The Partition by function indicates that data is grouped. Note that Partition by can have multiple fields.
 
2. The Over function can be used together with other Aggregate functions and analysis functions to play different roles. For example, SUM here also includes Rank and Dense_rank.