Windowing aggregate functions let you manipulate multiple levels of aggregation in the same query.
For example, you can list all quarters during which expenses are less than the average. You can use aggregate functions, including the simple aggregate functions AVG, COUNT, MAX, MIN, and SUM, to place results—possibly computed at different levels in the statement—on the same row. This placement provides a means to compare aggregate values with detail rows within a group, avoiding the need for a join or a correlated subquery.
These functions also let you compare nonaggregate values to aggregate values. For example, a salesperson might need to compile a list of all customers who ordered more than the average number of a product in a specified year, or a manager might want to compare an employee’s salary against the average salary of the department.
If a query specifies DISTINCT in the SELECT statement, then the DISTINCT operation is applied after the window operator. A window operator is computed after processing the GROUP BY clause and before the evaluation of the SELECT list items and a query’s ORDER BY clause.
Windowing aggregate example 1—This query returns a result set, partitioned by year, that shows a list of the products that sold higher-than-average sales.
SELECT * FROM (SELECT Surname AS E_name, DepartmentID AS Dept, CAST(Salary AS numeric(10,2) ) AS Sal, CAST(AVG(Sal) OVER(PARTITION BY DepartmentID) AS numeric(10, 2)) AS Average, CAST(STDDEV_POP(Sal) OVER(PARTITION BY DepartmentID) AS numeric(10,2)) AS STD_DEV FROM Employees GROUP BY Dept, E_name, Sal) AS derived_table WHERE Sal> (Average+STD_DEV ) ORDER BY Dept, Sal, E_name;
The results from the query:
E_name Dept Sal Average STD_DEV -------- ---- -------- ------- -------- Lull 100 87900.00 58736.28 16829.59 Sheffield 100 87900.00 58736.28 16829.59 Scott 100 96300.00 58736.28 16829.59 Sterling 200 64900.00 48390.94 13869.59 Savarino 200 72300.00 48390.94 13869.59 Kelly 200 87500.00 48390.94 13869.59 Shea 300 138948.00 59500.00 30752.39 Blaikie 400 54900.00 43640.67 11194.02 Morris 400 61300.00 43640.67 11194.02 Evans 400 68940.00 43640.67 11194.02 Martinez 500 55500.80 33752.20 9084.49
For the year 2000, the average number of orders was 1,787. Four products (700, 601, 600, and 400) sold higher than that amount. In 2001, the average number of orders was 1,048 and 3 products exceeded that amount.
Windowing aggregate example 2—This query returns a result set that shows the employees whose salary is one standard deviation greater than the average salary of their department. Standard deviation is a measure of how much the data varies from the mean.
SELECT * FROM (SELECT Surname AS E_name, DepartmentID AS Dept, CAST(Salary AS numeric(10,2) ) AS Sal, CAST(AVG(Sal) OVER(PARTITION BY dept) AS numeric(10, 2)) AS Average, CAST(STDDEV_POP(Sal) OVER(PARTITION BY dept) AS numeric(10,2)) AS STD_DEV FROM Employees GROUP BY Dept, E_name, Sal) AS derived_table WHERE Sal> (Average+STD_DEV ) ORDER BY Dept, Sal, E_name;
Every department has at least one employee whose salary significantly deviates from the mean, as shown in these results:
E_name Dept Sal Average STD_DEV -------- ---- -------- -------- -------- Lull 100 87900.00 58736.28 16829.59 Sheffield 100 87900.00 58736.28 16829.59 Scott 100 96300.00 58736.28 16829.59 Sterling 200 64900.00 48390.94 13869.59 Savarino 200 72300.00 48390.94 13869.59 Kelly 200 87500.00 48390.94 13869.59 Shea 300 138948.00 59500.00 30752.39 Blaikie 400 54900.00 43640.67 11194.02 Morris 400 61300.00 43640.67 11194.02 Evans 400 68940.00 43640.67 11194.02 Martinez 500 55500.80 33752.20 9084.49
Employee Scott earns $96,300.00, while the average salary for department 100 is $58,736.28. The standard deviation for department 100 is 16,829.00, which means that salaries less than $75,565.88 (58736.28 + 16829.60 = 75565.88) fall within one standard deviation of the mean.