Purpose
You can call this function to calculate the standard deviation of a group of numeric data. 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 it as an aggregate or analytic function.
Note
- When you use it 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 it 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 | (Optional) Specifies whether to remove duplicates during the query. Default value: ALL.
|
| expr | A numeric value or an expression that evaluates to a numeric value. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Notice
If you specify the
DISTINCTorUNIQUEkeyword, you can only specify theanalytic_clause. Theorder_by_clauseandwindowing_clauseare not allowed.
Return types
This function returns data of the NUMBER data type.
Examples
Assume that you have created the table employees.
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 example
Calculate the standard deviation for the salary column.
obclient> SELECT STDDEV(salary) FROM employees;
+-------------------------------------------+
STDDEV(SALARY)
+-------------------------------------------+
6026.474330580265330900400184969999384459
+-------------------------------------------+
1 row in set
Analytic example
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
