VAR_POP [Analytic]

Returns the statistical population variance of a non-null set of numbers (nulls are ignored) in a group within a window. Results are calculated by the sum of squares of the difference of expression from the mean of expression, divided by the number of rows remaining:

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

Behavior Type

Immutable

Syntax

VAR_POP ( 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

Examples

The following example calculates the cumulative population in the store orders fact table of sales in January 2007:

=> SELECT date_ordered,
      VAR_POP(SUM(total_order_cost)) 
      OVER (ORDER BY date_ordered) "var_pop"
   FROM store.store_orders_fact s
   WHERE date_ordered BETWEEN '2007-01-01' AND '2007-01-31'
   GROUP BY s.date_ordered;
 date_ordered |     var_pop
--------------+------------------
 2007-01-01   |                0
 2007-01-02   |         89870400
 2007-01-03   |       3470302472
 2007-01-04   |  4466755450.6875
 2007-01-05   | 3816904780.80078
 2007-01-06   |   25438212385.25
 2007-01-07   | 22168747513.1016
 2007-01-08   | 23445191012.7344
 2007-01-09   | 39292879603.1113
 2007-01-10   | 48080574326.9609
(10 rows)

See Also