Oracle Analysis Function Learning

I. Analysis Function syntax
 
FUNCTION_NAME (<parameter> ,...)
 
OVER (<partition by expression,...> <Order by expression <ASC | DESC> <nulls first | nulls last> <drawing wing clause>)
 
 
 
1. FUNCTION_NAME (<parameter> ,...)
 
The purpose of each analysis function is described later.
 
2. OVER keyword
 
Over is just a keyword. It is an analysis function.
 
3. PARTITION clause
 
The condition group Calculation of the analysis function is equivalent to the group by function. However, the analysis function does not aggregate the result set, but displays the calculation results of each row in the original record mode. By default, this clause indicates that the entire record set is calculated as a group.
 
4. order by clause
 
The order by clause in the analysis function is similar to the order by clause in the standard SQL statement, which indicates the conditions in the group for sorting. asc and desc indicate the direction of sorting, nulls first and nulls last indicate the sorting position of null values.
 
5. Extra wing clause
 
The default window is: when an order by clause exists, it indicates from the first row of the current partition to the current row; if there is no order by clause, it indicates the entire group.
 
Window functions can be used only when the order by clause is available.
 
A. range window, logical offset
 
The RANGE expression PRECEDING, the record set from the first N rows of the current row in the current group to the current row. The sorting column and expression can only be numerical values or interval dates. The selected window is before the current row after sorting (only one sorting column can be used when this window function is used) the value is greater than/less than (the value of this column in the current row-/+ expression), so it is related to the order by clause. Calculates the window range by sorting columns.
 
There can be multiple sorting columns in the following two cases:
 
Range between unbounded preceding and current row.
 
Range between current row and unbounded following.
 
B. ROW WINDOW (physical offset)
 
ROWS expression PRECEDING, which must be a positive number. Calculates the starting row of the current row in the order of sorting results.
 
In addition to the PRECEDING keyword above, there is also the current row representing the current row, FOLLOWING representing the next N rows of the current row, you can also use the between and form, for example, range between m preceding and n FOLLOWING indicates the calculation of the records from the first m rows to the last n rows. If it is not in the between and format, oracle considers that the window function only writes the starting row, AND the current row terminates the row by default. Therefore, the FOLLOWING keyword can only be used in between and.
 
 
 
II. Introduction to Analytic Functions
 
AVG
 
(<Distinct | all> expr)
 
Add distinct to remove the average value of an expression in a group or selected window.
 
CORR
 
(Expr, expr)
 
COVAR_POP (exp1, exp2)/(STDDEV_POP (expr1) * STDDEV_POP (expr2). The two expressions are correlated,-1 (inverse correlation )~ 1 (positive correlation), indicating unrelated
 
COUNT
 
(<Distinct> <*> <expr>)
 
Count, add distinct deduplication.
 
COVAR_POP
 
(Expr, expr)
 
Population Covariance
 
COVAR_SAMP
 
(Expr, expr)
 
Sample covariance
 
CUME_DIST
 
Cumulative distribution, that is, the relative position of the row in the group. 0 ~ is returned ~ 1
 
DENSE_RANK
 
The relative sorting of rows (with order by). The same value has the same ordinal number (the NULL value is the same), and the ordinal number is not left blank.
 
FIRST_VALUE
 
The first value of a group.
 
LAG
 
(Expr, <offset>, <default>)
 
For the row before access, OFFSET is a positive value of 1 by DEFAULT, indicating the relative number of rows. DEFAULT is the return value when the window range is exceeded (for example, the first row does not exist in the previous row)
 
LAST_VALUE
 
The last value of a group.
 
LEAD
 
(Expr, <offset>, <default>)
 
For the accessed rows, OFFSET is the positive value of 1 by DEFAULT, indicating the number of relative rows. DEFAULT is the return value when the window range is exceeded (for example, the last row does not exist in the previous row)
 
MAX
 
(Expr)
 
Maximum Value
 
MIN
 
(Expr)
 
Minimum value
 
NTILE
 
(Expr)
 
ID by the expression value and the position of the row in the group. If the expression is 4, the group consists of four parts: 1 ~ 4, instead of waiting for the extra part of the sub-part to be in the group with the smallest value
 
PERCENT_RANK
 
Similar to CUME_DIST, 1/(ordinal number of rows-1)
 
RANK
 
The relative ordinal number. This parameter can be used in parallel and the subsequent sequence number is empty.
 
RATIO_TO_REPORT
 
(Expr)
 
Expression value/SUM (expression value), the proportion of the current value to the current group.
 
REGR _ xxxx
 
(Expr, expr)
 
Linear Regression Functions
 
ROW_NUMBER
 
Offset of the row in the sorted Group
 
STDDEV
 
(Expr)
 
Standard Deviation
 
STDDEV_POP
 
(Expr)
 
Population Standard Deviation
 
STDDEV_SAMP
 
(Expr)
 
Sample Standard Deviation
 
SUM
 
(Expr)
 
Total
 
VAR_POP
 
(Expr)
 
Population variance
 
VAR_SAMP
 
(Expr)
 
Sample variance
 
VARIANCE
 
(Expr)
 
Variance
 
 
 
Iii. Key words of Aggregate functions
 
The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV are used together with DENSE_RANK FIRST/DENSE_RANK LAST when KEEP is used, obtain the first or last ranking record in a group. The order by clause must be used for sorting. You can also use the over () analysis function section.
 
Min (col2) keep (dense_rank first order by col1) retains the minimum value of col2 ranked first by col1.
 
Min (col2) keep (dense_rank first order by col1) over (partition by col3) groups by col3 to retain the minimum value of col2 ranked first by col1.