Oracle's analytic functions over

Oracle Analytics function over is widely used.

Used to calculate a group-based aggregate value, which differs from aggregate functions in that multiple rows are returned for each group, whereas aggregate functions return only one row for each group.

Often with Sum,rank and other function colleagues appear.

Example:

SOCTT's EMP table is an example: you need to get the total SAL for each department. The

SELECT empno, ename, Deptno,sum (SAL) over (PARTITION by Deptno) as Deptpn_sal from EMP

Results

1 7782 CLARK 10 8750
27839king108750
37934miller108750
47566jones2010875
57902ford2010875
67876adams2010875
77369smith2010875
87788scott2010875
97521ward309400
107844turner309400
117499allen309400
127900james309400
137698blake309400
147654martin309400

If you need to accumulate by person ID, you

SELECT empno, ename, Deptno,sum (SAL) over (PARTITION by Deptno ORDER by Empno) as Deptpn_sal from EMP

The results are as follows:

1 7782 CLARK 10 2450
27839king107450
37934miller108750
47369smith20800
57566jones203775
67788scott206775
77876adams207875
87902ford2010875
97499allen301600
107521ward302850
117654martin304100
127698blake306950
137844turner308450
147900james309400

Rank shows for example the next

SELECT empno, ename, Deptno, Sal,rank () over (PARTITION by Deptno ORDER by Sal) as Deptpn_sal from EMP

Results

1 7934 MILLER 10 1300.00 1
27782clark102450.002
37839king105000.003
47369smith20800.001
57876adams201100.002
67566jones202975.003
77788scott203000.004
87902ford203000.004
97900james30950.001
107654martin301250.002
117521ward301250.002
127844turner301500.004
137499allen301600.005
147698blake302850.006

Here's the difference between rank and row_number and Dense_rank.

① Row_number :

The Row_number function returns a unique value that, when the same data is encountered, is incremented sequentially in the order in which the records are recorded in the recordset.

② Dense_rank :
The Dense_rank function returns a unique value unless all the same data is ranked the same at the time when the same data is encountered.

③ RANK :
The Rank function returns a unique value, except that when the same data is encountered, all the same data is ranked the same at the same time, and the rank between the last same record and the next record rank is vacated.

Oracle's analytic functions over