Oracle COVAR_POP Function

Covariance measures whether two values tend to rise and fall together: positive when they move in the same direction, negative when in opposite directions. Oracle computes it with COVAR_POP for a whole population and COVAR_SAMP for a sample. It underlies the correlation coefficient and linear regression.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

covar_pop(expr1, expr2)
covar_samp(expr1, expr2)

COVAR_POP is the sum of the products of the deviations from the two means, divided by n; COVAR_SAMP divides by n - 1. Pairs with a NULL in either value are ignored.

Distance and Block Time

Example:

select round(covar_pop(distance_km, block_minutes))  as covar_pop,
       round(covar_samp(distance_km, block_minutes)) as covar_samp,
       round(corr(distance_km, block_minutes), 4)    as corr,
       round(corr_s(distance_km, block_minutes), 4)  as spearman,
       round(corr_k(distance_km, block_minutes), 4)  as kendall
from   routes;

Output:

   COVAR_POP    COVAR_SAMP      CORR    SPEARMAN    KENDALL
____________ _____________ _________ ___________ __________
      988971       1009154    0.9997           1          1

The covariance of distance and block time is positive, so longer routes take longer, but its size, nearly a million, says little on its own: it is in kilometre-minutes.

Covariance Depends on Units

Expressing distance in thousands of kilometres and time in hours changes the covariance completely, while the correlation stays the same.

Example:

-- covariance depends on the units; correlation does not
select round(covar_pop(distance_km, block_minutes))                 as covar_km_minutes,
       round(covar_pop(distance_km / 1000, block_minutes / 60), 2)  as covar_1000km_hours,
       round(corr(distance_km, block_minutes), 4)                   as corr_km_minutes,
       round(corr(distance_km / 1000, block_minutes / 60), 4)       as corr_1000km_hours
from   routes;

Output:

   COVAR_KM_MINUTES    COVAR_1000KM_HOURS    CORR_KM_MINUTES    CORR_1000KM_HOURS
___________________ _____________________ __________________ ____________________
             988971                 16.48             0.9997               0.9997

The covariance drops from 988,971 to 16.48, but the correlation is 0.9997 both times. That is why correlation, the covariance divided by both standard deviations, is the usual way to report how strongly two values are related.

Things to Know

• CORR(x, y) equals COVAR_POP(x, y) / (STDDEV_POP(x) * STDDEV_POP(y)).
• REGR_SLOPE(y, x) equals COVAR_POP(x, y) / VAR_POP(x).
• Use COVAR_SAMP for a sample and COVAR_POP for complete data.

Related Guides

• Oracle CORR Function
• Oracle VARIANCE Function

Conclusion

COVAR_POP and COVAR_SAMP measure whether two columns move together. The sign tells the direction, but the size depends on units, so report correlation for strength and use covariance as a building block for regression and other statistics.