Oracle's Over-function application (reprint)

Excerpt from: http://www.poluoluo.com/jzxy/201004/81921.html

The Baidu Library also documents the use of the over () analysis function in Oracle

This function was discovered inadvertently in the jar, only to know that the Oracle analysis function is so powerful, and that the over () function is particularly special, so write your own research results.

Personal understanding: Over () function is a conditional interpretation of analytic functions, the direct point is to add conditions to the analysis function bar.

What you see on the Web is more commonly used with the sum (), rank () function. Next, we use the analysis of the two functions combined with the use of over.

The following tests use the EMP table data under the Orace default Scott user.

1.sum () combined over ()

SQL code

Select A.empno as employee number

, A.ename as Employee name

, A.deptno as department number

, a.sal as remuneration

, SUM (SAL) over (partition by deptno) sum of remuneration by department

From Scott.emp A;

The result of this segment of SQL execution is:

Department number employee name employee code remuneration sum of remuneration by department

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

You can see from the results that the sum () function sums up the divisional distinction. The "Partition by" official point of view is called "zoning", in fact, is the scope of the statistics conditions.

The following is a more enjoyable effect for transforming the above SQL statement into the over () function plus "ORDER by Sal":

SQL code

Select A.empno as employee number

, A.ename as Employee name

, A.deptno as department number

, a.sal as remuneration

, SUM (SAL) over (partition by deptno) sum of remuneration by department

, SUM (SAL) over (partition by Deptno ORDER by Sal) pay by department

From Scott.emp A;

The result is:

Department number employee name employee code remuneration by department total remuneration by department

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

From the results can be seen in addition to the "Order by" after the statistics of a cumulative, here is understood as a statistical scope of the statistical smooth, step-by-step statistics.

Note: Do not add "order by" at the end of this SQL statement, should be used for analytic functions (partition by Deptno ORDER by Sal)

Already have a sort of statement, if you add a sort clause at the end of the sentence, the same fall, inconsistent, the result is puzzling.

2.rank () combined over ()

The rank function is a hierarchical function that must be used with the over function, otherwise it will report a "Missing window function error". I test SQL as follows:

SQL code

Select A.empno as employee number,

A.sal as salary,

A.job as Post,

Rank () over (partition by A.job ORDER by a.sal Desc) as Post salary level

From Scott.emp A;

The query results are:

Employee Number Salary Level

7902 ANALYST 1

7788 ANALYST 1

7934 1300 Clerk 1

7876 1100 Clerk 2

7900 950 Clerk 3

7369 Clerk 4

7566 2975 MANAGER 1

7698 2850 MANAGER 2

7782 2450 MANAGER 3

7839 President 1

7499 salesman 1

7844 salesman 2

7654 1250 salesman 3

7521 1250 salesman 3

Oracle's Over-function application (reprint)