SQL Function - Window Aggregate (Analytics function)
Table of Contents
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.
Partition in the clause statement define the subset.
The data property dense/parse is really important when using this functions.
Analytic functions allow to replace procedural by set based logic.
True streaming platform (not microbatch) will handles late arriving data. The window is never really closed and some late arriving data with a timestamp inside the window can arrive at any time.
Articles Related
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.
Functions
List of analytic functions
- COVAR_POP
- COVAR_SAMP
- CUME_DIST
- DENSE_RANK
- FIRST
- FIRST_VALUE
- LAST
- LAST_VALUE
- 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)