EXP_WEIGHTED_AVG Function [Aggregate]

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)

Parameters

Parameter	Description
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, Sybase 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.

Note: ROLLUP 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:

EXP_WEIGHTED_AVG result set
DepartmentID	Surname	Salary	W_AVG
400	Evans	68,940.000	34,470.000000
300	Litton	58,930.000	46,700.000000
200	Sterling	64,900.000	55,800.000000
200	Kelly	87,500.000	71,650.000000
400	Charlton	28,300.000	49,975.000000
100	Lull	87,900.000	68,937.500000
100	Gowda	59,840.000	60,621.875000
400	Francis	53,870.000	61,403.750000

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.