Purpose
This function calculates the standard deviation of a set of numeric data. It differs from the STDDEV_SAMP function in that STDDEV returns 0 when there is only one input row, while STDDEV_SAMP returns NULL. In OceanBase Database, the standard deviation 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 and returns multiple values for a set of rows. - When used as an aggregate function, it aggregates and calculates a set of rows, returning 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 eliminate duplicates. Optional. Default value is 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 order_by_clause and windowing_clause cannot be specified in the analytic_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
Aggregate function example
Calculate the standard deviation of the salary column.
obclient> SELECT STDDEV(salary) FROM employees;
+-------------------------------------------+
| STDDEV(SALARY) |
+-------------------------------------------+
| 6026.474330580265330900400184969999384459 |
+-------------------------------------------+
1 row in set
Analytic function example
Sort the data 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