Windowing aggregate functions let you manipulate multiple levels of aggregation in the same query. For example, listing all quarters in which expenses are less than the average. Aggregate functions, including the simple aggregate functions AVG, COUNT, MAX, MIN, and SUM, can be used 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 non-aggregate 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 In this example, the query returns a result set, partitioned by year, that shows a list of the products that sold higher-than-average sales.
SELECT * FROM (SELECT year(order_date) AS Y, prod_id, SUM(quantity) AS Q, CAST(AVG(SUM(quantity)) OVER (PARTITION BY Y) AS numeric (8, 2)) AS Average FROM alt_sales_order S, alt_sales_order_items O WHERE S.id = O.id GROUP BY Y, O.prod_id ) AS derived_table WHERE Q > Average ORDER BY Y, prod_id;
The following are the results from the query:
Year prod_id Q Average ---- ------- ---- ------- 2000 400 2030 1787.00 2000 600 2124 1787.00 2000 601 1932 1787.00 2000 700 2700 1787.00 2001 400 1248 1048.90 2001 401 1057 1048.90 2001 700 1836 1048.90
For the year 1993, the average number of orders was 1,787. Four products (700, 601, 600, and 400) sold higher than that amount. In 1994, the average number of orders was 1,048 and three products exceeded that amount.
Windowing aggregate example 2 In this example, the 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 emp_lname AS E_name, dept_id AS Dept, CAST(salary AS numeric(10,2) ) AS Sal, CAST(AVG(Sal) OVER(PARTITION BY dept_id) AS numeric(10, 2)) AS Average, CAST(STDDEV_POP(Sal) OVER(PARTITION BY dept_id) AS numeric(10,2)) AS STD_DEV FROM employee GROUP BY Dept, E_name, Sal) AS derived_table WHERE Sal> (Average+STD_DEV ) ORDER BY Dept, Sal, E_name;
The results of this query are as follows:. Every department has at least one employee whose salary significantly deviates from the mean.
Employee Dept Salary 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 departmental average is $58,736.28. The standard deviation for that department 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. At $96,300.00, employee Scott is well above that figure.
