EXP_WEIGHTED_AVG()

Syntax

EXP_WEIGHTED_AVG( expression, period-expression )

An exponential moving average (EMA), sometimes also called an exponentially weighted moving average (EWMA), applies weighting factors which decrease exponentially. The weighting for each older data point decreases exponentially, giving much more importance to recent observations while still not discarding older observations entirely. The graph at right shows an example of the weight decrease.

The degree of weighing decrease is expressed as a constant smoothing factor α, a number between 0 and 1. α may be expressed as a percentage, so a smoothing factor of 10% is equivalent to α=0.1. Alternatively, α may be expressed in terms of N time periods, where. For example,

N=19 is equivalent to α=0.1.

The observation at a time period t is designated Yt, and the value of the EMA at any time period t is designated St. S1 is undefined. S2 may be initialized in a number of different ways, most commonly by setting S2 to Y1, though other techniques exist, such as setting S2 to an average of the first 4 or 5 observations. The prominence of the S2 initialization's effect on the resultant moving average depends on α; smaller α values make the choice of S2 relatively more important than larger α values, since a higher α discounts older observations faster.

This type of moving average reacts faster to recent price changes than a simple moving average. The 12- and 26-day EMAs are the most popular short-term averages, and they are used to create indicators like the moving average convergence divergence (MACD) and the percentage price oscillator (PPO). In general, the 50- and 200-day EMAs are used as signals of long-term trends.

Sybase extension.

-- create input stream schema
CREATE SCHEMA InSchema (
    id STRING,
    x FLOAT,
    y FLOAT
);

-- create output stream schema
CREATE SCHEMA OutSchema (
    id STRING,
    x FLOAT,
    y FLOAT,
    exp_weighted_avg_result FLOAT,
);

-- create input stream
CREATE INPUT STREAM StreamIn
SCHEMA InSchema;

-- create master window
CREATE MASTER WINDOW ResultWindow
SCHEMA OutSchema
KEEP 5 ROWS
;

-- create output stream
CREATE OUTPUT STREAM StreamOut
SCHEMA OutSchema;

-- input stream read data from csv file by ReadFromCsvFileAdapterType adapter
ATTACH INPUT ADAPTER ReadFromCSVFile TYPE ReadFromCsvFileAdapterType
TO STREAM StreamIn
PROPERTIES
    FILENAME            = "$ProjectFolder\..\data\data.csv",
    TITLEROW            = "false",
    TIMESTAMPCOLUMN     = "false",
    RATE                = "1",
    USECURRENTTIMESTAMP = "true"
;

-- output stream write data to csv file by WriteToCsvFileAdapterType adapter
ATTACH OUTPUT ADAPTER WriteToCSVFile TYPE WriteToCsvFileAdapterType
TO STREAM StreamOut
PROPERTIES
    FILENAME = "$ProjectFolder\..\data\result.csv"
;

-- insert the calculated result to window
INSERT INTO ResultWindow
SELECT id,x,y,
exp_weighted_avg(x, y)
FROM StreamIn
KEEP 5 ROWS;

INSERT INTO StreamOut
SELECT *
FROM ResultWindow;