STDDEV_SAMP [Analytic]

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

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

When VAR_SAMP returns NULL, STDDEV_SAMP returns NULL.

STDDEV_SAMP() is semantically identical to the nonstandard function, STDDEV().

Behavior Type

Immutable

Syntax

STDDEV_SAMP ( 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

Examples

The following example returns the sample 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_samp"
      FROM employee_dimension WHERE job_title =  'Assistant Director'; 
 employee_last_name | annual_salary |   stddev_samp
--------------------+---------------+------------------
 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
 ...

See Also