Skip to main content

Oracle Analytic Functions

AVG(expression) : Returns average value of expression

CORR(expression1, expression2) : Returns the coefficient of correlation of a set of number pairs

COVAR_POP(expression1, expression2) : Returns the population covariance of a set of number pairs

COVAR_SAMP(expression1, expression2) : Returns the sample covariance of a set of number pairs

COUNT(expression) : Returns the number of rows returned by the query

CUME_DIST(expression1, ...) : Calculates the cumulative distribution of a value in a group of values

DENSE_RANK() : Computes the rank of a row in an ordered group of rows and returns the rank as a number

FIRST_VALUE(expression) : Returns the first value in an ordered set of values

LAG(expression, offset, ...) : Provides access to a row at a given physical offset prior to cursor position

LAST_VALUE(expression) : Returns the last value in an ordered set of values

LEAD(expression, offset, ...) : Provides access to a row at a given physical offset beyond cursor position

MAX(expression) : Returns maximum value of expression

MIN(expression) : Returns minimum value of expression

NTILE(expression) : Divides an ordered data set into a number of buckets indicated by expression and assigns the appropriate bucket number to each row

PERCENT_RANK(expression1, ...) : For a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than the number of rows being evaluated

PERCENTILE_CONT(expression) : Takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value

PERCENTILE_DISC(expression) : Takes a percentile value and a sort specification, and returns an element from the set

RANK(expression1, ...) : Computes the rank of each row returned from a query with respect to the other rows returned by the query

RATIO_TO_REPORT(expression) : Computes the ratio of a value to the sum of a set of values

REGR_SLOPE(expression1, expression2) : Returns the slope of the line

REGR_INTERCEPT(expression1, expression2) : Returns the y-intercept of the regression line

REGR_COUNT(expression1, expression2) : Returns an integer that is the number of non-null number pairs used to fit the regression line

REGR_R2(expression1, expression2) : Returns the coefficient of determination (also called R-squared or goodness of fit) for the regression

REGR_AVGX(expression1, expression2) : Evaluates the average of the independent variable (expression2) of the regression line

REGR_AVGY(expression1, expression2) : Evaluates the average of the independent variable (expression1) of the regression line

ROW_NUMBER() : Assigns a unique number to each row to which it is applied

STDDEV(expression) : Returns the sample standart deviation of expression, a set of numbers

STDDEV_POP(expression) : Computes the population standart deviation and returns the square root of the population variance

STDDEV_SAMP(expression) : Computes the cumulative sample standart deviation and returns the square root of the sample variance

SUM(expression) : Returns the sum of values of expression

VAR_POP(expression) : Returns the population variance of a set of numbers

VAR_SAMP(expression) : Returns the sample variance of a set of numbers

Comments

Popular posts from this blog