AntDB-Oracle Compatibility Developer’s Manual P3–63

VARIANCE

Calculates the variance of the specified column.

Example:

Calculates the variance of sa1.

SELECT empno, sal, VARIANCE(sal) OVER (ORDER BY hiredate) "Variance"
FROM emp ORDER BY ename, sal, "Variance";
 EMPNO |  SAL  |       Variance       
-------+-------+------------------------
  7876 |  8100 |              2999125
  7499 |  7600 |              2080000
  7698 | 11850 | 5697604.166666666667
  7782 |  7450 |  4749315.47619047619
  7902 |  8000 | 3630961.174242424242
  7900 |  5950 | 3630961.174242424242
  7566 |  7975 |              1564250
  7839 | 10000 | 4143673.611111111111
  7654 |  6250 |              3826250
  7934 |  6300 | 3430480.769230769231
  7788 |  8000 | 3195539.148351648352
  7469 |  4800 |              2000000
  7369 |  6800 |              2000000
  7844 |  6500 | 4204006.696428571429
  7521 |  6250 | 1392291.666666666667
(15 rows)

CORR

Calculate the correlation coefficient of a pair of expressions.

Example:

postgres=# select corr(t.num,test.num) from 
postgres-# (select id-1 as id, num from test) as t,test where t.id=test.id ;
 CORR(T.NUM,TEST.NUM)  
------------------------------
   -0.7802897838627655
(1 row)