SQL Reference - COVAR

COVAR_SAMP

COVAR_SAMP is a function in SQL that calculates the sample covariance of a set of numeric pairs. This statistical measurement represents the degree to which two variables change together in a dataset. COVAR_SAMP calculates the covariance for each pair of rows, excluding pairs with null values. It's commonly used in analytics scenarios for data analysis.

COVAR_SAMP(expression1, expression2)

expression1: The first set of numbers in a pair for which you want to calculate the covariance. It should be a number, and all non-null items in the list are included in the calculation.
expression2: The corresponding second set of numbers in a pair for which you want to calculate the covariance. Like the first expression, it should be a number and all non-null items in the list are included in the calculation.

Example

SELECT COVAR_SAMP(salary, commission_pct)
AS Sample_Covariance
FROM employees;

Output

SAMPLE_COVARIANCE
-----------------
12245.8387552346

Explanation

This function computes the sample covariance of a pair of number sets. Here, salary and commission_pct from the employees table are used. The result, 12245.8387552346, represents their sample covariance.

COVAR_SAMP(Y, X)

y: This is the first expression that is a number, and it represents the dependent variable in the covariance calculation.
x: This is the second expression that is a number, and it represents the independent variable in the covariance calculation.

Example

SELECT COVAR_SAMP(column1, column2)
FROM table_name;

Output

          covar_samp
------------------------------
 13487.1442948399004

Explanation

In the given example, the COVAR_SAMP() function returns the sample covariance of a set of number pairs. Covariance is a measure that illustrates how two variables are related to each other. If the above variance was derived from column1 and column2 of a table, it indicates that these two columns have a covariance of approximately 13487.14. This can suggest a positive linear relation, as covariance > 0 typically signifies that larger values of one variable correspond with larger values of the other.