Windowing aggregate functions

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 ASDept, CAST(Salary AS numeric(10,2) ) AS Sal,CAST(AVG(Sal) OVER(PARTITION BY DepartmentID) ASnumeric(10, 2)) AS Average, CAST(STDDEV_POP(Sal)OVER(PARTITION BY DepartmentID) AS numeric(10,2)) ASSTD_DEVFROM EmployeesGROUP BY Dept, E_name, Sal) AS derived_table WHERESal> (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.59Sheffield 		 100			87900.00			58736.28			16829.59Scott 			 100		96300.00			58736.28			16829.59Sterling 		 	 200		64900.00			48390.94			13869.59Savarino 		 	 200		72300.00			48390.94			13869.59Kelly 			 200		87500.00			48390.94			13869.59Shea 			 300		138948.00			59500.00			30752.39Blaikie 			 400		54900.00			43640.67			11194.02Morris 			 400		61300.00			43640.67			11194.02Evans 			 400		68940.00			43640.67			11194.02Martinez 			 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.