VARIANCE , VARIANCE_SAMP | Snowflake Documentation (original) (raw)

Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.

Aliases:

VAR_SAMP

Syntax¶

Aggregate function

VARIANCE( [ DISTINCT ] )

Window function

VARIANCE( [ DISTINCT ] ) OVER ( [ PARTITION BY ] [ ORDER BY [ ASC | DESC ] [ ] ] )

For detailed _windowframe_ syntax, see Window function syntax and usage.

Arguments¶

_expr1_

The _expr1_ should evaluate to one of the numeric data types.

_expr2_

This is the expression to partition by.

_expr3_

This is the expression to order by within each partition.

Returns¶

The data type of the returned value is NUMBER(<precision>, <scale>). The scale depends upon the values being processed.

Usage notes¶

• For single-record inputs, VAR_SAMP, VARIANCE, and VARIANCE_SAMP all return NULL. This is different from the Oracle behavior, where VAR_SAMP returns NULL for a single record and VARIANCE returns 0.
• When passed a VARCHAR expression, this function implicitly casts the input to floating point values. If the cast cannot be performed, an error is returned.
• When this function is called as a window function with an OVER clause that contains an ORDER BY clause:
  • A window frame is required. If no window frame is specified explicitly, the following implied window frame is used:
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    For more information about window frames, including syntax, usage notes, and examples, see Window function syntax and usage.
  • Using the keyword DISTINCT inside the window function is prohibited and results in a compile-time error.

Examples¶

For examples, see VAR_SAMP.