Purpose
This function calculates the standard deviation of a set of numeric data. The difference between this function and the STDDEV_SAMP function is that the STDDEV function returns 0 when only one row of data is provided, while the STDDEV_SAMP function returns NULL. In OceanBase Database, the standard deviation value is the arithmetic square root of the variance calculated by the VARIANCE function. It can be used 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 calculates the standard deviation for a set of rows and returns multiple values. - When used as an aggregate function, it aggregates the standard deviation for a set of rows and returns only one value. In this case, the
OVERclause is not required.
Syntax
STDDEV([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates. This is an optional parameter, with a default value of ALL.
|
| expr | A numeric value or a value that can be converted to a numeric value. |
| OVER | Specifies the OVER clause to define the window for calculation. For more information, see Analytic functions. |
Notice
If you specify the DISTINCT or UNIQUE keyword, the analytic_clause cannot contain the order_by_clause or windowing_clause.
Return type
The function returns a value of the NUMBER type.
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
Example of an aggregate function
Calculate the standard deviation of the salary column.
obclient> SELECT STDDEV(salary) FROM employees;
+-------------------------------------------+
| STDDEV(SALARY) |
+-------------------------------------------+
| 6026.474330580265330900400184969999384459 |
+-------------------------------------------+
1 row in set
Example of an analytic function
Sort the rows in ascending order by the hiredate column and calculate the cumulative standard deviation of the salary column.
obclient> SELECT last_name,hiredate,salary,STDDEV(salary) OVER (ORDER BY hiredate) "StdDev"
FROM employees;
+-----------+------------+--------+-------------------------------------------+
| LAST_NAME | HIREDATE | SALARY | StdDev |
+-----------+------------+--------+-------------------------------------------+
| Raphaely | 2017-07-01 | 1700 | 0 |
| Errazuriz | 2017-07-21 | 1400 | 212.132034355964257320253308631454711785 |
| Raphaely | 2017-07-22 | 1700 | 173.205080756887729352744634150587236694 |
| De Haan | 2018-05-01 | 11000 | 4702.127178203498995615489088200868644482 |
| Partners | 2018-12-01 | 14000 | 6064.899009876421676804205219406952308814 |
| Russell | 2019-07-11 | 13000 | 6138.94670661561181357873224397795992899 |
| Hartstein | 2019-10-05 | 14000 | 6026.474330580265330900400184969999384459 |
| Weiss | 2019-10-05 | 13500 | 6026.474330580265330900400184969999384459 |
+-----------+------------+--------+-------------------------------------------+
8 rows in set