Skip to main content

Categories: Aggregate, Window

VAR_SAMP

Returns the sample variance of non-NULL records.

Syntax​

VAR_SAMP(col_name NUMERIC) → NUMERIC​

  • col_name: The name of the column for which to return the sample variance. The values in the column must be numbers, such as INT, DOUBLE, or FLOAT.

Examples

VAR_SAMP example 
SELECT VAR_SAMP(passenger_count) FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- 1.868747683518558
VAR_SAMP example 
SELECT VAR_SAMP(tip_amount) FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- 5.106086065187043
VAR_SAMP example: Window function with sliding window frame 
SELECT city, state, pop, VAR_SAMP(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 4.1940229333333336E7
-- AKHIOK, AK, 13309, 3.7242330666666664E7
-- AKIACHAK, AK, 481, 4.1343588E7
-- ...

Usage Notes​

For single input records, VAR_SAMP returns NULL.

The VAR_SAMP function supports optional PARTITION BY, ORDER_BY, and sliding window frame subclauses. See Window Functions for more information and syntax.