VAR_SAMP Function

Description

The VAR_SAMP function returns the sample variance. The denominator is the number of all rows - 1. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is inserted before the expression, it calculates the sample variance after deleting duplicates and if the keyword is omitted or ALL, it calculates the sample 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 is returned.

The following are the formulas applied to the function.

VAR_SAMP = { 1 / (N-1) } * SUM( { x_I - mean(x) }² )

• SUM: Sum
• mean: Sample mean

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

VAR_SAMP( [ DISTINCT | UNIQUE | ALL] expression )

• expression: Specifies one expression to return the numeric.
• ALL: Is used to calculate the sample variance of unique values without duplicates. It is the default value.
• DISTINCT or UNIQUE: Is used to calculate the sample variance for the unique values without duplicates.

Example of Aggregate Function

The following example shows how to output the sample 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_SAMP(score) FROM student;

           var_samp(score)

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

     5.815238095238092e+02

Example of Analytic Function

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

SELECT subjects_id, name, score, VAR_SAMP(score) OVER(PARTITION BY subjects_id) v_samp

FROM student ORDER BY subjects_id, name;

  subjects_id  name                                     score                    v_samp

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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