Not all wrong predictions cost the same. Treating a business traveller as economy may lose a valuable customer, while the opposite mistake only wastes an upgrade offer. A cost matrix tells an Oracle Machine Learning classification model what each kind of mistake costs, and predictions with COST MODEL then choose the class with the lowest expected cost instead of the highest probability.
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
-- a table with ACTUAL_TARGET_VALUE, PREDICTED_TARGET_VALUE, COST dbms_data_mining.add_cost_matrix(model_name, cost_matrix_table); prediction(model cost model using *) prediction_cost(model, class cost model using *) dbms_data_mining.remove_cost_matrix(model_name);
The example uses the ML_TICKETS view and the CABIN_CLASS model from How to Explain Predictions with PREDICTION_DETAILS in Oracle.
Make Missing a Business Traveller Expensive
Example:
-- predicting ECONOMY for a business traveller costs 5 times more than the opposite error
create table cabin_costs (actual_target_value varchar2(10),
predicted_target_value varchar2(10),
cost number);
insert into cabin_costs values ('BUSINESS', 'BUSINESS', 0), ('BUSINESS', 'ECONOMY', 5),
('ECONOMY', 'ECONOMY', 0), ('ECONOMY', 'BUSINESS', 1);
commit;
exec dbms_data_mining.add_cost_matrix('CABIN_CLASS', 'CABIN_COSTS')
select prediction(cabin_class cost model using *) as predicted, count(*) as tickets,
round(avg(prediction_cost(cabin_class, 'BUSINESS' cost model using *)), 3)
as avg_cost_if_business
from ml_tickets
group by prediction(cabin_class cost model using *);
select * from table(dbms_data_mining.get_model_cost_matrix('CABIN_CLASS')) order by 1, 2;
exec dbms_data_mining.remove_cost_matrix('CABIN_CLASS')Output:
Table CABIN_COSTS created. 4 rows inserted. Commit complete. PL/SQL procedure successfully completed. PREDICTED TICKETS AVG_COST_IF_BUSINESS ____________ __________ _______________________ ECONOMY 525 0.99 BUSINESS 544 0.504 ACTUAL PREDICTED COST ___________ ____________ _______ BUSINESS BUSINESS 0 BUSINESS ECONOMY 5 ECONOMY BUSINESS 1 ECONOMY ECONOMY 0 PL/SQL procedure successfully completed.
- Predicting economy for a business traveller costs 5, the opposite mistake 1, and correct predictions 0.
- With COST MODEL, 544 of the 1,069 tickets are predicted business: whenever business is reasonably likely, predicting it is cheaper than risking the five-times-more-expensive mistake.
- PREDICTION_COST gives the expected cost of predicting business for each ticket, averaged here per predicted class.
- GET_MODEL_COST_MATRIX shows the matrix stored with the model, and REMOVE_COST_MATRIX takes it off again.
The example drops the cost table afterward.
Things to Know
- A cost matrix changes scoring, not the trained model, so it can be adjusted without retraining.
- Choose costs from real business values, such as revenue lost or offer price, so the expected cost means something.
- Without COST MODEL in the query, predictions still use probabilities.
Related Guides
- How to Get All Class Probabilities with PREDICTION_SET
- How to Explain Predictions with PREDICTION_DETAILS in Oracle
Conclusion
A cost matrix makes Oracle Machine Learning predictions minimize the cost of mistakes rather than their number. Add it with ADD_COST_MATRIX, score with COST MODEL, and read expected costs with PREDICTION_COST.
