The STDDEV function calculates the population standard deviation. The STDDEV function uses numeric data as arguments and returns numeric data. The difference between this function and the STDDEV_SAMP function is that if only one row of input data is available, STDDEV returns 0 but STDDEV_SAMP returns NULL.
In ApsaraDB for OceanBase, the value of the standard deviation is the arithmetic square root of the variance that is calculated by the VARIANCE function.
Syntax
STDDEV([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | Removes duplicate keywords. This indicates that the population standard deviation of unique values is calculated. |
| UNIQUE | Removes duplicate keywords. This indicates that the population standard deviation of unique values is calculated. |
| ALL | All the numeric columns. |
| expr | The numeric type or the types that can be converted to the numeric type. |
| OVER | Uses the OVER clause to define a window for calculation. |
Notice
If you specify the DISTINCT or UNIQUE keyword, order_by_clause and windowing_clause cannot appear in analytic_clause.
Return type
The data of the NUMBER type is returned.
Examples
Examples of the analytic function
The following statements create the employees table and insert data into the table:
CREATE TABLE employees(manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
COMMIT;
Call the function and execute the following statement:
SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hiredate) "StdDev"
FROM employees WHERE manager_id = 100 ORDER BY last_name, salary, "StdDev";
The following query result is returned:
+-----------+--------+-------------------------------------------+
| LAST_NAME | SALARY | StdDev |
+-----------+--------+-------------------------------------------+
| De Haan | 11000 | 4702.127178203498995615489088200868644482 |
| Errazuriz | 1400 | 212.132034355964257320253308631454711785 |
| Hartstein | 14000 | 6340.346993658943269176828928801701088079 |
| Partners | 14000 | 6064.899009876421676804205219406952308814 |
| Raphaely | 1700 | 0 |
| Raphaely | 1700 | 173.205080756887729352744634150587236694 |
| Russell | 13000 | 6026.474330580265330900400184969999384459 |
| Weiss | 13500 | 6244.311697171159907069428668980211861012 |
+-----------+--------+-------------------------------------------+
Examples of the aggregate function
Call the function and execute the following statement:
SELECT STDDEV(salary) FROM employees WHERE manager_id = 100 ;
The following query result is returned:
+-------------------------------------------+
| STDDEV(SALARY) |
+-------------------------------------------+
| 6026.474330580265330900400184969999384459 |
+-------------------------------------------+