How to Get All Class Probabilities with PREDICTION_SET

PREDICTION returns only the most likely class. A decision often needs more: how likely each class is, to set a threshold or rank alternatives. PREDICTION_SET returns every class with its probability for a row, as a collection that TABLE turns into rows.

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

select s.prediction, s.probability
from   source_table t,
       table(prediction_set(model [, bestn [, cutoff]] using *)) s;

BESTN limits the result to the most likely classes, and CUTOFF to classes with at least that probability.

The examples use the ML_TICKETS view and the CABIN_CLASS model from How to Explain Predictions with PREDICTION_DETAILS in Oracle.

Every Class for One Ticket

Example:

select t.ticket_id, s.prediction, round(s.probability, 3) as probability
from   ml_tickets t,
       table(prediction_set(cabin_class using *)) s
where  t.ticket_id = 850;

Output:

   TICKET_ID PREDICTION       PROBABILITY
____________ _____________ ______________
         850 ECONOMY                0.769
         850 BUSINESS               0.231

Ticket 850 is economy with probability 0.769 and business with 0.231, which add up to 1.

Probabilities Side by Side

Example:

-- the probability of each cabin for several tickets, side by side
select ticket_id, actual,
       round(max(case s.prediction when 'ECONOMY'  then s.probability end), 3) as p_economy,
       round(max(case s.prediction when 'BUSINESS' then s.probability end), 3) as p_business
from   (select ticket_id, cabin as actual, prediction_set(cabin_class using *) as ps
        from   ml_tickets where ticket_id in (10, 540, 765, 1005)) t,
       table(t.ps) s
group  by ticket_id, actual
order  by ticket_id;

Output:

   TICKET_ID ACTUAL         P_ECONOMY    P_BUSINESS
____________ ___________ ____________ _____________
          10 ECONOMY                1             0
         540 BUSINESS           0.723         0.277
         765 BUSINESS            0.17          0.83
        1005 ECONOMY             0.17          0.83

Conditional aggregation turns the set into one column per class. Ticket 540 shows why the prediction failed: a business ticket that the model gave only 0.277 for business. The example builds its own copy of the view and model and drops them afterward.

Things to Know

• Order the set by probability to rank alternatives, for example the second most likely class.
• A business rule can act on the probability of a class, such as flagging tickets with more than 0.5 for business, instead of on the single prediction.
• PREDICTION_SET also accepts COST MODEL when the model has a cost matrix.

Related Guides

• How to Explain Predictions with PREDICTION_DETAILS in Oracle
• How to Use LATERAL Inline Views with CROSS APPLY in Oracle

Conclusion

PREDICTION_SET returns each class with its probability for a row. Turn it into rows with TABLE, or into columns with conditional aggregation, and base decisions on probabilities rather than on the single most likely class.