Analytical Functions

Ranking Functions

Ranking FunctionsDescription
ROWNUMpseudo-column and is calculated as rows are retrieved
ROW_NUMBERrow number for ranking
RANKReturns the rank of items in a group and leaves a gap for a tie.
DENSE_RANKRank the rows and does not skip ties
PERCENT_RANK()Returns the percent rank of a value relative to a group.
CUME_DIST()Returns the position of a value relative to a group.
NTILEGroups data by putting data into a number of percentile groups
HypotheticalRank and Distribution Functions

Group By Functions

Group By FunctionsDescription
PARTITION BYSeparate data groups and perform calculation within that group
CUBEReturns rows containing a subtotal for all combinations of columns
ROLLUPReturns a row containing a subtotal
GROUPINGDeals with NULL value for ROLLUP and CUBE
GROUPING_IDReturns the decimal equivalent of the GROUPING bit vector.
GROUP_IDRemoves duplicate rows returned by a ROLLUP or CUBE.

Inverse Percentile Functions

Inverse Percentile FunctionsDescription
PERCENTILE_DISCChecks the cumulative distribution values
PERCENTILE_CONTChecks the percent rank values in each group

Window Functions

Report Function

Linear Regression Functions

Linear Regression FunctionsDescription
REGR_AVGX(y, x)Returns the average of x after eliminating x and y pairs
REGR_AVGY(y, x)Returns the average of y after eliminating x and y pairs
REGR_COUNT(y, x)Returns the number of non-null number pairs that are used to fit the regression line
REGR_INTERCEPT(y, x)Returns the intercept on the y-axis of the regression line
REGR_R2(y, x)Returns the coefficient of determination of the regression line
REGR_SLOPE(y, x)Returns the slope of the regression line
REGR_SXX(y, x)Returns REG_COUNT (y, x) * VAR_POP(x)
REGR_SXY(y, x)Returns REG_COUNT (y, x) * COVAR_POP(y, x)
REGR_SYY(y, x)Returns REG_COUNT (y, x) * VAR_POP (y)
Home »
Oracle »