EXP_WEIGHTED_AVG function [Aggregate]

Calculates an exponential weighted moving average. Weightings determine the relative importance of each quantity that makes up the average.

EXP_WEIGHTED_AVG (expression, period-expression)

OVER (window-spec)

window-spec: See the Usage section, below.

Similar to the WEIGHTED_AVG function, the weights in EXP_WEIGHTED_AVG decrease over time. However, weights in WEIGHTED_AVG decrease arithmetically, whereas weights in EXP_WEIGHTED_AVG decrease exponentially. Exponential weighting applies more weight to the most recent values, and decreases the weight for older values while still applying some weight.

Sybase IQ calculates the exponential moving average using:

S*C+(1-S)*PEMA

In the calculation above, IQ applies the smoothing factor by multiplying the current closing price (C) by the smoothing constant (S) added to the product of the previous day’s exponential moving average value (PEMA) and 1 minus the smoothing factor.

Sybase IQ calculates the exponential moving average over the entire period specified by the OVER clause. period-expression specifies the moving range of the exponential moving average.

The following example returns an exponential weighted average of salaries for employees in Florida with the salary of recently hired employees contributing the most weight to the average. There are three rows used in the weighting:

SELECT DepartmentID, Surname, Salary,EXP_WEIGHTED_AVG(Salary, 3) OVER (ORDER BY YEAR(StartDate) DESC) as "W_AVG"FROM EmployeesWHERE State IN ('FL') ORDER BY StartDate DESC

The returned result set is: