# 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 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.