Oracle analytic function-variance,var_samp,var_pop

variance

Variance计算expr的方差。
函数中当expr的个数为1的时候(即求方差的元素只有1个时候),variance返回为0.
当expr > 1的时候,variance等同于var_samp.

SQL> select count(*) from employees where department_id = 10;

  COUNT(*)
----------
         1

SQL> SELECT VARIANCE(salary) "Variance" from employees where department_id = 10;   --当元素个数为1的时候

  Variance
----------
         0

var_samp

Var_samp返回在数据集中丢弃了nulls之后的数据集合的样本方差。如果这个函数被用于一个空集合,那么函数将返回null。
函数的计算方式为:
(SUM(expr – (SUM(expr) / COUNT(expr)))2) / (COUNT(expr) – 1)

当输入的数据集的个数为1个元素的时候,variance函数返回为0,var_samp返回为null。当数据集个数大于1个元素的时候,var_samp和variance相同。

var_pop

Var_pop返回数据集中丢弃了nulls之后的总体方差,对于空集合,var_pop函数返回null。
函数的计算公式为
SUM((expr – (SUM(expr) / COUNT(expr)))2) / COUNT(expr)

函数对比

SQL> select department_id,
  2         last_name,
  3         salary,
  4         variance(salary) over(partition by department_id order by hire_date) "Variance",
  5         var_samp(salary) over(partition by department_id order by hire_date) "Var_samp",
  6         var_pop(salary) over(partition by department_id order by hire_date) "Var_pop"
  7    from employees
  8   where department_id <= 40;

DEPARTMENT_ID LAST_NAME                                              SALARY   Variance   Var_samp    Var_pop
------------- -------------------------------------------------- ---------- ---------- ---------- ----------
           10 Whalen                                                   4400          0                     0
           20 Hartstein                                               13000          0                     0
           20 Fay                                                      6000   24500000   24500000   12250000
           30 Raphaely                                                11000          0                     0
           30 Khoo                                                     2600   35280000   35280000   17640000
           30 Tobias                                                   2600   23520000   23520000   15680000
           30 Baida                                                    2900   17242500   17242500   12931875
           30 Himuro                                                   2600   13878000   13878000   11102400
           30 Colmenares                                               2500 11666666.7 11666666.7 9722222.22
           40 Mavris                                                   6500          0                     0
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s