Calculating Pearson Correlation

In this lesson, you will learn how to quantify the strength and direction of a linear relationship between two numerical columns using SQL aggregation functions.

The Concept of Pearson Correlation

The Pearson Correlation Coefficient ($r$) measures how closely two variables move together. It results in a value between -1 (a perfect inverse relationship) and +1 (a perfect direct relationship), with 0 indicating no linear relationship.

In SQL, because standard libraries don’t always provide a built-in CORR() function, we calculate this using the standard statistical formula:

$r = \frac{n(\sum xy) - (\sum x)(\sum y)}{\sqrt{[n\sum x^2 - (\sum x)^2][n\sum y^2 - (\sum y)^2]}}$

Translating to SQL Aggregates

To calculate this, we need the sum of $x$, the sum of $y$, the sum of the product ($x \times y$), and the sum of the squares ($x^2$ and $y^2$). We can compute these efficiently in a single pass over the data using standard SUM() aggregates.

Consider a table sales_data where we want to correlate ad_spend ($x$) and revenue ($y$):

SELECT 
    (COUNT(*) * SUM(x * y) - SUM(x) * SUM(y)) / 
    SQRT(
        (COUNT(*) * SUM(x * x) - POWER(SUM(x), 2)) * 
        (COUNT(*) * SUM(y * y) - POWER(SUM(y), 2))
    ) AS pearson_r
FROM sales_data;

Why This Matters in Aggregation

Because this calculation relies entirely on SUM() and COUNT(), it is highly performant. These are "distributive" aggregate functions, meaning they play perfectly with the Parallel Processing of Aggregates and Partition-Aware Aggregation techniques you learned previously. You can aggregate these partial sums across different data nodes or partitions before performing the final calculation, keeping your analytical queries fast even on massive datasets.

By calculating these components during your standard ETL or aggregation step, you avoid the overhead of moving raw data out of the database for statistical processing, keeping your data analysis local to the engine.

Key Takeaway: Pearson Correlation can be computed in SQL by aggregating the sums of squares and cross-products, allowing you to measure linear dependencies efficiently using standard distributive aggregate functions.