Calculate population standard deviation using STDDEV - MaxCompute

STDDEV (alias: STDDEV_POP) calculates the population standard deviation of all non-null input values.

STDDEV works as both an aggregate function (with GROUP BY) and a window function (with OVER).

Limits

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions and aggregate functions of the same level cannot appear in the same query.

Usage notes

MaxCompute V2.0 data type edition

STDDEV supports additional data types (TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY) in the MaxCompute V2.0 data type edition. To use these types, enable the V2.0 edition at either the session or project level:

Session level: Add set odps.sql.type.system.odps2=true; before your SQL statement, then submit both together.
Project level: Run the following command. The change takes effect after 10 to 15 minutes.
```
setproject odps.sql.type.system.odps2=true;
```
For details, see Project operations and Data type editions.

Window function behavior with ORDER BY

When used as a window function with an ORDER BY clause:

Without a frame clause, the function computes the cumulative population standard deviation from the first row to the current row in the partition.
The behavior for rows with tied values depends on whether Hive-compatible mode is enabled. See Examples for a side-by-side comparison.

Memory usage

If a query includes multiple aggregate functions and project resources are insufficient, a memory overflow error may occur. Optimize the SQL statement or purchase additional computing resources as needed.

Syntax

-- Aggregate form: calculate the population standard deviation of all values in a column
double stddev(double <colname>)
decimal stddev(decimal <colname>)

-- Window form: calculate the population standard deviation of expr within a window
double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Parameters

colname: Required. The column to compute. Accepted types: DOUBLE, DECIMAL. STRING and BIGINT values are implicitly converted to DOUBLE before calculation.
expr: Required. The expression to compute. Accepted types: DOUBLE, DECIMAL. STRING and BIGINT values are implicitly converted to DOUBLE. Any other type causes an error. Null rows are excluded from calculation. Specify distinct to compute the standard deviation over distinct values only.
partition_clause, orderby_clause, frame_clause: See windowing_definition.

Return value

The return type matches the input type:

Input type	Return type
TINYINT	DOUBLE
SMALLINT	DOUBLE
INT	DOUBLE
BIGINT	DOUBLE
FLOAT	DOUBLE
DOUBLE	DOUBLE
DECIMAL	DECIMAL

Null rows are not included in the calculation. If all input values are null, the function returns null.

Sample data

The following examples use an emp table. To create it and load the sample data, run:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

The emp.txt file contains:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

The following examples show STDDEV used as an aggregate function (Examples 1–2) and as a window function (Examples 3–5), all against the same emp table.

Example 1: Population standard deviation across all rows

Calculate the population standard deviation of all sal values:

select stddev(sal) from emp;

Result:

+------------+
| _c0        |
+------------+
| 1262.7549932628976 |
+------------+

Example 2: Population standard deviation per group

Group employees by department and calculate the population standard deviation of salary per department:

select deptno, stddev(sal) from emp group by deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1546.1421524412158 |
| 20         | 1004.7387720198718 |
| 30         | 610.1001739241043 |
+------------+------------+

Example 3: Window function without ORDER BY

Use PARTITION BY to define a window and calculate the population standard deviation of sal per partition. Without ORDER BY, the function returns the same value for every row in the partition — the standard deviation across all rows in that partition:

select deptno, sal, stddev(sal) over (partition by deptno) from emp;

Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 1300       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 800        | 1004.7387720198718 |
| 20         | 1100       | 1004.7387720198718 |
| 20         | 2975       | 1004.7387720198718 |
| 30         | 1500       | 610.1001739241042 |
| 30         | 950        | 610.1001739241042 |
| 30         | 1600       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Example 4: Window function with ORDER BY (non-Hive-compatible mode)

With ORDER BY, the function computes the cumulative population standard deviation from the first row to the current row within the partition. In non-Hive-compatible mode, each row is evaluated individually, so tied sal values can produce different cumulative results:

-- Disable Hive-compatible mode
set odps.sql.hive.compatible=false;
-- Run the query
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- First row; stddev of a single value is 0
| 10         | 1300       | 0.0        |           -- Cumulative stddev of rows 1-2
| 10         | 2450       | 542.1151989096865 |    -- Cumulative stddev of rows 1-3
| 10         | 2450       | 575.0      |           -- Cumulative stddev of rows 1-4
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Example 5: Window function with ORDER BY (Hive-compatible mode)

In Hive-compatible mode, all rows with the same sal value share the same cumulative result — computed up to the last row with that value. This differs from non-Hive-compatible mode, where each row is evaluated individually:

-- Enable Hive-compatible mode
set odps.sql.hive.compatible=true;
-- Run the query
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- Rows 1 and 2 share the same sal; both get the cumulative stddev of rows 1-2
| 10         | 1300       | 0.0        |
| 10         | 2450       | 575.0      |           -- Rows 3 and 4 share the same sal; both get the cumulative stddev of rows 1-4
| 10         | 2450       | 575.0      |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Related functions

Aggregate functions: functions that aggregate multiple input rows into a single value
Window functions: functions that compute results over a defined window of rows