Analytical functions include simple aggregates, window functions, and numeric functions.
Simple aggregates — AVG, COUNT, MAX, MIN, and SUM, STDDEV, and VARIANCE
You can use all simple aggregates, except the Grouping() function, with an OLAP windowed function.
Window functions:
Windowing aggregates — AVG, COUNT, MAX, MIN, and SUM.
Ranking functions — RANK, DENSE_RANK, PERCENT_RANK, ROW_NUMBER, and NTILE.
Statistical functions — STDDEV, STDDEV_SAMP, STDDEV_POP, VARIANCE, VAR_SAMP, and VAR_POP.
Distribution functions — PERCENTILE_CONT and PERCENTILE_DISC.
Inter-row functions — LAG and LEAD.
Numeric functions — WIDTH_BUCKET, CEIL, and LN, EXP, POWER, SQRT, and FLOOR.
The ranking and inverse distribution analytical functions are not supported by Adaptive Server Enterprise.
This table lists the analytical functions and their parameters. Unlike some aggregate functions, you cannot specify DISTINCT in window functions.
Function |
Parameters |
---|---|
AVG |
( { column-name | numeric-expr } ) |
COUNT |
( * ) |
COUNT |
( { column-name | expression } ) |
DENSE_RANK |
() |
GROUPING * |
( { GROUPING group-by-expression } ) |
MAX |
( { column-name | expression } ) |
MIN |
( { column-name | expression } ) |
NTILE |
( integer ) |
PERCENT_RANK |
() |
PERCENTILE_CONT |
( numeric-expr ) |
PERCENTILE_DISC |
( numeric-expr ) |
RANK |
() |
ROW_NUMBER |
() |
STDDEV |
( [ ALL ] expression ) |
STDDEV_POP |
( [ ALL ] expression ) |
STDDEV_SAMP |
( [ ALL ] expression ) |
SUM |
( { column-name | expression } ) |
VAR_POP |
( [ ALL ] expression ) |
VAR_SAMP |
( [ ALL ] expression ) |
VARIANCE |
( [ ALL ] expression ) |
* The OLAP SQL standard allows Grouping() in GROUP BY CUBE, or GROUP BY ROLLUP operations only.