Shulou(Shulou.com)06/01 Report--

Oracle provides some powerful analysis functions that can be used to accomplish requirements that may require stored procedures to implement.

Analysis functions calculate aggregate values based on a set of data rows, which, unlike aggregate functions, return multiple rows of results for each set. The parsing function is executed last in the query statement except for the ORDER BY clause. All join and all WHERE, GROUP BY, and HAVING clauses are executed before the parse function. So parsing functions can only appear in select or ORDER BY clauses.

The following is a syntax diagram given in the official documentation for version 11.2:

Here is a brief introduction to each part:

Analytic_function

Specify the name of the analysis function, followed by a list of all the analysis functions

Arguments

The parse function can have 0 to 3 parameters. Parameters can be any numeric type or other non-numeric type that can be implicitly converted to a numeric type.

Analytic_clause

Use OVER analytic_clause to indicate that the function operates on a query result set. If you want to filter query results based on analysis 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 parsing function treats all rows as a group.

Order_by_clause

Use order_by_claus to specify how data is sorted in a group.

ASC (default) | DESC

NULLS FIRST (default in DESC) | NULLS LAST (default in ASC)

Windowing_clause

Some 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 window that uses logical offsets

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

Here are all the parsing functions. Functions with a * sign allow 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 analysis functions:

AVG is also an aggregate function:

Scott@TEST > select avg (sal) from emp; AVG (SAL)-2073.21429

As an example of an analysis function:

Eg1: used alone

Scott@TEST > select deptno,ename,hiredate,sal,avg (sal) over () avg from emp DEPTNO ENAME HIREDATE SAL AVG--20 SMITH 1980-12-17 00:00:00 800 2073.21429 30 ALLEN 1981-02-20 00:00:00 1600 2073.21429 30 WARD 1981-02-22 00:00:00 1250 2073.21429 20 JONES 1981-04-02 00:00:00 2975 2073.21429 30 MARTIN 1981-09-28 00:00:00 1250 2073.21429 30 BLAKE 1981-05-01 00:00:00 2850 2073.21429 10 CLARK 1981-06-09 00:00:00 2450 2073.21429 20 SCOTT 00:00:00 3000 2073.21429 10 KING 1981-11-17 00:00:00 5000 2073.21429 30 TURNER 1981-09-08 00:00:00 1500 2073.21429 20 ADAMS 1987-05-23 00:00:00 1100 2073.21429 30 JAMES 1981-12-03 00:00:00 950 2073.21429 20 FORD 1981-12-03 00:00:00 3000 2073.21429 10 MILLER 1982-01-23 00:00:00 1300 2073.21429

From the output, we can see that the function calculates the overall average value and outputs it to each line.

Eg2: using query_partition_clause

Scott@TEST > select deptno,ename,hiredate,sal,avg (sal) over (partition by deptno) avg from emp DEPTNO ENAME HIREDATE SAL AVG--10 CLARK 1981-06-09 00:00:00 2450 2916.66667 10 KING 1981-11-17 00:00:00 5000 2916.66667 10 MILLER 1982-01-23 00:00:00 1300 2916.66667 20 JONES 5000 00:00:00 2175 20 FORD 1981-12-03 00:00:00 3000 2175 20 ADAMS 1987-05-23 00:00:00 1100 2175 20 SMITH 1980-12-17 00:00:00 800 2175 20 SCOTT 1987-04-19 00:00:00 3000 2175 30 WARD 1981-02-22 00:00:00 1250 1566.66667 30 TURNER 1981-09-08 00:00:00 1500 1566.66667 30 ALLEN 1981-02-20 00:00:00 1 600 1566.66667 30 JAMES 1981-12-03 00:00:00 950 1566.66667 30 BLAKE 1981-05-01 00:00:00 2850 1566.66667 30 MARTIN 1981-09-28 00:00:00 1250 1566.66667scott@TEST > select deptno Avg (sal) from emp group by deptno DEPTNO AVG (SAL)-30 1566.66667 20 2175 10 2916.66667

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

Eg3: using order_by_clause

Scott@TEST > select deptno,ename,hiredate,sal,avg (sal) over (partition by deptno order by sal) avg from emp DEPTNO ENAME HIREDATE SAL AVG--10 MILLER 1982-01-23 00:00:00 1300 1300 10 CLARK 1981-06-09 00:00:00 2450 1875 10 KING 1981-11-17 00:00:00 5000 2916.66667 20 SMITH 1980-12-17 00:00:00 800800 20 ADAMS 1987-05-23 00:00:00 1100 950 20 JONES 1981-04-02 00:00:00 2975 1625 20 SCOTT 1987-04-19 00:00:00 3000 2175 20 FORD 1981-12-03 00:00:00 3000 2175 30 JAMES 1981-12-03 00:00:00 950 950 30 MARTIN 1981-09-28 00:00:00 1250 1150 30 WARD 1981-02-22 00:00:00 1250 1150 30 TURNER 1981-09-08 00:00:00 1500 1237.5 30 ALLEN 1981-02-20 00:00:00 1600 1310 30 BLAKE 1981-05-01 00:00:00 2850 1566.66667

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

Eg4: using windowing_clause

Scott@TEST > 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 SAL AVG--10 MILLER 1982-01-23 00:00:00 1300 1875 10 CLARK 1981-06-09 00:00:00 2450 2916.66667 10 KING 1981-11-17 00:00:00 5000 3725 20 SMITH 1980-12-17 00:00:00 800 950 20 ADAMS 1987-05-23 00:00:00 1100 1625 20 JONES 1981-04-02 00:00:00 2975 2358.33333 20 SCOTT 1987-04-19 00:00:00 3000 2991.66667 20 FORD 1981-12-03 00:00:00 3000 3000 30 JAMES 1981-12-03 00:00:00 950 1100 30 MARTIN 1981-09-28 00:00:00 1250 1150 30 WARD 1981-02-22 00:00:00 1250 1333.33333 30 TURNER 1981-09-08 00:00:00 1500 1450 30 ALLEN 1981-02-20 00:00:00 1600 1983.33333 30 BLAKE 1981-05-01 00:00:00 2850 2225

As you can see from the output, the output of the analysis function for each row in each group averages itself with its previous and next lines.

There are too many analysis functions, so we will not introduce the functions one by one here. Students who are interested can click on the connection above to check the corresponding functions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.