VARIANCE [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 )
VARIANCE
returns the variance of expression, which is calculated as follows:
- 0 if the number of rows in expression = 1
VAR_SAMP
if the number of rows in expression > 1
The nonstandard function VARIANCE
is provided for compatibility with other databases. It is semantically identical to VAR_SAMP
.
Behavior Type
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()
|
Examples
The following example calculates the cumulative variance in the store orders fact table of sales in December 2007:
=> SELECT date_ordered, VARIANCE(SUM(total_order_cost)) OVER (ORDER BY date_ordered) "variance" FROM store.store_orders_fact s WHERE date_ordered BETWEEN '2007-12-01' AND '2007-12-31' GROUP BY s.date_ordered; date_ordered | variance --------------+------------------ 2007-12-01 | NaN 2007-12-02 | 2259129762 2007-12-03 | 1809012182.33301 2007-12-04 | 35138165568.25 2007-12-05 | 26644110029.3003 2007-12-06 | 25943125234 2007-12-07 | 23178202223.9048 2007-12-08 | 21940268901.1431 2007-12-09 | 21487676799.6108 2007-12-10 | 21521358853.4331 (10 rows)