Product Documentation

Appearance

VAR_SAMP and VAR_POP (window functions) ​

Given a set of integer, decimal, or floating-point numbers, return the sample variance or population variance, based on a window definition with an optional frame clause. The VAR_SAMP result is equivalent to the squared sample standard deviation of the same set of numbers. VAR_SAMP and VARIANCE are synonyms.

VAR_SAMP | VARIANCE (expression)
VAR_POP (expression)
OVER { window_name | ( [ window_definition ] ) }

The return type is either DOUBLE PRECISION (for floating-point inputs) or DECIMAL (for all other inputs).

For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# select teamid, avg_att, 
variance(capacity) over (partition by city order by avg_att rows between 1 preceding and 2 following) as varsamp 
from team where avg_att>0 order by 1,2;
 teamid | avg_att |     varsamp      
--------+---------+------------------
     1 |  59.944 | 172924204.500000
     2 |  33.690 | 124583112.500000
     8 |  11.189 |           [NULL]
    13 |  41.500 | 158271771.000000
    15 |  24.636 |  29823964.333333
    17 |  38.124 |  10219720.500000
    22 |  32.201 |           [NULL]
    23 |  43.910 |  10219720.500000
    24 |  54.041 | 210904722.000000
    25 |  75.286 | 210904722.000000
    27 |  49.754 |           [NULL]
    28 |  26.972 |           [NULL]
    36 |  30.751 |           [NULL]
    37 |  27.534 |           [NULL]
    38 |  43.071 |           [NULL]
    39 |  20.711 |           [NULL]
    41 |  35.776 | 136056716.250000
    42 |  20.594 |           [NULL]
    43 |  24.631 | 124583112.500000
    44 |  34.910 |  40788875.000000
(20 rows)

Note: The VARIANCE or VAR_SAMP function for an expression that consists of a single value returns NULL. The VAR_POP function for an expression that consists of a single value returns 0).

Parent topic:Window Functions