EXP_WEIGHTED_AVG function [Aggregate]

Function

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

Syntax

EXP_WEIGHTED_AVG (expression, period-expression)
OVER (window-spec)

window-spec: See the Usage section, below.

Parameters

expression A numeric expression for which a weighted value is being computed.

period-expression A numeric expression specifying the period for which the average is to be computed.

Usage

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.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement. The window-spec must contain an ORDER BY statement and cannot contain a frame specification. For information on how to specify the window, see “Analytical functions”.

NoteROLLUP and CUBE are not supported in the GROUP BY clause. DISTINCT is not supported.

Example

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:

Table 4-21: EXP_WEIGHTED_AVG result set

DepartmentID

Surname

Salary

W_AVG

400

Evans

68940.000

34470.000000

300

Litton

58930.000

46700.000000

200

Sterling

64900.000

55800.000000

200

Kelly

87500.000

71650.000000

400

Charlton

28300.000

49975.000000

100

Lull

87900.000

68937.500000

100

Gowda

59840.000

60621.875000

400

Francis

53870.000

61403.750000

Standards and compatibility