Purpose
This function calculates the population standard deviation of numeric data. The population standard deviation is the square root of the population variance. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, the
OVERclause must be used to define the window for calculation. It computes the values for a set of rows and returns multiple values. - When used as an aggregate function, the function aggregates the values for a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
STDDEV_POP( [ALL] expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| ALL | Specifies 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 | Defines the window for calculation using the OVER clause. 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