Correlation Analysis in SQL

Correlation Analysis in SQL

In this lesson, you will learn how to quantify the strength and direction of a linear relationship between two numerical variables using the Pearson correlation coefficient directly within your SQL query pipelines.

The Pearson Correlation Formula

The Pearson correlation coefficient ($r$) measures how much two variables move together, ranging from -1 (perfect negative correlation) to +1 (perfect positive correlation). To calculate this in SQL, we rely on 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]}}$

Implementing in SQL

Since many SQL dialects do not have a native CORR() function, we implement this by creating an Aggregation Layer. We compute the necessary sums—$\sum x$, $\sum y$, $\sum xy$, $\sum x^2$, and $\sum y^2$—in a single pass, then combine them in a final calculation step.

Using a Common Table Expression (CTE) keeps our logic modular:

WITH Stats AS (
    SELECT 
        COUNT(*) as n,
        SUM(x) as sum_x,
        SUM(y) as sum_y,
        SUM(x * y) as sum_xy,
        SUM(x * x) as sum_x2,
        SUM(y * y) as sum_y2
    FROM analytical_dataset
)
SELECT 
    (n * sum_xy - sum_x * sum_y) / 
    SQRT((n * sum_x2 - POWER(sum_x, 2)) * (n * sum_y2 - POWER(sum_y, 2))) 
    AS pearson_correlation
FROM Stats;

Why This Matters for Analytics

In an analytical pipeline, correlation is often the first step in feature selection. By calculating this directly in your database, you avoid the latency of moving large datasets into external languages like Python or R. This approach is highly efficient because it leverages the database’s ability to perform mass aggregations over your cluster keys, effectively reducing the data to a single row of summary statistics before computing the final coefficient. Always ensure your data is cleaned of NULL values using filters before aggregation, as a single NULL can nullify the entire result set.

Key Takeaway: You can perform robust statistical analysis in SQL by aggregating raw variables into the components of the Pearson formula within a CTE, enabling high-performance correlation analysis at scale.