MaxCompute:STDDEV_SAMP

Limits

Before you use window functions, take note of the following limits:

• Window functions are supported only in SELECT statements.

• A window function cannot contain nested window functions or nested aggregate functions.

• You cannot use window functions together with aggregate functions of the same level.

Syntax

-- Calculate the sample standard deviation of all the values in a column.
double stddev_samp(double <colname>)
decimal stddev_samp(decimal <colname>)

-- Calculate the standard deviation of expr in a window.
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 name of a column, which can be of the DOUBLE or DECIMAL type. If the specified column is of the STRING or BIGINT type, the values in the column are implicitly converted into the DOUBLE type before calculation.

• expr: required. This parameter specifies the expression that is used to calculate the sample standard deviation. The input values can be of the DOUBLE or DECIMAL type.

  • If an input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation. If it is of another data type, an error is returned.

  • If the value in a row is null, this row is not used for calculation.

  • If the distinct keyword is specified, the sample standard deviation of distinct values is calculated.

• partition_clause, orderby_clause, and frame_clause: For more information about these parameters, see windowing_definition.

Return value

• If the value of the column specified by colname in a row is null, the row is not used for calculation. The following table describes the mappings between data types of input data and return values.

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

• A value of the same data type as expr is returned. If the values of all expressions specified by expr are null, null is returned. If the window has only one row of data whose expr value is not null, 0 is returned.

Sample data

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;

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

• Example 1: Use the deptno column to define a window and calculate the sample standard deviation of the sal column. The ORDER BY clause is not specified. This function returns the cumulative sample standard deviation of the current window. The current window includes the rows that have the same deptno value. Sample statement:

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

  The following result is returned:

  +------------+------------+------------+
  | deptno     | sal        | _c2        |
  +------------+------------+------------+
  | 10         | 1300       | 1693.7138680032904 |   -- This row is the first row of this window. The return value is the cumulative sample standard deviation of the values from the first row to the sixth row. 
  | 10         | 2450       | 1693.7138680032904 |   -- The return value is the cumulative sample standard deviation of the values from the first row to the sixth row. 
  | 10         | 5000       | 1693.7138680032904 |   -- The return value is the cumulative sample standard deviation of the values from the first row to the sixth row. 
  | 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: Use the deptno column to define a window and calculate the sample standard deviation of the sal column. The ORDER BY clause is specified. This function returns the cumulative sample standard deviation of the values from the first row to the current row in the current window. The current window includes the rows that have the same deptno value. Sample statement:

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

  The following result is returned:

  +------------+------------+------------+
  | deptno     | sal        | _c2        |
  +------------+------------+------------+
  | 10         | 1300       | 0.0        |          -- This row is the first row of this window. 
  | 10         | 1300       | 0.0        |          -- The return value is the cumulative sample standard deviation of the values in the first and second rows. 
  | 10         | 2450       | 663.9528095680697 |   -- The return value is the cumulative sample standard deviation of the values from the first row to the third row. 
  | 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 |
  +------------+------------+------------+

• Example 3: Calculate the sample standard deviation of salary (sal) values of all employees. Sample statement:

  select stddev_samp(sal) from emp;

  The following result is returned:

  +------------+
  | _c0        |
  +------------+
  | 1301.6180541247609 |
  +------------+

• Example 4: Use this function with GROUP BY to group all employees by department (deptno) and calculate the sample standard deviation of salary values of employees in each department. Sample statement:

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

  The following result is returned:

  +------------+------------+
  | deptno     | _c1        |
  +------------+------------+
  | 10         | 1693.7138680032901 |
  | 20         | 1123.3320969330487 |
  | 30         | 668.3312551921141 |
  +------------+------------+

Related functions

STDDEV_SAMP is an aggregate function or a window function.

• For more information about the functions that are used to calculate the average value of multiple input records and aggregate parameters, see Aggregate functions.

• For more information about the functions that are used to calculate the sum of data of columns in a window and sort data, see Window functions.