Oracle Analytic functions

Recognize analytic functions

What is an analytic function?

Analytic functions are powerful functions that Oracle specifically uses to address the statistical requirements of complex reports, which can be grouped in data and then calculated based on some statistical values of groups, and each row of each group can return a statistic value.

What is the difference between an analytic function and an aggregate function?

The normal aggregation functions are grouped by group by, each grouping returns a statistic value, and the analysis functions are grouped by partition, and each group can return a statistic value.

The form of an analytic function:

The analysis function has an open window function over (), which contains three analysis clauses: grouping (partition by), sorting (Order by), window (Rows), which are used in the following form:

Over (Partition by XXX Order by yyy rows between zzz)

NOTE: Window clauses include: Rows window, range mode, and sliding window.

Examples of analytic functions:

Displays the wages of the employees of each department, accompanied by a maximum wage showing the section.

Select Deptno,empno,ename,sal,

Last_value (SAL) over (partition by Deptno order by sal rows between unbounded preceding and unbounded following) Max_sal

from EMP;

DEPTNO EMPNO ename SAL max_sal

7934 MILLER 1300.00 5000

7782 CLARK 2450.00 5000

Ten 7839 KING 5000.00 5000

7369 SMITH 800.00 3000

7876 ADAMS 1100.00 3000

7566 JONES 2975.00 3000

7788 SCOTT 3000.00 3000

7902 FORD 3000.00 3000

7900 JAMES 950.00 2850

7654 MARTIN 1250.00 2850

7521 WARD 1250.00 2850

7844 TURNER 1500.00 2850

7499 ALLEN 1600.00 2850

7698 BLAKE 2850.00 2850

2. understand the over () function

2.1 timing of execution of two order by

An analytic function is an operation that occurs after the end of an entire SQL query (the execution of an order by in an SQL statement is special), meaning that the order by in the SQL statement also affects the execution of the analysis function:

a) The same : If the order by in the SQL statement satisfies the ordering required by the parse function analysis, then the sort in the SQL statement will be executed first, and the parsing function will not have to be sorted at the time of parsing.

b) inconsistency between the two : if the order by in the SQL statement does not satisfy the ordering required to parse the parse function, the sort in the SQL statement will last after parsing function analysis is finished.

2.2 grouping/sorting/windows in analytic functions

The analysis function contains three analysis clauses : grouping (Partition by), sorting (Order by), window (Rows)

The window is the range of data to be processed when analyzing the function analysis, taking sum, which is the record in the sum window instead of the entire grouping, so we need to assign the window to the first row of data in the group to the current row when we want the accumulated value of a field. If you specify that the window is from the first row in the group to the last row, then each sum value in the group will be the same, which is the sum of the entire group.

Window clauses Here I only say rows window, range mode and sliding window do not mention

In a window clause we often use three properties that specify the first row, the current row, and the last row.

The first line is unbounded preceding,

The current row is

The last line is unbounded following.

A window clause cannot appear alone, and must have an ORDER BY clause to appear, such as:

Last_value (SAL) over (partition by Deptno order by sal rows between unbounded preceding and unbounded following)

The example above specifies that the window is the entire grouping.

When an ORDER BY clause is present, there is not necessarily a window clause, but the effect is very different, at which time the window defaults to the current row of the first row of the current group!

When omitting a window clause:

A) If an order by is present, the default window is unbounded preceding and current now

b) If an order by is omitted at the same time, the default window is unbounded preceding and unbounded following

If the grouping is omitted, all records are treated as a group:

A) If an order by is present, the default window is unbounded preceding and current row

b) If the order by is omitted at this time, the window defaults to unbounded preceding and unbounded following

2.3 help understanding instances of over ()

Example 1:sql no sort, over () sort clause omitted

Select Deptno, Empno,ename, Sal,

Last_value (SAL) over (partition by Deptno)

From EMP

where deptno=20

DEPTNO EMPNO ename sal Last_value (SAL) over (partitionb

7369 SMITH 800.00 3000

7566 JONES 2975.00 3000

7788 SCOTT 3000.00 3000

7876 ADAMS 1100.00 3000

7902 FORD 3000.00 3000

Example 2:sql no sort, over () sort clauses have, window omitted

Select deptno, Empno,ename, Sal,

Last_value (SAL) over (partition by Deptno ORDER by Sal)

From EMP where deptno= ' 30 '

DEPTNO EMPNO ename sal Last_value (SAL) over (partitionb

7900 JAMES 950.00 950

7654 MARTIN 1250.00 1250

7521 WARD 1250.00 1250

7844 TURNER 1500.00 1500

7499 ALLEN 1600.00 1600

7698 BLAKE 2850.00 2850

Example 3:sql no sort, over () sort clauses have, window also, window specifically emphasizes the whole group of data

Select Deptno, Empno,ename, Sal,

Last_value (SAL) over (partition by Deptno order by Sal

Rows between unbounded preceding and unbounded following)

From EMP where deptno=30

DEPTNO EMPNO ename sal Last_value (SAL) over (partitionb

7900 JAMES 950.00 2850

7521 WARD 1250.00 2850

7654 MARTIN 1250.00 2850

7844 TURNER 1500.00 2850

7499 ALLEN 1600.00 2850

7698 BLAKE 2850.00 2850

Example 4:sql has sort (positive order), over () sort clauses No, first SQL sort and then analysis function operation.

Select Deptno, Empno,ename, Sal,

Last_value (SAL) over (partition by Deptno)

From EMP

where deptno=30

ORDER BY Deptno,sal

DEPTNO EMPNO ename sal Last_value (SAL) over (partitionb

7900 JAMES 950.00 2850

7521 WARD 1250.00 2850

7654 MARTIN 1250.00 2850

7844 TURNER 1500.00 2850

7499 ALLEN 1600.00 2850

7698 BLAKE 2850.00 2850

2.4 lag () with lead (): Before and after the nth line

Lag (ARG1,ARG2,ARG3):

ARG1 is an expression returned from another row

ARG2 is the offset of the current row partition that you want to retrieve. is a positive offset, which is the number of previous rows that are retrieved backwards.

ARG3 is the value returned when the number represented by Arg2 exceeds the range of the grouping.

The lead () is opposite to lag ()

2.5. Ratio_to_report (): percent

Select Empno,ename,sal,deptno,

Ratio_to_report (SAL) over () as "each employee's salary is the total wage ratio",

Ratio_to_report (SAL) over (partition by Deptno) as "the salary of each employee is the ratio of the total salary of the department"

From EMP

Order BY Deptno,empno;

EMPNO ename SAL DEPTNO The salary of each employee is more than the total salary of each employee compared to the salary of the department.

7782 CLARK 2450.00 10 0.0844099913867356 0.28

7839 KING 5000.00 10 0.172265288544358 0.571428571428571

7934 MILLER 1300.00 10 0.0447889750215332 0.148571428571429

7369 SMITH 800.00 20 0.0275624461670973 0.0735632183908046

7566 JONES 2975.00 20 0.102497846683893 0.273563218390805

7788 SCOTT 3000.00 20 0.103359173126615 0.275862068965517

7876 ADAMS 1100.00 20 0.0378983634797588 0.101149425287356

7902 FORD 3000.00 20 0.103359173126615 0.275862068965517

7499 ALLEN 1600.00 30 0.0551248923341947 0.170212765957447

7521 WARD 1250.00 30 0.0430663221360896 0.132978723404255

7654 MARTIN 1250.00 30 0.0430663221360896 0.132978723404255

7698 BLAKE 2850.00 30 0.0981912144702842 0.303191489361702

7844 TURNER 1500.00 30 0.0516795865633075 0.159574468085106

7900 JAMES 950.00 30 0.0327304048234281 0.101063829787234

Summary: Ratio_to_report () is a molecule in parentheses,

Over () is the denominator in parentheses

With Ratio_to_report (analytic functions, we avoid the need to write analytic functions and divide them by themselves.)

Note: The denominator default is the total ratio.

Oracle Analytic functions