Oracle Analytical Functions

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 N^th 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.