When one value depends on another in a straight line, such as flight time on distance, a linear regression finds that line: y = slope x + intercept. Oracle's REGR_ aggregate functions fit it by least squares, directly in SQL, and also report how well it fits. No export to a statistics tool is needed.
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:
regr_slope(y, x) regr_intercept(y, x) regr_r2(y, x) regr_count(y, x) regr_avgx(y, x) regr_avgy(y, x) regr_sxx(y, x) regr_syy(y, x) regr_sxy(y, x)
Note the order: the dependent value y comes first, then x.
| Function | Returns |
|---|---|
| REGR_SLOPE, REGR_INTERCEPT | The fitted line |
| REGR_R2 | How well the line fits: 1 is perfect, 0 no better than the mean |
| REGR_COUNT | The number of pairs where neither value is NULL |
| REGR_AVGX, REGR_AVGY | The averages of x and y over those pairs |
| REGR_SXX, REGR_SYY, REGR_SXY | The sums of squares the others are computed from |
Fit Block Time to Distance
Example:
-- block time as a straight line of distance: minutes = slope * km + intercept
select round(regr_slope(block_minutes, distance_km), 5) as slope,
round(regr_intercept(block_minutes, distance_km), 1) as intercept,
round(regr_r2(block_minutes, distance_km), 4) as r_squared,
regr_count(block_minutes, distance_km) as n,
round(regr_avgx(block_minutes, distance_km)) as avg_km,
round(regr_avgy(block_minutes, distance_km)) as avg_minutes
from routes;Output:
SLOPE INTERCEPT R_SQUARED N AVG_KM AVG_MINUTES __________ ____________ ____________ _____ _________ ______________ 0.06645 41.6 0.9993 50 6655 484
A Nimbus Air flight takes about 41.6 minutes plus 0.06645 minutes per kilometre, roughly 4 minutes per 60 km. An R-squared of 0.9993 means the line explains almost all the variation in block times across the 50 routes.
Predict a Value
Plug a new x into the line to predict y, here the block time of a 10,000 km route.
Example:
-- predicted block time for a 10,000 km route from the fitted line
select round(regr_slope(block_minutes, distance_km) * 10000
+ regr_intercept(block_minutes, distance_km)) as predicted_minutes_10000km,
round(regr_sxx(block_minutes, distance_km)) as sxx,
round(regr_sxy(block_minutes, distance_km)) as sxy
from routes;Output:
PREDICTED_MINUTES_10000KM SXX SXY
____________________________ ____________ ___________
706 744163434 49448553The prediction is 706 minutes, about 11 hours 46 minutes. The sums of squares show how the slope is formed: REGR_SXY / REGR_SXX.
Things to Know
- All REGR_ functions use only the pairs where both values are not NULL, so REGR_COUNT may be smaller than COUNT(*).
- Use GROUP BY to fit a separate line per group, such as per aircraft type.
- A high R-squared shows a good straight-line fit, not that x causes y.
Related Guides
Conclusion
The REGR_ functions fit a least-squares line y = slope x + intercept in SQL, with REGR_R2 for the quality of the fit and REGR_COUNT for the number of pairs used. Remember that y comes first, and use the slope and intercept to predict new values.
See my articles more often when you search Google.
