Purpose
The STDDEV_POP function calculates the population standard deviation of numeric data. The population standard deviation is the square root of the population variance. You can use this function as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for calculation. It computes the standard deviation for a set of rows and returns multiple values. - When used as an aggregate function, the function aggregates the standard deviation for a set of rows and returns a single value. In this case, you do not need to use the
OVERclause.
Syntax
STDDEV_POP( [ALL] expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| ALL | Specifies to consider all numeric columns. This is an optional parameter. The default value is ALL. |
| expr | A numeric type (NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE) or an expression that can be converted to a numeric type. |
| OVER | Use the OVER clause to define the window for calculation. For more information, see Analytic Function Description. |
Return type
The return type is the same as the data type of the expr parameter.
Examples
Assume that the employees table has been created.
obclient> SELECT * FROM employees;
+---------------+-----------+------------+--------+
| DEPARTMENT_ID | LAST_NAME | HIREDATE | SALARY |
+---------------+-----------+------------+--------+
| 30 | Raphaely | 2017-07-01 | 1700 |
| 30 | De Haan | 2018-05-01 | 11000 |
| 40 | Errazuriz | 2017-07-21 | 1400 |
| 50 | Hartstein | 2019-10-05 | 14000 |
| 50 | Raphaely | 2017-07-22 | 1700 |
| 50 | Weiss | 2019-10-05 | 13500 |
| 90 | Russell | 2019-07-11 | 13000 |
| 90 | Partners | 2018-12-01 | 14000 |
+---------------+-----------+------------+--------+
8 rows in set
Aggregate function example
Calculate the population standard deviation of the salary column.
obclient> SELECT STDDEV_POP(salary) FROM employees;
+-------------------------------------------+
| STDDEV_POP(SALARY) |
+-------------------------------------------+
| 5637.250548804798333699350384281939588505 |
+-------------------------------------------+
1 row in set
Analytic function example
Group by the department_id column and calculate the population standard deviation of the salary column.
obclient> SELECT department_id,last_name,salary,STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
ORDER BY department_id,last_name,salary;
+---------------+-----------+--------+-------------------------------------------+
| DEPARTMENT_ID | LAST_NAME | SALARY | POP_STD |
+---------------+-----------+--------+-------------------------------------------+
| 30 | De Haan | 11000 | 4650 |
| 30 | Raphaely | 1700 | 4650 |
| 40 | Errazuriz | 1400 | 0 |
| 50 | Hartstein | 14000 | 5684.090858606052304285807872404592677763 |
| 50 | Raphaely | 1700 | 5684.090858606052304285807872404592677763 |
| 50 | Weiss | 13500 | 5684.090858606052304285807872404592677763 |
| 90 | Partners | 14000 | 500 |
| 90 | Russell | 13000 | 500 |
+---------------+-----------+--------+-------------------------------------------+
8 rows in set