Learn Oracle Analytic functions (Analytic Functions)

Oracle provides a number of powerful analytic functions that can be used to accomplish requirements that may require a stored procedure to be implemented.

The analytic function calculates the aggregated values based on a set of data rows, which are different from the aggregate functions, and they return multiple rows of results for each group. The parse function is executed last in the query statement except for the ORDER BY clause. All joins and all where, GROUP by and having clauses are executed before the parse function. Therefore, the analysis function can only appear in the Select or ORDER BY clause.

The syntax given in the official documentation for version 11.2:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/92/23/wKiom1j8YE-zfXLsAAGOKuDzgbs859.png "title=" 1.png "alt=" Wkiom1j8ye-zfxlsaagokudzgbs859.png "/> A brief introduction to each of the sections below:

Analytic_function

Specify the name of the parse function, followed by all the analysis functions

Arguments

Analysis functions can have between 0 and 3 parameters. A parameter can be any numeric type or other non-numeric type that can be implicitly converted to a numeric type.

Analytic_clause

Using over analytic_clause indicates that a function operation is a query result set. If you want to filter query results based on analytic functions, you need to use nested subqueries.

Query_partition_clause

Use the partition BY clause to group the query result set based on one or more value_expr. If omitted, the parse function treats all rows as a group.

Order_by_clause

Use Order_by_claus to specify how the data is sorted in a group.

ASC (default) | DESC

NULLS First (default in DESC) | NULLS last (default in ASC)

Windowing_clause

Partial parsing functions allow the use of the Windowing_clause clause.

This clause can be specified only if Order_by_clause is specified.

rows specifies the window that uses the physical row

range specifies a window with a logical offset

For more information, please refer to: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i81407

The following are all the analysis functions, with the * number function allowing the use of Windowing_clause:

Avg *
CORR *
COUNT *
Covar_pop *
Covar_samp *
Cume_dist
Dense_rank
First
First_value *
LAG
Last
Last_value *
Lead
Listagg
MAX *
MEDIAN
MIN *
Nth_value *
NTILE
Percent_rank
Percentile_cont
Percentile_disc
RANK
Ratio_to_report
Regr_ (Linear Regression) Functions *
Row_number
STDDEV *
Stddev_pop *
Stddev_samp *
SUM *
Var_pop *
Var_samp *
VARIANCE *

Take AVG as an example to introduce the use of analytic functions:

AVG is also an aggregation function:

[Email protected]>select avg (SAL) from EMP; AVG (SAL)----------2073.21429

As an example of an analytic function:

EG1: Used Alone

[Email protected]>select deptno,ename,hiredate,sal,avg (SAL)  over ()  avg from  emp;    deptno ename  hiredate      Salavg---------- ------------------------------ ------------------- ---------- ---------- 20 smith  1980-12-17 00:00:00     800 2073.2142930  allen  1981-02-20 00:00:00    1600 2073.2142930 ward    1981-02-22 00:00:00    1250 2073.2142920 jones  1981-04-02  00:00:00    2975 2073.2142930 martin  1981-09-28 00:00:00     1250 2073.2142930 BLAKE  1981-05-01 00:00:00     2850 2073.2142910 clark  1981-06-09 00:00:00    2450  2073.2142920 SCOTT  1987-04-19 00:00:00    3000 2073.2142910 king   1981-11-17  00:00:00    5000 2073.2142930 turner  1981-09-08 00:00:00     1500 2073.2142920 ADAMS  1987-05-23 00:00:00     1100 2073.2142930 JAMES  1981-12-03 00:00:00      950 2073.2142920 ford   1981-12-03 00:00:00    3000  2073.2142910 miller  1982-01-23 00:00:00    1300 2073.21429

From the output you can see that the function calculates the average of the whole and outputs it to each row

EG2: Using Query_partition_clause

[Email protected]>select deptno,ename,hiredate,sal,avg (SAL)  over (partition by  DEPTNO)  avg from emp;    DEPTNO ENAME  HIREDATE      salavg---------- ------------------------------ ------------------- --------- - ----------10 clark  1981-06-09 00:00:00    2450  2916.6666710 king   1981-11-17 00:00:00    5000 2916.6666710  miller  1982-01-23 00:00:00    1300 2916.6666720 jones   1981-04-02 00:00:00    2975        217520 ford   1981-12-03 00:00:00    3000        217520 ADAMS  1987-05-23 00:00:00    1100        217520 smith  1980-12-17 00:00:00     800        217520 SCOTT  1987-04-19 00:00:00    3000        217530 WARD   1981-02-22 00:00:00     1250 1566.6666730 turner  1981-09-08 00:00:00    1500  1566.6666730 ALLEN  1981-02-20 00:00:00    1600  1566.6666730 james  1981-12-03 00:00:00     950 1566.6666730  blake  1981-05-01 00:00:00    2850 1566.6666730 martin   1981-09-28 00:00:00    1250 1566.66667[email protected]> Select deptno,avg (SAL)  from emp group by deptno;    deptno    avg (SAL)---------- ----------30 1566.6666720 217510 2916.66667 

As you can see from the output, Avg calculates the average of each department and outputs it to the corresponding row.

EG3: Using Order_by_clause

[Email protected]>select deptno,ename,hiredate,sal,avg (SAL)  over (partition by  Deptno order by sal)  avg from emp;    deptno ename   hiredate     salavg---------- ------------------------------ -- ----------------- ---------- ----------10 miller  1982-01-23 00:00:00     1300       130010 CLARK  1981-06-09  00:00:00    2450       187510 king    1981-11-17 00:00:00    5000 2916.6666720 smith  1980-12-17  00:00:00     80080020 ADAMS  1987-05-23 00:00:00     110095020 JONES  1981-04-02 00:00:00    2975        162520 scott  1987-04-19 00:00:00    3000        217520 FORD   1981-12-03 00:00:00    3000        217530 JAMES  1981-12-03 00:00:00      95095030 MARTIN  1981-09-28 00:00:00    1250        115030 ward   1981-02-22 00:00:00    1250        115030 TURNER  1981-09-08 00:00:00     1500     1237.530 ALLEN  1981-02-20 00:00:00     1600       131030 BLAKE  1981-05-01  00:00:00    2850 1566.66667

As you can see from the output, the rows in each department are sorted in ascending order by Sal.

EG4: Using Windowing_clause

[Email protected]>select deptno,ename,hiredate,sal,avg (SAL)  over (partition by  Deptno order by sal rows between 1 preceding and 1 following )  avg from emp;    DEPTNO ENAME  HIREDATE      salavg---------- ------------------------------ ------------------- ----------  ----------10 miller  1982-01-23 00:00:00    1300        187510 CLARK  1981-06-09 00:00:00     2450 2916.6666710 king   1981-11-17 00:00:00    5000        372520 SMITH  1980-12-17 00:00:00      80095020 ADAMS  1987-05-23 00:00:00    1100        162520 jones  1981-04-02 00:00:00    2975 2358.3333320 scott   1987-04-19 00:00:00    3000 2991.6666720 FORD    1981-12-03 00:00:00    3000       300030  james  1981-12-03 00:00:00     950        110030 MARTIN  1981-09-28 00:00:00    1250        115030 ward   1981-02-22 00:00:00    1250  1333.3333330 TURNER  1981-09-08 00:00:00    1500        145030 ALLEN  1981-02-20 00:00:00     1600 1983.3333330 blake  1981-05-01 00:00:00    2850        2225 

As you can see from the output, the output of the analysis function to each of the rows in each group is to average itself to the three rows of its previous and next row.

Analysis function Too many, here do not introduce the function, interested students can point open the above connection, to see the corresponding function.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1918614

Learn Oracle Analytic functions (Analytic Functions)