Prediction intervals with quantile models

Quantile regression gives a direct path to prediction intervals inside DuckDB: train several pinball models, check coverage, and optionally apply split-conformal calibration. Export with keep_columns so scored rows keep their identity keys.

LOAD duckboost;

CREATE TABLE series AS
SELECT
    i::DOUBLE AS x,
    (0.5 * i + 3.0 * sin(i / 5.0) + (i % 7) * 0.2)::DOUBLE AS y,
    i AS id
FROM range(1, 101) t(i);

CREATE TABLE quantile_models AS
SELECT
    duckboost_train(y, [x], MAP {
        'objective': 'quantile', 'quantile_alpha': '0.05',
        'n_estimators': '30', 'max_depth': '3', 'feature_names': 'x'
    }) AS q05,
    duckboost_train(y, [x], MAP {
        'objective': 'quantile', 'quantile_alpha': '0.50',
        'n_estimators': '30', 'max_depth': '3', 'feature_names': 'x'
    }) AS q50,
    duckboost_train(y, [x], MAP {
        'objective': 'quantile', 'quantile_alpha': '0.95',
        'n_estimators': '30', 'max_depth': '3', 'feature_names': 'x'
    }) AS q95
FROM series;

Empirical coverage and mean interval width on the training rows:

SELECT
    round(avg((y >= lo AND y <= hi)::INTEGER), 2) AS coverage,
    round(avg(hi - lo), 2) AS mean_width,
    round(avg((lo <= mid AND mid <= hi)::INTEGER), 2) AS ordered_bands
FROM (
    SELECT
        s.y,
        duckboost_predict(m.q05, [s.x]) AS lo,
        duckboost_predict(m.q50, [s.x]) AS mid,
        duckboost_predict(m.q95, [s.x]) AS hi
    FROM series s, quantile_models m
);

Pinball loss via duckboost_evaluate follows the stored objective_alpha when metric is auto:

SELECT
    duckboost_evaluate_agg(model, y, [x]) AS auto_pinball,
    duckboost_evaluate_agg(model, y, [x], MAP {'metric': 'pinball'}) AS pinball
FROM q90, series;

When independently trained quantiles cross, enforce order with least / greatest before reporting the interval:

SELECT
    id,
    least(lo, hi) AS lower,
    greatest(lo, hi) AS upper
FROM (
    SELECT
        s.id,
        duckboost_predict(m.q05, [s.x]) AS lo,
        duckboost_predict(m.q95, [s.x]) AS hi
    FROM series s, quantile_models m
);

Split-conformal calibration

Hold out a calibration fold, compute absolute residual scores against the quantile band, and take the conformal quantile of those scores. Adding that width to every prediction interval restores finite-sample coverage under exchangeability, at the cost of wider intervals.

-- Example shape (fill in with your calibration split):
-- score_i = greatest(q_lo(x_i) - y_i, y_i - q_hi(x_i))
-- q_hat = quantile_cont(score, 1 - alpha) on the calibration fold
-- interval = [q_lo - q_hat, q_hi + q_hat]

Deploy with identity columns

For scoring jobs, keep the row id beside the prediction so results join back without relying on row order:

SELECT duckboost_to_sql(
    model,
    'series',
    ['x'],
    MAP {
        'separate_trees': 'false',
        'prediction_alias': 'q50',
        'keep_columns': 'id'
    }
) AS export_sql
FROM q50;

Scale note

The native trainer buffers every training row and its bin ids in memory, then finds splits from histograms (including oblivious / symmetric growth). Training is single-threaded. duckboost_predict caches a parsed model within each execution chunk when the JSON repeats; for large scoring jobs prefer duckboost_to_sql (optionally with keep_columns / proba) and run the generated SQL without the extension.