STDDEV [analytic]

计算当前行相对于 窗口内组的统计样本标准偏差。 STDDEV_SAMP 返回值与为 VAR_SAMP 函数定义的方差的平方根相同:

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

VAR_SAMP 返回 NULL 时,此函数将返回 NULL

行为类型

不可变

语法

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

参数

表达式
任何 NUMERIC 数据类型或可隐式转换为数字数据类型的任何非数字数据类型。函数返回的数据类型与参数的数字数据类型相同。
OVER()
请参阅分析函数

示例

以下示例返回雇员维度表中副经理的薪资标准差:

=> 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
 ...

另请参阅