STDDEV_SAMP calculates the sample standard deviation of a set of values. Use it as an aggregate function with GROUP BY to compute per-group deviations, or as a window function with an OVER clause to compute rolling or partition-level deviations.
Limitations
Window functions have the following limitations:
Supported only in
SELECTstatements.Cannot contain nested window functions or nested aggregate functions.
Cannot be used together with aggregate functions at the same query level.
Syntax
-- Aggregate function
DOUBLE STDDEV_SAMP(DOUBLE <colname>)
DECIMAL STDDEV_SAMP(DECIMAL <colname>)
-- Window function
DOUBLE STDDEV_SAMP([DISTINCT] <expr>) OVER ([partition_clause] [orderby_clause] [frame_clause])
DECIMAL STDDEV_SAMP([DISTINCT] <expr>) OVER ([partition_clause] [orderby_clause] [frame_clause])Parameters
colname
Required. The column to aggregate. Accepts DOUBLE or DECIMAL. STRING and BIGINT values are implicitly converted to DOUBLE; other types return an error.
expr
Required. The expression to evaluate. Must resolve to DOUBLE or DECIMAL.
STRING and BIGINT values are implicitly converted to DOUBLE. Other types return an error.
NULL values are excluded from the calculation.
With
DISTINCT, the function operates on unique values only.
partition_clause, orderby_clause, frame_clause
Optional. Define the window scope. For full syntax details, see windowing_definition.
Return value
The return type depends on the input:
| Input type | Return type |
|---|---|
| TINYINT | DOUBLE |
| SMALLINT | DOUBLE |
| INT | DOUBLE |
| BIGINT | DOUBLE |
| FLOAT | DOUBLE |
| DOUBLE | DOUBLE |
| DECIMAL | DECIMAL |
For the window function form, the return type matches the type of expr. If all input values are NULL, the function returns NULL.
Single non-NULL value behavior: By default, if the window or group contains only one non-NULL value, STDDEV_SAMP returns 0. To match BigQuery behavior, run SET odps.sql.bigquery.compatible=true; before executing your query — this causes the function to return NULL instead of 0.
Sample data
The following examples use an emp table. Create the table and load the sample data with these statements:
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; -- Replace emp.txt with the actual path to your data file.The emp.txt file contains the following data:
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,,10Examples
Example 1: Partition-level sample standard deviation
Calculate the sample standard deviation of salaries (sal) for each department (deptno). Because no ORDER BY clause is specified, the function computes the standard deviation over all rows in each partition and returns the same value for every row in that partition.
SELECT deptno, sal, STDDEV_SAMP(sal) OVER (PARTITION BY deptno)
FROM emp;Result:
+------------+------------+--------------------+
| deptno | sal | _c2 |
+------------+------------+--------------------+
| 10 | 1300 | 1693.7138680032904 |
| 10 | 2450 | 1693.7138680032904 |
| 10 | 5000 | 1693.7138680032904 |
| 10 | 1300 | 1693.7138680032904 |
| 10 | 5000 | 1693.7138680032904 |
| 10 | 2450 | 1693.7138680032904 |
| 20 | 3000 | 1123.3320969330487 |
| 20 | 3000 | 1123.3320969330487 |
| 20 | 800 | 1123.3320969330487 |
| 20 | 1100 | 1123.3320969330487 |
| 20 | 2975 | 1123.3320969330487 |
| 30 | 1500 | 668.331255192114 |
| 30 | 950 | 668.331255192114 |
| 30 | 1600 | 668.331255192114 |
| 30 | 1250 | 668.331255192114 |
| 30 | 1250 | 668.331255192114 |
| 30 | 2850 | 668.331255192114 |
+------------+------------+--------------------+Example 2: Cumulative sample standard deviation
Calculate the cumulative sample standard deviation of salaries (sal) for each department. The window is partitioned by deptno and ordered by sal, so the function accumulates from the first row to the current row within each partition.
SELECT deptno, sal, STDDEV_SAMP(sal) OVER (PARTITION BY deptno ORDER BY sal)
FROM emp;Result:
+------------+------------+--------------------+
| deptno | sal | _c2 |
+------------+------------+--------------------+
| 10 | 1300 | 0.0 |
| 10 | 1300 | 0.0 |
| 10 | 2450 | 663.9528095680697 |
| 10 | 2450 | 663.9528095680696 |
| 10 | 5000 | 1511.2081259707413 |
| 10 | 5000 | 1693.7138680032904 |
| 20 | 800 | 0.0 |
| 20 | 1100 | 212.13203435596427 |
| 20 | 2975 | 1178.7175234126282 |
| 20 | 3000 | 1182.7536725793752 |
| 20 | 3000 | 1123.3320969330487 |
| 30 | 950 | 0.0 |
| 30 | 1250 | 212.13203435596427 |
| 30 | 1250 | 173.20508075688772 |
| 30 | 1500 | 225.0 |
| 30 | 1600 | 253.4758371127315 |
| 30 | 2850 | 668.331255192114 |
+------------+------------+--------------------+The first row of each partition returns 0.0 because the standard deviation of a single value is zero (MaxCompute default behavior).
Example 3: Overall sample standard deviation
Calculate the sample standard deviation of salaries across all employees.
SELECT STDDEV_SAMP(sal) FROM emp;Result:
+--------------------+
| _c0 |
+--------------------+
| 1301.6180541247609 |
+--------------------+Example 4: Sample standard deviation with GROUP BY
Group employees by department and calculate the sample standard deviation of salaries for each group.
SELECT deptno, STDDEV_SAMP(sal)
FROM emp
GROUP BY deptno;Result:
+------------+--------------------+
| deptno | _c1 |
+------------+--------------------+
| 10 | 1693.7138680032901 |
| 20 | 1123.3320969330487 |
| 30 | 668.3312551921141 |
+------------+--------------------+Related functions
STDDEV_SAMP works as both an aggregate function and a window function.
For aggregate functions, see Aggregate functions.
For window functions, see Window functions.