Analytical Functions
Ranking Functions
| Ranking Functions | Description |
|---|---|
| ROWNUM | pseudo-column and is calculated as rows are retrieved |
| ROW_NUMBER | row number for ranking |
| RANK | Returns the rank of items in a group and leaves a gap for a tie. |
| DENSE_RANK | Rank 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. |
| NTILE | Groups data by putting data into a number of percentile groups |
| Hypothetical | Rank and Distribution Functions |
Group By Functions
| Group By Functions | Description |
|---|---|
| PARTITION BY | Separate data groups and perform calculation within that group |
| CUBE | Returns rows containing a subtotal for all combinations of columns |
| ROLLUP | Returns a row containing a subtotal |
| GROUPING | Deals with NULL value for ROLLUP and CUBE |
| GROUPING_ID | Returns the decimal equivalent of the GROUPING bit vector. |
| GROUP_ID | Removes duplicate rows returned by a ROLLUP or CUBE. |
Inverse Percentile Functions
| Inverse Percentile Functions | Description |
|---|---|
| PERCENTILE_DISC | Checks the cumulative distribution values |
| PERCENTILE_CONT | Checks the percent rank values in each group |
Window Functions
- Cumulative Sum
- Moving Average
- Centered Average
- Gets the first rows in a window.
- Gets the last rows in a window.
Report Function
- RATIO_TO_REPORT()
- LAG
- LEAD
- FIRST gets the first values in an ordered group.
- LAST gets the last values in an ordered group.
Linear Regression Functions
| Linear Regression Functions | Description |
|---|---|
| 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) |