A regression model predicts a number, such as a fare. A single number hides how sure the model is. For generalized linear models, PREDICTION_BOUNDS returns a lower and upper bound around each prediction, a range that contains the true value with a given confidence, 95 percent by default.
Code for This Guide
The main examples are in the examples/machine-learning 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
prediction_bounds(model [, confidence_level [, class_value]] using ...).lower prediction_bounds(model [, confidence_level [, class_value]] using ...).upper
PREDICTION_BOUNDS works with models built with ALGO_GENERALIZED_LINEAR_MODEL.
Fares by Distance
FARE_GLM learns economy fares as a straight line of distance. The query predicts fares for three distances given in a VALUES clause, with their bounds.
Example:
-- economy fares as a straight line of distance, with 95% bounds
declare
v_settings dbms_data_mining.setting_list;
begin
v_settings('ALGO_NAME') := 'ALGO_GENERALIZED_LINEAR_MODEL';
dbms_data_mining.create_model2('FARE_GLM', 'REGRESSION',
'select t.ticket_id, t.fare, r.distance_km
from tickets t join flights f using (flight_id) join routes r using (route_id)
where t.cabin = ''ECONOMY''',
v_settings, 'TICKET_ID', 'FARE');
end;
/
select distance_km,
round(prediction(fare_glm using distance_km), 2) as predicted_fare,
round(prediction_bounds(fare_glm using distance_km).lower, 2) as lower_95,
round(prediction_bounds(fare_glm using distance_km).upper, 2) as upper_95
from (values (1137), (5497), (11641)) t (distance_km);Output:
PL/SQL procedure successfully completed.
DISTANCE_KM PREDICTED_FARE LOWER_95 UPPER_95
______________ _________________ ___________ ___________
1137 143.69 131.69 155.69
5497 540.79 533.41 548.17
11641 1100.37 1088.97 1111.78A 1,137 km flight is predicted at 143.69, between 131.69 and 155.69. For 11,641 km the range is 1,088.97 to 1,111.78. USING passes only the column the model needs, so the input can be any row with a DISTANCE_KM value.
Things to Know
- Pass a confidence level such as 0.99 as the second argument for a wider, surer range.
- For a classification GLM, the bounds apply to the probability of a class.
- Drop the model with DBMS_DATA_MINING.DROP_MODEL('FARE_GLM') when done.
Related Guides
Conclusion
PREDICTION_BOUNDS adds a confidence range to the predictions of generalized linear models. Report the range with the prediction, so users see how precise each estimate is.
