Oracle analytic function-covar_pop,covar_samp

covar_samp

COVAR_SAMP计算一对表达式的样本协方差,可以用于聚合或分析函数。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / (n-1) –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

covar_pop

Covar_pop返回一对表达式对总体协方差。可以当作聚合函数和分析函数使用。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / n –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

SQL> select department_id,
  2         covar_pop(sysdate - hire_date, salary) "covar_pop",
  3         covar_samp(sysdate - hire_date, salary) "covar_samp"
  4    from employees
  5   where department_id <= 40
  6   group by department_id;

DEPARTMENT_ID  covar_pop covar_samp
------------- ---------- ----------
           10          0
           20     957250    1914500
           30 1258261.11 1509913.33
           40          0

分析功能

SQL> SELECT product_id,
  2         supplier_id,
  3         COVAR_POP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_POP,
  4         COVAR_SAMP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_SAMP
  5    FROM product_information p
  6   WHERE category_id = 29
  7   ORDER BY product_id, supplier_id;

PRODUCT_ID SUPPLIER_ID  COVAR_POP COVAR_SAMP
---------- ----------- ---------- ----------
      1774      103088          0
      1775      103087    1473.25     2946.5
      1794      103096 1702.77778 2554.16667
      1825      103093    1926.25 2568.33333
      2004      103086     1591.4    1989.25
      2005      103086     1512.5       1815
      2416      103088 1475.97959 1721.97619
      2417      103088 1478.70313 1689.94643
      2449      103088  1326.8642 1492.72222
      3101      103086     1195.2       1328
      3170      103089 1590.07438 1749.08182
      3171      103089    1718.25 1874.45455
      3172      103094 1710.42012 1852.95513
      3173      103094 1588.56122 1710.75824
      3175      103089     1593.4 1707.21429
此条目发表在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