Oracle CORR Function

Do longer routes take proportionally longer to fly? Do higher fares go with longer distances? A correlation coefficient answers such questions with one number from -1 to 1. CORR returns the Pearson correlation of two columns, and CORR_S and CORR_K the rank correlations of Spearman and Kendall, all as aggregate functions in SQL.

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:

corr(expr1, expr2)
corr_s(expr1, expr2 [, {coefficient | one_sided_sig | two_sided_sig}])
corr_k(expr1, expr2 [, {coefficient | one_sided_sig | two_sided_sig}])

1 means a perfect positive relationship, -1 a perfect negative one, and 0 none. Rows where either value is NULL are ignored. CORR_S and CORR_K can return a significance instead of the coefficient.

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

Distance and scheduled block time are almost perfectly correlated, 0.9997, and their ranks agree perfectly, 1. The first two columns are covariances, covered separately.

Distance and Fare

With FILTER, one query computes the correlation overall and per cabin.

Example:

-- do longer routes have higher average fares?
select round(corr(r.distance_km, t.fare), 3) as corr_all,
       round(corr(r.distance_km, t.fare) filter (where t.cabin = 'ECONOMY'), 3)  as corr_economy,
       round(corr(r.distance_km, t.fare) filter (where t.cabin = 'BUSINESS'), 3) as corr_business
from   tickets t join flights f on f.flight_id = t.flight_id join routes r on r.route_id = f.route_id;

Output:

   CORR_ALL    CORR_ECONOMY    CORR_BUSINESS
___________ _______________ ________________
      0.542           0.963            0.963

Within each cabin, fares rise closely with distance (0.963). Across both cabins together, the correlation drops to 0.542, because the cabin, not the distance, explains most of the difference between a business and an economy fare. Mixing groups can hide a strong relationship.

Things to Know

• Correlation measures association, not cause.
• CORR only detects straight-line relationships; CORR_S detects any steadily rising or falling one.
• All three work as analytic functions with OVER.

Related Guides

• How to Filter Aggregates with the FILTER Clause in Oracle

Conclusion

CORR returns the Pearson correlation of two columns, CORR_S and CORR_K the Spearman and Kendall rank correlations. Compute them per group, with GROUP BY or FILTER, when the data mixes populations that behave differently.