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.
Vector element types
Feature values must be Float32 or Float64.
Arguments and options
Scalar SQL arguments
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
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.
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.