All Products
Search
Document Center

STDDEV

Last Updated: Jun 18, 2021

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 |
+-------------------------------------------+