Purpose
STDDEV() returns the standard deviation of a set of numeric values. The difference between STDDEV() and STDDEV_SAMP() is that the STDDEV() function returns 0 for only one row of input data, whereas STDDEV_SAMP() returns NULL. The standard deviation value in the OceanBase Database is the square root of the variance returned by the VARIANCE() function. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
STDDEV([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates during the execution of the query. This parameter is optional. Default value: ALL.
|
| expr | An expression of a numeric data type or a data type that can be converted to a numeric data type. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Notice
If you specify the DISTINCT or UNIQUE keyword, you can specify only analytic_clause. order_by_clause and windowing_clause are not allowed.
Return type
The return type is NUMBER.
Examples
The table employees 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 for the salary column.
obclient> SELECT STDDEV(salary) FROM employees;
+-------------------------------------------+
| STDDEV(SALARY) |
+-------------------------------------------+
| 6026.474330580265330900400184969999384459 |
+-------------------------------------------+
1 row in set
Example of an analytic function
Sort the data records in the table by the hiredate column in ascending order, and return the cumulative standard deviation for 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
