VAR_POP/VARIANCE Functions

Description

The functions VARPOP and VARIANCE are used interchangeably and they return a variance of expression values for all rows. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is inserted before the expression, they calculate the population variance after deleting duplicates; if the keyword is omitted or ALL, they calculate the sample population variance for all values.

The return value is a DOUBLE type. If there are no rows that can be used for calculating a result, NULL will be returned.

The following is a formula that is applied to the function.

VAR_POP = (1/N) * SUM( { x_I - AVG(x) }² )

• SUM: Sum
• AVG: Average

For analytic function examples, see the following analytic function examples. For more information, see Overview.

Note In CUBRID 2008 R3.1 or earlier, the VARIANCE function worked the same as the VAR_SAMP Function.

Syntax

VAR_POP( [ DISTINCT | UNIQUE | ALL] expression )

• expression: Specifies an expression that returns a numeric value.
• ALL: Gets the variance for all values (default).
• DISTINCT or UNIQUE: Gets the variance of unique values without duplicates.

Example of Aggregate Function

The following example shows how to output the population variance of all students for all subjects

CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE);

INSERT INTO student VALUES

('Jane',1, 78),

('Jane',2, 50),

('Jane',3, 60),

('Bruce', 1, 63),

('Bruce', 2, 50),

('Bruce', 3, 80),

('Lee', 1, 85),

('Lee', 2, 88),

('Lee', 3, 93),

('Wane', 1, 32),

('Wane', 2, 42),

('Wane', 3, 99),

('Sara', 1, 17),

('Sara', 2, 55),

('Sara', 3, 43);

SELECT VAR_POP(score) FROM student;

            var_pop(score)

==========================

     5.427555555555550e+02

Example of Analytic Function

The following example shows how to output the score and population variance of all students by subject (subjects_id).

SELECT subjects_id, name, score, VAR_POP(score) OVER(PARTITION BY subjects_id) v_pop

FROM student ORDER BY subjects_id, name;

  subjects_id  name                                     score                     v_pop

=======================================================================================

            1  'Bruce'                  6.300000000000000e+01     6.931999999999998e+02

            1  'Jane'                   7.800000000000000e+01     6.931999999999998e+02

            1  'Lee'                    8.500000000000000e+01     6.931999999999998e+02

            1  'Sara'                   1.700000000000000e+01     6.931999999999998e+02

            1  'Wane'                   3.200000000000000e+01     6.931999999999998e+02

            2  'Bruce'                  5.000000000000000e+01     2.575999999999999e+02

            2  'Jane'                   5.000000000000000e+01     2.575999999999999e+02

            2  'Lee'                    8.800000000000000e+01     2.575999999999999e+02

            2  'Sara'                   5.500000000000000e+01     2.575999999999999e+02

            2  'Wane'                   4.200000000000000e+01     2.575999999999999e+02

            3  'Bruce'                  8.000000000000000e+01     4.348000000000002e+02

            3  'Jane'                   6.000000000000000e+01     4.348000000000002e+02

            3  'Lee'                    9.300000000000000e+01     4.348000000000002e+02

            3  'Sara'                   4.300000000000000e+01     4.348000000000002e+02

            3  'Wane'                   9.900000000000000e+01     4.348000000000002e+02