STDDEV [Analytic]

Computes the statistical sample standard deviation of the current row with respect to the group within a window. STDDEV_SAMP returns the same value as the square root of the variance defined for the VAR_SAMP function:

STDDEV( expression ) = SQRT(VAR_SAMP( expression ))

When VAR_SAMP returns NULL, this function returns NULL.

The nonstandard function STDDEV is provided for compatibility with other databases. It is semantically identical to STDDEV_SAMP.

Behavior Type

Immutable

Syntax

STDDEV ( expression ) OVER ( 
    [ window-partition-clause ] 
    [ window-order-clause ]
    [ window-frame-clause ] )

Parameters

expression

Any NUMERIC data type or any non-numeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

OVER()

See Analytic Functions

Example

The following example returns the standard deviations of salaries in the employee dimension table by job title Assistant Director:

=> SELECT employee_last_name, annual_salary,
       STDDEV(annual_salary) OVER (ORDER BY hire_date) as "stddev"
   FROM employee_dimension  
   WHERE job_title =  'Assistant Director';
 employee_last_name | annual_salary |      stddev
--------------------+---------------+------------------
 Bauer              |         85003 |              NaN
 Reyes              |         91051 | 4276.58181261624
 Overstreet         |         53296 | 20278.6923394976
 Gauthier           |         97216 | 19543.7184537642
 Jones              |         82320 | 16928.0764028285
 Fortin             |         56166 | 18400.2738421652
 Carcetti           |         71135 | 16968.9453554483
 Weaver             |         74419 | 15729.0709901852
 Stein              |         85689 | 15040.5909495309
 McNulty            |         69423 | 14401.1524291943
 Webber             |         99091 | 15256.3160166536
 Meyer              |         74774 | 14588.6126417355
 Garnett            |         82169 | 14008.7223268494
 Roy                |         76974 | 13466.1270356647
 Dobisz             |         83486 | 13040.4887828347
 Martin             |         99702 | 13637.6804131055
 Martin             |         73589 | 13299.2838158566
 ...