Summary of basic concepts and syntax of Oracle analysis functions

The analysis function calculates the aggregation value based on a row set. Different from the aggregate function, the analysis function returns multiple rows. Except for the last orderby clause, the analysis function is in a query.

The analysis function calculates the aggregation value based on a row set. Different from the aggregate function, the analysis function returns multiple rows. Except for the last order by clause, the analysis function is a query.

Analytic functions compute an aggregate value based on a group of rows. they differ from aggregate functions in that they return multiple rows for each group. the group of rows is called a window and is defined by the analytic_clause. for each row, a sliding window of rows is defined. the window determines the range of rows used to perform the calculations for the current row. window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations saved med in a query failed t for the final order by clause. all joins and all WHERE, group by, and HAVING clses are completed before the analytic functions are processed. therefore, analytic functions can appear only in the select list or order by clause.

The analysis function calculates the aggregation value based on a row set. Different from the aggregate function, the analysis function returns multiple rows.
(Many analysis functions can also be used as Aggregate functions, but their syntax is different)
A row set is called a window. A sliding window is defined for each row. This window determines the range of rows used for calculation for the current row.
The window size can be physical (row number) or logical (value range ).

Except for the final order by clause, the analysis function is the last operation executed in a query. All join, where, group by, and having statements are executed before the analysis function ,, therefore, analysis functions can only appear in select statements or order by clauses.

Analysis Function syntax
Analytic_function ([arguments])
OVER (
[Partition_clause]
[Order_by_clause [windowing_clause]
)

1) analysis functions
There are the following analysis functions. The function with the asterisk (*) indicates that the wing clause is allowed.
AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
Listparts
MAX *
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 *

2) PARTITION clause
Grouping by expression. If this clause is omitted, all result sets are considered as a group.

3) order by clause
Order by adds a default running wing clause. This window continues from the first row of the Group to the current row (value ),
That is, range between unbounded preceding and current row.
If order by is not available, the default window is all rows in the group.

4) wing clause
Defines the row set used by the analysis function. The data in the set can be seen as a window.
To use the explain wing clause, you must use the order by clause.
There are two keywords for Window Creation: ROWS and RANGE.
ROWS indicates a physical window (ROW); RANGE indicates a logical window.
BETWEEN... AND: specifies a range. If it is omitted, only one endpoint is specified. It serves as the start point AND the end point is the current row.
Unbounded preceding: indicates that the window starts from the first row of the current group.
Unbounded following: indicates the window, which ends on the last row of the current group.
Current row: indicates that the window starts/ends with the current row or the CURRENT value.
Value_expr PRECEDING/value_expr FOLLOWING: Specifies a physical or logical offset.

The following are examples to understand these concepts. The emp table has the following data:

The wmsys. wm_concat () function can be used to merge fields and merge a column of data from multiple rows into one row for display.
For example, the following function is used to display the names of all people in a department in one row.

In the preceding example, wm_concat () is used as an aggregate function. Let's take a look at it as the result of the analysis function.
Different from Aggregate functions, the analysis function returns a result for each row.

In the preceding example, the order by clause is not added. The default window without the order by clause is all rows in the group.
Therefore, all the names in each department group are displayed.