Skip to main content

Random Forest Regressor

UDTF: cuml_random_forest_regressor

Official cuML reference: Python API

Fit a query-local cuML random forest regressor and score independent rows.

Quickstart​

Register the relations referenced by the parenthesized SELECT clauses below. For metadata validation, the descriptor names training_vectors and predict_vectors.

SELECT id, prediction
FROM cuml_random_forest_regressor(
training => (SELECT id, d0, label FROM train),
predict => (SELECT id, d0 FROM test)
)
ORDER BY row_ordinal;

Inputs​

Each relation argument is a parenthesized SELECT subquery. Metadata validation resolves a registered table or view for the same role without scanning its rows. See ML Inputs for the ID, feature element, Float64 narrowing, null, finite-value, and runtime-dimension contract.

RoleRequiredValidation referenceDescription
trainingyestableDense features and an Int32 label used to fit the query-local model.
predictyestableIndependent dense feature rows scored with the query-local model; must omit label.

Vector element types​

Feature values must be Float32 or Float64.

Arguments and options​

Scalar SQL arguments​

ArgumentTypeRequiredDefaultConstraintsDescription
bootstrapbooleannotrueSample training rows with replacement.
max_batch_sizeintegerno4096minimum 1; maximum 2147483647Maximum tree nodes processed together during fitting.
max_depthintegerno16minimum 0; maximum 2147483647Maximum tree depth; zero uses the native unlimited depth.
max_featuresnumberno1greater than 0; maximum 1Fraction of features sampled per split.
max_leavesintegernoNo defaultminimum 1; maximum 2147483647Maximum leaf count; omission uses the native unlimited setting.
max_n_binsintegerno128minimum 2; maximum 2147483647Maximum histogram bins.
max_samplesnumberno1greater than 0; maximum 1Fraction of training rows sampled per tree.
min_impurity_decreasenumberno0minimum 0Minimum split impurity reduction.
min_samples_leafintegerno1minimum 1; maximum 2147483647Minimum samples per leaf.
min_samples_splitintegerno2minimum 2; maximum 2147483647Minimum samples to split a node.
n_treesintegerno100minimum 1; maximum 2147483647Number of trees.
seedintegerno0minimum 0; maximum 18446744073709551615Training random seed.

Vector binding shapes​

Each relation subquery projects an id and either feature columns in dimension order or one list column, with the element types listed under Vector element types. The training relation also projects a finite Float32 label; the predict relation omits it. Nullable field types are accepted. A null ID, feature value, list row, list element, or label fails the query.

The list-column shape excludes other feature columns. See ML Inputs for the allowed list containers and runtime checks.

Output​

ColumnTypeNullableDescription
row_ordinalUInt64noEvaluated input row position, including when IDs repeat. Follows the input's top-level ORDER BY on output columns; unspecified without one.
idsame_as_predict.idnoLogical ID from the predict relation.
predictionFloat32noPredicted regression target.

The output schema depends on the call. Run gpu_validate_call against registered relations to inspect the schema that the validated ID types and literal options produce.

Examples​

How concentrated is next month's spending, and can a forest find the big spenders?​

The MerRec evaluation covers 2,017,739 shoppers. A forest ranks them using ten behavior features from the preceding 92 days. The captured top 1% holds 38.1% of the next month's listed-price GMV; ranking by past spend finds 35.2%.

The forest trains on shoppers active before August 1 and scores those active before September 1. SQL compares its ranking with past spend and recent intent actions, using the next 30 days of purchases for evaluation.

RankingTop 1% share of next-month GMVTop 5% share
Random forest forecast38.1%61.8%
Past 92-day GMV35.2%60.0%
Intent actions in the last 30 days15.6%not computed

The evaluation covers one later month, with $2,538,509 total GMV summing the listed prices of completed purchases.

Executed SQL
WITH train AS (
SELECT e.user_id AS id,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'buy_comp' THEN i.price ELSE 0 END) AS REAL) AS gmv,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-07-02 00:00:00' AND e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-07-02 00:00:00' AND e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-07-02 00:00:00' AND e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-07-02 00:00:00' AND e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys_30d,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' THEN i.price ELSE 0 END) / SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' THEN 1 ELSE 0 END) AS REAL) AS mean_price,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'buy_comp' THEN i.price ELSE 0 END) AS REAL) AS next_gmv
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_time >= TIMESTAMP '2023-05-01 00:00:00' AND e.event_time < TIMESTAMP '2023-08-31 00:00:00'
GROUP BY e.user_id
HAVING SUM(CASE WHEN e.event_time < TIMESTAMP '2023-08-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) >= 20
),
test AS (
SELECT e.user_id AS id,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'buy_comp' THEN i.price ELSE 0 END) AS REAL) AS gmv,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys_30d,
CAST(SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' THEN i.price ELSE 0 END) / SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' THEN 1 ELSE 0 END) AS REAL) AS mean_price,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'buy_comp' THEN i.price ELSE 0 END) AS REAL) AS next_gmv
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_time >= TIMESTAMP '2023-06-01 00:00:00' AND e.event_time < TIMESTAMP '2023-10-01 00:00:00'
GROUP BY e.user_id
HAVING SUM(CASE WHEN e.event_time < TIMESTAMP '2023-09-01 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) >= 20
),
pred AS (
SELECT id, prediction FROM cuml_random_forest_regressor(
training => (SELECT id, LN(1 + views) AS views, LN(1 + likes) AS likes, LN(1 + intents) AS intents, LN(1 + buys) AS buys, LN(1 + gmv) AS gmv, LN(1 + views_30d) AS views_30d, LN(1 + likes_30d) AS likes_30d, LN(1 + intents_30d) AS intents_30d, LN(1 + buys_30d) AS buys_30d, LN(1 + mean_price) AS mean_price, LN(1 + next_gmv) AS label FROM train ORDER BY id),
predict => (SELECT id, LN(1 + views) AS views, LN(1 + likes) AS likes, LN(1 + intents) AS intents, LN(1 + buys) AS buys, LN(1 + gmv) AS gmv, LN(1 + views_30d) AS views_30d, LN(1 + likes_30d) AS likes_30d, LN(1 + intents_30d) AS intents_30d, LN(1 + buys_30d) AS buys_30d, LN(1 + mean_price) AS mean_price FROM test ORDER BY id),
n_trees => 100, max_depth => 12, seed => 42)
), ranked AS (
SELECT t.next_gmv, COUNT(*) OVER () AS cohort,
ROW_NUMBER() OVER (ORDER BY p.prediction DESC, t.id) AS by_model,
ROW_NUMBER() OVER (ORDER BY t.gmv DESC, t.id) AS by_past_spend,
ROW_NUMBER() OVER (ORDER BY t.intents_30d DESC, t.id) AS by_recent_intent
FROM pred p JOIN test t ON t.id = p.id
)
SELECT COUNT(*) AS shoppers, SUM(next_gmv) AS next_month_gmv,
SUM(CASE WHEN by_model <= cohort / 100 THEN next_gmv ELSE 0 END) / SUM(next_gmv) AS top1pct_model,
SUM(CASE WHEN by_past_spend <= cohort / 100 THEN next_gmv ELSE 0 END) / SUM(next_gmv) AS top1pct_past_spend,
SUM(CASE WHEN by_recent_intent <= cohort / 100 THEN next_gmv ELSE 0 END) / SUM(next_gmv) AS top1pct_recent_intent,
SUM(CASE WHEN by_model <= cohort / 20 THEN next_gmv ELSE 0 END) / SUM(next_gmv) AS top5pct_model,
SUM(CASE WHEN by_past_spend <= cohort / 20 THEN next_gmv ELSE 0 END) / SUM(next_gmv) AS top5pct_past_spend
FROM ranked;

Limits​

  • Classifier labels must be Int32 contiguous classes 0..C-1; regressor labels must be finite Float32. Null labels fail the query. Predict must omit label and match the training feature dimension.
  • Prediction imports the fitted forest into nvForest and scores the predict relation on the GPU; a classifier tie resolves to the lowest class index. Training and prediction inputs are limited to i32::MAX rows and the packed feature matrix to i32::MAX elements; the random forest path does not batch rows.
  • Training uses one RAFT pool stream whose allocations share the query grant. The forest is query-local; an empty predict relation returns an empty result.
  • seed sets the bootstrap rows and feature subsets; refits are not guaranteed to be bit-identical, because regression histograms accumulate with floating-point atomics.
  • Float64 features, as wide columns or list elements, are narrowed to Float32 on the device before training or inference. Each value rounds to the nearest Float32, which keeps about seven significant digits; a value beyond the Float32 range becomes infinite and fails the query.