WEIGHTED_AVG()

Aggregate. Calculates an arithmetically (or linearly) weighted average.

Syntax

WEIGHTED_AVG( expression )

Parameters
expression A numeric expression for which a weighted value is to be computed.

An arithmetic weighted average is any average that has multiplying factors to give different weights to different data points. But in technical analysis a weighted moving average (WMA) has the specific meaning of weights which decrease arithmetically. In an n-day WMA the latest day has weight n, the second latest n − 1, etc, down to zero:

The graph at the right shows how the weights decrease, from highest weight for the most recent data points, down to zero. It can be compared to the weights in the exponential moving average which follows. It's important to remember that for more exaggerated weighting on the ongoing values, you may use an EMA. You could also average two or more WMA together.

Weighted Average Chart PNG

Sybase extension.

The following example demonstrates how to use the WEIGHTED_AVG functionto calculate the weighted average of data in a CSV file:
-- 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,
    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,
weighted_avg(x)
FROM StreamIn
KEEP 5 ROWS;

INSERT INTO StreamOut
SELECT *
FROM ResultWindow;