VAR_SAMP()

Aggregate. Computes the statistical variance of a sample consisting of a numeric-expression, as a DOUBLE.

Syntax

VAR_SAMP( numeric-expression )

Parameters
numeric-expression	The expression whose sample-based variance is calculated over a set of rows. The expression is commonly a column name.

Usage

This function converts its argument to DOUBLE, performs the computation in double-precision floating point, and returns a DOUBLE as the result. The variance (s2) of numeric-expression (x) is computed according to the following formula, which assumes a normal distribution:

s2 = (1/( N - 1 )) * SUM( xi - mean( x ) )2

This variance does not include rows where numeric-expression is NULL. It returns NULL for a group containing either 0 or 1 rows.

SQL/2003 SQL foundation feature outside of core SQL. The VARIANCE syntax is a vendor extension.

The following example demonstrates how to use the VAR_SAMP functionto calculate the statistical variance of a sample of data from 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,
    var_samp_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,
var_samp(x)
FROM StreamIn
KEEP 5 ROWS;

INSERT INTO StreamOut
SELECT *
FROM ResultWindow;