VAR_SAMP [Analytic]

Returns the sample variance of a non-NULL set of numbers (NULL values in the set are ignored) for each row of the group within a window. Results are calculated as follows:

(SUM( expression * expression ) - SUM( expression ) * SUM( expression ) / COUNT( expression ) ) 
/ (COUNT( expression ) - 1 )

This function and VARIANCE differ in one way: given an input set of one element, VARIANCE returns 0 and VAR_SAMP returns NULL.

Behavior Type

Immutable

Syntax

VAR_SAMP ( expression ) OVER ( 
    [ window-partition-clause ] 
    [ window-order-clause ]
    [ window-frame-clause ] )

Parameters

expression

Any NUMERIC data type or any non-numeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument
OVER()

See Analytic Functions

Null Handling

  • VAR_SAMP returns the sample variance of a set of numbers after it discards the NULL values in the set.
  • If the function is applied to an empty set, then it returns NULL.

Examples

The following example calculates the sample variance in the store orders fact table of sales in December 2007:

=> SELECT date_ordered,
      VAR_SAMP(SUM(total_order_cost)) 
      OVER (ORDER BY date_ordered) "var_samp"
   FROM store.store_orders_fact s
   WHERE date_ordered BETWEEN '2007-12-01' AND '2007-12-31'
   GROUP BY s.date_ordered;
 date_ordered |     var_samp
--------------+------------------
 2007-12-01   |              NaN
 2007-12-02   |      90642601088
 2007-12-03   | 48030548449.3359
 2007-12-04   | 32740062504.2461
 2007-12-05   | 32100319112.6992
 2007-12-06   |  26274166814.668
 2007-12-07   | 23017490251.9062
 2007-12-08   | 21099374085.1406
 2007-12-09   | 27462205977.9453
 2007-12-10   | 26288687564.1758
(10 rows)

See Also