VAR_POP(expr)
Returns the population standard variance of expr
. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also use VARIANCE(), which is equivalent but is not standard SQL.
Variance is calculated by
It is an aggregate function, and so can be used with the GROUP BY clause.
From MariaDB 10.2.2, VAR_POP() can be used as a window function.
VAR_POP() returns NULL
if there were no matching rows.
CREATE TABLE v(i tinyint); INSERT INTO v VALUES(101),(99); SELECT VAR_POP(i) FROM v; +------------+ | VAR_POP(i) | +------------+ | 1.0000 | +------------+ INSERT INTO v VALUES(120),(80); SELECT VAR_POP(i) FROM v; +------------+ | VAR_POP(i) | +------------+ | 200.5000 | +------------+
As an aggregate function:
CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT); INSERT INTO stats VALUES ('a',1),('a',2),('a',3), ('b',11),('b',12),('b',20),('b',30),('b',60); SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x) FROM stats GROUP BY category; +----------+---------------+----------------+------------+ | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) | +----------+---------------+----------------+------------+ | a | 0.8165 | 1.0000 | 0.6667 | | b | 18.0400 | 20.1693 | 325.4400 | +----------+---------------+----------------+------------+
As a window function:
CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT); INSERT INTO student_test VALUES ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), ('Tatiana', 'SQL', 87); SELECT name, test, score, VAR_POP(score) OVER (PARTITION BY test) AS variance_results FROM student_test; +---------+--------+-------+------------------+ | name | test | score | variance_results | +---------+--------+-------+------------------+ | Chun | SQL | 75 | 287.1875 | | Chun | Tuning | 73 | 582.0000 | | Esben | SQL | 43 | 287.1875 | | Esben | Tuning | 31 | 582.0000 | | Kaolin | SQL | 56 | 287.1875 | | Kaolin | Tuning | 88 | 582.0000 | | Tatiana | SQL | 87 | 287.1875 | +---------+--------+-------+------------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/var_pop/