Oracle progressive cumulative method (OVER function) (Problem Description: For example, the query record has 5 lines, each line record has a numeric field. The second behavior 1, 2 lines; third behavior 1 , 2, 3 lines; the fourth behavior of the first, 2, 3, 4 lines; followed by such push ...)

oracle progressive cumulative method (OVER function)

 

Sql over the role and usage
1.RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 
Can be sorted by specified field grouping, sorting the result set of the same packet field,
Where partition By is a group field, ordery by specifies the order field

2.Over cannot be used separately, and is used with analysis functions: rank (), Dense_Rank (), Row_Number (), etc..
Its parameters: over (partition by columnname1 order by columnname2)
Meaning: Packet sorting by the field specified by Columname1, or packet sorting according to the value of the field columnname1.

Solve practical problems: (Implement statistics)
1) Problem Description: For example, the query record has 5 lines, and each line record has a numeric field. The second behavior of the first and second lines; the third behavior of the first, 2, 3 lines; the fourth behavior of the first, 2, 3, 4 lines; the back is in this type …
2) Workaround: Use Oracle’s own Over function.
As described below:
1. Construction test table EMP
– CREATE TABLE
create table employee 

DEPTNO NUMBER(4), 
ENAME VARCHAR2(20), 
SAL NUMBER(10) 
); 
2. Insert test data
Insert Into Employee (Deptno, Ename, SAL) VALUES (0001, ‘CLARK’, 2450);
Insert Into Employee (Deptno, Ename, SAL) VALUES (0002, ‘Smith’, 3000);
INSERT INTO Employee (Deptno, Ename, SAL) VALUES (0003, ‘Allen “, 1250);
insert Into Employee (Deptno, Ename, Sal) Values ​​(0004, ‘James’, 950);
The results of the query are as follows:
这里写图片描述

3. Write SQL (with over function)
select deptno, 
sal, 
sum(sal) over (order by deptno) AccSal 
from employee

The results of the query are as follows:
这里写图片描述