Oracle over () function usage

The following articles mainly introduce the actual usage of the Oracle over () function. on a website, I accidentally saw this function to understand the content of the Oracle analysis function, in addition, the actual usage of the Oracle over () function is particularly special, so record your own research results.

In my understanding, the over () function is a condition interpretation of the analysis function. The direct point is to add conditions to the analysis function.

We can see on the Internet that it is commonly used with sum () and rank () functions. Next, we will use the two functions in combination with the over usage.

The following test uses the emp table data of the scott user by default in the example E.

1. sum () combined with Oracle over ()

SQL code

Select a. empno as employee ID

, A. ename as employee name

, A. deptno as department no.

, A. sal as salary

, Sum (sal) over (partition by deptno) calculates the total salary by department

From scott. emp;

The SQL statement execution result is:

Department No. employee name employee code salary calculated by Department total salary

7934 MILLER 10 1300 8750

7782 CLARK 10 2450 8750

7839 KING 10 5000 8750

7369 SMITH 20 800 10875

7876 ADAMS 20 1100 10875

7566 JONES 20 2975 10875

7788 SCOTT 20 3000 10875

7902 FORD 20 3000 10875

7900 JAMES 30 950 9400

7654 MARTIN 30 1250 9400

7521 WARD 30 1250 9400

7844 TURNER 30 1500 9400

7499 ALLEN 30 1600 9400

7698 BLAKE 30 2850 9400

We can see from the result that the sum () function calculates the sum of Department differentiation. The official point of "partition by" is called "partition", which is actually a range condition for statistics.

Next we will add "order by sal" to the Oracle over () function after modifying the preceding SQL statement to see a more enjoyable effect:

SQL code

Select a. empno as employee ID

, A. ename as employee name

, A. deptno as department no.

, A. sal as salary

, Sum (sal) over (partition by deptno) calculates the total salary by department

, Sum (sal) over (partition by deptno order by sal) cumulative salary by department

From scott. emp;

Result:

Department No. employee name employee code salary calculated by Department total salary calculated by Department cumulative salary

7934 MILLER 10 1300 8750 1300

7782 CLARK 10 2450 8750 3750

7839 KING 10 5000 8750 8750

7369 SMITH 20 800 10875 800

7876 ADAMS 20 1100 10875 1900

7566 JONES 20 2975 10875 4875

7788 SCOTT 20 3000 10875 10875

7902 FORD 20 3000 10875 10875

7900 JAMES 30 950 9400 950

7654 MARTIN 30 1250 9400 3450

7521 WARD 30 1250 9400 3450

7844 TURNER 30 1500 9400 4950

7499 ALLEN 30 1600 9400 6550

7698 BLAKE 30 2850 9400 9400

We can see from the results that "order by" is added to accumulate the statistics. Here, I personally understand that the statistics set for the statistical range are smooth, one step at a time.

Note: Do not add "order by" at the end of this SQL statement. It should be the analysis function used (partition by deptno order by sal)

There are already sorting statements. If you add a sorting clause at the end of a sentence, the results will be confusing if they are inconsistent.

2. Combine rank () with Oracle over ()

Rank is a hierarchical function. This function must be used with the over function. Otherwise, an error "Window Function missing" is reported ". I tested the SQL statement as follows:

SQL code

Select a. empno as employee ID,

A. sal as salary,

A. job as position,

Rank () OVER (partition by a. job order by a. sal desc) as position salary level

From scott. emp;

The query result is:

Employee ID: salary position level

7902 3000 ANALYST 1

7788 3000 ANALYST 1

7934 1300 cler1

7876 1100 cler2

7900 950 cler3

7369 800 cler4

7566 2975 MANAGER 1

7698 2850 MANAGER 2

7782 2450 MANAGER 3

7839 5000 PRESIDENT 1

7499 1600 SALESMAN 1

7844 1500 SALESMAN 2

7654 1250 SALESMAN 3

7521 1250 SALESMAN 3