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)