The below is the compiled list of analytical & advanced functions in Oracle SQL*Plus. Some of them are widely used ones which we will be discussed in detail in the upcoming articles.
|
Name |
Description |
|
CORR |
CORR returns the coefficient of correlation of a set of number
pairs. You can use it as an aggregate or analytic function. |
|
COVAR_POP |
COVAR_POP returns the population covariance of a set of number
pairs. You can use it as an aggregate or analytic function. |
|
COVAR_SAMP |
COVAR_SAMP returns the sample covariance of a set of number
pairs. You can use it as an aggregate or analytic function. |
|
CUME_DIST |
Calculate the cumulative distribution of a value in a set of
values |
|
DENSE_RANK |
Calculate the rank of a row in an ordered set of rows with no
gaps in rank values. |
|
FIRST_VALUE |
Get the value of the first row in a specified window frame. |
|
LAG |
Provide access to a row at a given physical offset that comes
before the current row without using a self-join. |
|
LAST_VALUE |
Get the value of the last row in a specified window frame. |
|
LEAD |
Provide access to a row at a given physical offset that follows
the current row without using a self-join. |
|
NTH_VALUE |
Get the Nth value in a set of values. |
|
NTILE |
Divide an ordered set of rows into a number of buckets and
assign an appropriate bucket number to each row. |
|
STDDEV |
STDDEV is a built-in function which returns the standard
deviation of a set of numbers, i.e. the square root of the variance for the
input number set. It can be used as both an Aggregate and an Analytic
function. |
|
VARIANCE |
VARIANCE returns the variance of expr . You can use it as an
aggregate or analytic function. Oracle Database calculates the variance of
expr as follows: 0 if the number of rows in expr = 1. |
|
VAR_POP |
VAR_POP returns the population variance of a set of numbers
after discarding the nulls in this set. You can use it as both an aggregate
and analytic function. |
|
VAR_SAMP |
VAR_SAMP returns the sample variance of a set of numbers after
discarding the nulls in this set. You can use it as both an aggregate and
analytic function. |
|
PERCENT_RANK |
Calculate the percent rank of a value in a set of values. |
|
RANK |
Calculate the rank of a value in a set of values |
|
ROW_NUMBER |
Assign a unique sequential integer starting from 1 to each row
in a partition or in the whole result |
|
CLUSTER_DETAILS |
CLUSTER_DETAILS returns cluster details for each row in the
selection. The return value is an XML string that describes the attributes of
the highest probability cluster or the specified cluster_id. |
|
FEATURE_DETAILS |
FEATURE_DETAILS returns feature details for each row in the
selection. The return value is an XML string that describes the attributes of
the highest value feature or the specified feature_id. |
|
PREDICTION_DETAILS |
PREDICTION_DETAILS returns prediction details for each row
in the selection. The return value is an XML string that describes the
attributes of the prediction. |
|
LISTAGG |
The LISTAGG function concatenates values of
the measure_column for each GROUP based on
the order_by_clause. |
|
RATIO_TO_REPORT |
RATIO_TO_REPORT computes the ratio of a value to the sum of
a set of values. If expr evaluates to null, then the
ratio-to-report value also evaluates to null. |
