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
Syntax
VAR_SAMP ( expression ) OVER ( ... [ window-partition-clause ] ... [ window-order-clause ] ... [ window-frame-clause ] )
Parameters
expression |
Any |
OVER()
|
Null Handling
VAR_SAMP
returns the sample variance of a set of numbers after it discards theNULL
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)