COVAR_POP & COVAR_SAMP Analytic Functions in Oracle SQL

COVARIANCE is a measure of the relationship between two random variables. Measure of the variance between two variables, the variance of one variable equals the variance of the other variable.

The variance can be positive or negative values as follows:
Positive covariance: Indicates that two variables tend to move in the same direction.
Negative covariance: Reveals that two variables tend to move in inverse directions.

Difference:
COVARIANCE measures the total variation of two random variables from their expected values.
CORRELATION measures the strength of the relationship between variables.

COVAR_POP returns the population covariance of a set of data.

For population covariance formula:

COVAR_POP(expr1,expr2) = (SUM(expr1 * expr2) – SUM(expr2) * SUM(expr1) / n) / n

COVAR_SAMP returns the sample covariance of a set of data.

For sample covariance formula:

COVAR_SAMP(expr1,expr2) = (SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / (n-1)

Xi – the values of the X-variable
Yj – the values of the Y-variable
X̄ – the mean (average) of the X-variable
Ȳ – the mean (average) of the Y-variable
n – the number of the data points

Example of COVARIANCE
Employee table check the covariance with hire_data and salary going in same direction with respect to Job_id.

SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id, covar_pop, covar_samp;

  
JOB_ID      COVAR_POP COVAR_SAMP
---------- ---------- ----------
SA_MAN         660700     825875
SA_REP     579988.466  600702.34
SH_CLERK     212432.5 223613.158
ST_CLERK    176577.25 185870.789
ST_MAN         436092     545115

Take example of share market
Suppose two companies A and B having stock history in previous 4 year.
User want to check the both are going in same direction otherwise he invest in other company. Find COVARIANCE from last 4 year data.

Company stocks detail of A and B companies:

select * from stocks;

COMPANY         VALUE       YEAR
---------- ---------- ----------
A                1500       2015
A                1700       2016
A                1600       2017
A                2000       2018
B                 700       2015
B                1000       2016
B                1100       2017
B                1200       2018

Calculate covariance with self join and COVAR_SAMP function with over().
It return positive value, its mean both company going in same direction.

select a.company,b.company, COVAR_SAMP(a.value,b.value) over() "Sample covariance",COVAR_POP(a.value,b.value) over() "Population covariance" from stocks a , stocks b
where a.year=b.year and a.company='A' and b.company='B';

COMPANY    COMPANY    Sample covariance Population covariance
---------- ---------- ----------------- ---------------------
A          B                 36666.6667                 27500
A          B                 36666.6667                 27500
A          B                 36666.6667                 27500
A          B                 36666.6667                 27500

Example show manually calculate by formula provided above:

1. Find Mean value of both companies A and B.

Year        A        B        A*B
------ ------ -------- ----------
2015     -200     -300      60000
2016        0        0          0
2017     -100      100     -10000
2018      300      200      60000

Note: Its exact match with Oracle COVAR_SAMP & COVAR_POP function.