Aggregate functions support partitioning, ordering and framing. Below we can see example of this.
SELECT Part, Number, SUM( Number) OVER ( PARTITION BY Part ORDER BY Part DESC, Number NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Col1 FROM aggWindow ORDER BY Part, Number DESC;
We can use WINDOW definition to show all of the classical aggregation functions. Our window is defined as (), so we will aggregate the whole column.
SELECT Part, Number, SUM( Number ) OVER W AS SumCol, MAX( Number ) OVER W AS MaxCol, MIN( Number ) OVER W AS MinCol, AVG( Number ) OVER W AS AvgCol, PROD( Number ) OVER W AS ProdCol FROM aggWindow WINDOW W AS ( ) ORDER BY Number;
We can use listagg and group_concat functions to concatenate text from our column.
SELECT listagg( Part, ';' ) OVER W AS listAggCol, group_concat( Part, '-' ) OVER W AS groupConcatCol FROM aggWindow WINDOW W AS ( );
Running Aggregations
Aggregate functions are used to calculate cumulative values. In this case we create our frame relative to current row and we use ORDER BY.
SELECT Number, SUM( Number ) OVER ( ORDER BY Number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As Col1 FROM aggWindow;
Moving Aggregations
We can also use aggregate functions for calculation of moving aggregations.
SELECT Number, AVG( Number ) OVER ( ORDER BY Number ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) As Col1 FROM aggWindow;
Usage of window functions in ORDER BY
Window functions can be used only after all data from our query is created. That means that Window functions can be used only in SELECT and ORDER BY clauses. Here is the usage of Window function in ORDER BY.
SELECT Part, Number FROM aggWindow ORDER BY COUNT(Number) OVER (PARTITION BY Part);
Combining WINDOW functions and GROUPBY clause
If we combine WINDOW functions and GROUP BY clause, there is a difference between what these functions can see. GROUP BY calculation can only see one group, but it can see all the detail rows inside of that group. WINDOW function can see all the groups, but it can not see detail rows.
SELECT Part, SUM( Number ) AS Total FROM aggWindow GROUP BY Part ORDER BY Part;
GROUP BY will sum detail rows for each group separately. It can not combine values from different groups.
SELECT Part, SUM(SUM( Number )) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Total FROM aggWindow GROUP BY Part ORDER BY Part;
WINDOW function will see only those rows that are returned after GROUP BY finished its calculation. WINDOW function can see only totals for the groups, and not the detail rows. WINDOW function can work with those totals, from all of the groups, at the same time.
It seems that GROUP BY and WINDOW functions are of equal power. But that is not the truth. WINDOW functions are more powerful. We can preserve detail rows in the original columns, and we can create new columns with aggregated values. This allow us to compare detail and aggregated values. This is not possible with GROUP BY.
Look, we now have detail values and aggregated values in the same row. We can now easily calculate share of details in the total.
SELECT Part, Number, SUM( Number ) OVER ( PARTITION BY Part ) AS Total FROM aggWindow ORDER BY Part, Number;
SELECT Part, Number, SUM( Number ) OVER ( PARTITION BY Part ) AS Total, 100 * Number / SUM( Number ) OVER ( PARTITION BY Part ) AS "Share%" FROM aggWindow ORDER BY Part, Number;
The last column will show us share as a percentage.
Statistical Aggregate Functions
Statistical aggregate functions can be used in the same way as the classical aggregate functions.
In one of the earlier posts, we saw how functions CORR, COVAR_POP and COVAR_SAMP return wrong result. Contrary to that, if these functions are used inside of a WINDOW function, their results will be correct.
SELECT Part, Number, CORR( Number, Number - 1 ) OVER W AS CorrCol, COVAR_POP( Number, Number - 1 ) OVER W AS CovarPopCol, COVAR_SAMP( Number, Number - 1 ) OVER W AS CovarSampCol, STDDEV_POP( Number ) OVER W AS StdDevPopCol, STDDEV_SAMP( Number ) OVER W AS StdDevSampCol, VAR_POP( Number ) OVER W AS VarPopCol, VAR_SAMP( Number ) OVER W AS VarSampCol FROM aggWindow WINDOW W AS ( ) ORDER BY Number;
