Skip to main content

Multinomial classification

Predict one of three classes and inspect a confusion table instead of a single accuracy number.

The practical problem and model​

A quality-control system may need to route an item to one of several categories using numeric measurements. This tutorial teaches that pattern with flower measurements and three species labels.

Multinomial classification extends a binary classification problem to multiple mutually exclusive classes. The trained function returns the chosen class ID. Here 0, 1, and 2 mean setosa, versicolor, and virginica; the numbers are category identifiers, not an ordering of quality.

The SQL groups actual and predicted labels to form a compact confusion table. This makes it clear which classes are being confused, something a single overall score can hide.

Before you run​

Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.

Studio: Tutorial: Multinomial Classification. Download the complete SQL.

Step 1: Create fifteen labelled measurements​

There are five rows for each species. The four numeric measurements are the features.

CREATE CATALOG IF NOT EXISTS tutorial;

CREATE SCHEMA IF NOT EXISTS tutorial.flowers;

CREATE OR REPLACE TABLE tutorial.flowers.iris (
    sepal_len DOUBLE,
    sepal_wid DOUBLE,
    petal_len DOUBLE,
    petal_wid DOUBLE,
    species   INT   -- 0=setosa, 1=versicolor, 2=virginica
);

INSERT INTO tutorial.flowers.iris VALUES
    (5.1, 3.5, 1.4, 0.2, 0), (4.9, 3.0, 1.4, 0.2, 0), (4.7, 3.2, 1.3, 0.2, 0),
    (5.0, 3.6, 1.4, 0.2, 0), (5.4, 3.9, 1.7, 0.4, 0),
    (7.0, 3.2, 4.7, 1.4, 1), (6.4, 3.2, 4.5, 1.5, 1), (6.9, 3.1, 4.9, 1.5, 1),
    (5.5, 2.3, 4.0, 1.3, 1), (6.5, 2.8, 4.6, 1.5, 1),
    (6.3, 3.3, 6.0, 2.5, 2), (5.8, 2.7, 5.1, 1.9, 2), (7.1, 3.0, 5.9, 2.1, 2),
    (6.3, 2.9, 5.6, 1.8, 2), (6.5, 3.0, 5.8, 2.2, 2);

Step 2: Train a three-class model​

k = 3 declares the number of classes. The label is first in the training SELECT and is explicitly cast to DOUBLE.

CREATE OR REPLACE MODEL tutorial.flowers.species_clf
    (DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'multinomial'
OPTIONS (learning_rate = 0.1, k = 3, max_iters = 2000, tolerance = 1e-5)
AS SELECT
    CAST(species AS DOUBLE) AS label,
    sepal_len, sepal_wid, petal_len, petal_wid
FROM tutorial.flowers.iris;

Step 3: Inspect individual and grouped predictions​

The model output is a class ID even though the SQL signature permits the numeric result as DOUBLE. The confusion query casts it to INT for display.

SELECT tutorial.flowers.species_clf(5.0, 3.5, 1.5, 0.2) AS predicted_species;

SELECT
    species AS actual,
    CAST(tutorial.flowers.species_clf(sepal_len, sepal_wid, petal_len, petal_wid) AS INT) AS predicted,
    COUNT(*) AS rows
FROM tutorial.flowers.iris
GROUP BY actual, predicted
ORDER BY actual, predicted;

What to check in the results​

The example flower is classified as 0. The recorded confusion table contains three rows:

ActualPredictedRows
005
115
225

The counts sum to all 15 input rows. This is a training-fixture check, not evidence of perfect classification on new flowers.

Adapt it to real data​

Keep the class-ID mapping alongside the deployed model. Evaluate each class on separate labelled examples, especially rare categories. Define what happens when the real world produces a class that was absent from training, rather than silently assigning it a familiar meaning.

For an operational routing system, compare the cost of each kind of confusion and provide a review path for uncertain cases.

Supported models

All tutorials · Setup and troubleshooting

Last updated on