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.
A = (1500+1700+1600+2000)/4 = 1700
B = (700+1000+1100+1200)/4 = 1000
2. Difference between each value and mean price as shown in below table and multiply that difference A*B
Year A B A*B ------ ------ -------- ---------- 2015 -200 -300 60000 2016 0 0 0 2017 -100 100 -10000 2018 300 200 60000
3. Sum of multiple column(A*B).
Sum (A*B column) = 110000
4. Find sample co-variance.
*n = 4 no of rows.
Sample Coverence = 110000 /(4-1) = 36666.6667
Population Coverence = 110000/4 = 27500
Note: Its exact match with Oracle COVAR_SAMP & COVAR_POP function.
