Database Reference
In-Depth Information
customer_gender male
M 1
F 0
F 0
M 1
M 1
Built-in and user-defined functions can be incorporated into user-defined
aggregates, which can then be used as a window function. In Section 11.3.1, a
window function is used to calculate moving averages to smooth a data series. In
this section, a user-defined aggregate is created to calculate an
Exponentially
Weighted Moving Average (EWMA)
. For a given time series, the EWMA
series is defined as shown in
Equation 11.1
.
11.1
where 0 ≤
α
≤ 1
The smoothing factor, determines how much weight to place on the latest point in
a given time series. By repeatedly substituting into
Equation 11.1
for the prior value
of the EWMA series, it can be shown that the weights against the original series are
exponentially decaying backward in time.
To implement EWMA smoothing as a user-defined aggregate in SQL, the
functionality in
Equation 11.1
needs to be implemented first as a user-defined
function.
CREATE FUNCTION ewma_calc(numeric, numeric, numeric)
RETURNS numeric as
/* $1 = prior value of EWMA */
/* $2 = current value of series */
/* $3 = alpha, the smoothing factor */
'SELECT CASE
WHEN $3 IS NULL
/* bad alpha */
OR $3 < 0
OR $3 > 1 THEN NULL
WHEN $1 IS NULL THEN $2
/* t = 1 */
WHEN $2 IS NULL THEN $1
/* y is unknown */
