WEIGHTED_AVG function [Aggregate]

Calculates an arithmetically (or linearly) weighted average. A weighted average is an average in which each quantity to be averaged is assigned a weight. Weightings determine the relative importance of each quantity that make up the average.

WEIGHTED_AVG (expression)

OVER (window-spec)

window-spec: See the Usage section, below.

Use the WEIGHTED_AVG function to create a weighted moving average. In a weighted moving average, weights decrease arithmetically over time. Weights decrease from the highest weight for the most recent data points, down to zero.

To exaggerate the weighting, you can average two or more weighted moving averages together, or use an EXP_WEIGHTED_AVG function instead.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement.

window-spec:

• Must contain an ORDER BY specifier.

• Cannot contain FOLLOWING or RANGE specifiers.

• The second argument of the ROW specifier—if provided—must be CURRENT ROW.

• Cannot contain NULL values.

• Cannot contain the DISTINCT specifier.

• UNBOUNDED PRECEDING is supported, but may result in poor performance if used

The following example returns a weighted average of salaries by department for employees in Florida, with the salary of recently hired employees contributing the most weight to the average:

SELECT DepartmentID, Surname, Salary,
WEIGHTED_AVG(Salary) OVER (PARTITION BY DepartmentID
ORDER BY YEAR(StartDate) DESC) as "W_AVG"
FROM Employees
WHERE State IN ('FL') ORDER BY DepartmentID

The returned result set is: