Analytical Functions

Analytical functions include simple aggregates, window functions, and numeric functions.

Simple aggregates — AVG, COUNT, MAX, MIN, and SUM, STDDEV, and VARIANCE

Note: 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.

Note: The ranking and inverse distribution analytical functions are not supported by Adaptive Server.

Unlike some aggregate functions, you cannot specify DISTINCT in window functions.

* The OLAP SQL standard allows Grouping() in GROUP BY CUBE, or GROUP BY ROLLUP operations only.

Windowing Aggregate Function Usage
A major feature of the ISO/ANSI SQL extensions for OLAP is a construct called a window. This windowing extension let users divide result sets of a query (or a logical partition of a query) into groups of rows called partitions and determine subsets of rows to aggregate with respect to the current row.
Ranking Functions Usage
The OLAP ranking functions let application developers compose single-statement SQL queries that answer questions such as “Name the top 10 products shipped this year by total sales,” or “Give the top 5% of salespeople who sold orders to at least 15 different companies.”
Statistical Aggregate Analytic Function Usage
Statistical aggregate analytic functions summarize data over a group of rows from the database.
Distribution Functions Usage
The inverse distribution analytical functions PERCENTILE_CONT and PERCENTILE_DISC take a percentile value as the function argument and operate on a group of data specified in the WITHIN GROUP clause, or operate on the entire data set.
Inter-Row Functions Usage
The inter-row functions LAG and LEAD enable access to previous values or subsequent values in a data series.

Parent topic: SQL Functions