SQL Function - Window Aggregate (Analytics function)
Table of Contents
1 - About
Windowing functions (known also as analytics) allow to compute:
- and aggregates.
They are distinguished from ordinary SQL functions by the presence of an OVER clause.
With windowing aggregate functions, you can calculate moving and cumulative versions of SUM, AVERAGE, COUNT, MAX, MIN, and many more functions.
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window.
Analytic functions enable you to calculate:
- Rankings and percentiles
- First/last analysis
- Linear regression statistics
Analytic functions can be used in the following areas where standard SQL fails to impress.
- Ranking data within subsets of a data set.
- Aggregating data within subsets of a data set.
- Performing aggregations over moving windows.
- Displaying and comparing aggregates to individual entries within a single query.
- Comparing two or more rows within a given data set.
- Fetching from the translate table the values based on EFFDT.
Analytic functions allow to replace procedural by set based logic.
2 - Articles Related
3 - The process
Query processing using analytic functions takes place in three stages :
- all joins, WHERE, GROUP BY and HAVING clauses are performed.
- the result set is made available to the analytic functions, and all their calculations take place.
- if the query has an ORDER BY clause at its end, the ORDER BY is processed to allow for precise output ordering.
4 - Functions
List of analytic functions
- COVAR_POP
- COVAR_SAMP
- COUNT
- CUME_DIST
- DENSE_RANK
- FIRST
- FIRST_VALUE
- LAST
- LAST_VALUE
- max/min
- Ntile
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- RATIO_TO_REPORT
- REGR_ (Linear Regression) Functions
- STDDEV_POP (Population)
- STDDEV_SAMP (Sample)
- VAR_POP (Population)
- VAR_SAMP (Sample)