Skip to main content

Linear Regression

TYPE 'linear' fits a least-squares regression with optional L2 regularization (ridge) via batch gradient descent. The first column of the training SELECT is the continuous label; remaining columns are features. The trainer iterates training iterations over the data source until the per-iteration weight delta falls below tolerance or max_iters is reached.

When to use it​

  • Continuous targets with roughly linear relationships to the features.
  • A baseline you want to beat before reaching for trees or DNNs.
  • An interpretable model where the per-feature weight is the deliverable.

When NOT to use it​

  • Strong feature interactions or non-linearities — use engineered interaction features or DNN regression; the native tree trainers are classification-only.
  • Unscaled features spanning many orders of magnitude — batch gradient descent will converge poorly without standardisation.
  • Categorical features without one-hot encoding (encode categories numerically before training).

Syntax​

CREATE MODEL <name>(DOUBLE, DOUBLE[, DOUBLE ...]) RETURNS DOUBLE
TYPE { 'linear' | 'linear_regression' | 'linear-regression' }
OPTIONS (...)
AS SELECT <label>, <f1>, <f2>, ... FROM <source>;

The function call signature drops the label — you pass features only at inference.

Options​

OptionDefaultTypeRangeWhat it does
learning_rate0.01float> 0batch gradient descent step size
l2_lambda0.0float>= 0L2 regularization strength (ridge); 0 disables
max_iters100int>= 1Maximum training iterations
tolerance1e-4float>= 0Convergence threshold on weight delta between iterations
partitions1int>= 1Training partitions; see execution and memory limits

Examples​

These fragments assume the named source tables exist. Match training and inference feature order and preprocessing. For a complete dataset and runnable script, follow the linked tutorial.

Minimal:

CREATE MODEL price(DOUBLE, DOUBLE) RETURNS DOUBLE
TYPE 'linear'
AS SELECT list_price AS label, sqft, bedrooms FROM listings;

SELECT listing_id, price(sqft, bedrooms) AS estimated_price
FROM new_listings;

Tuned with ridge + extended budget:

CREATE MODEL revenue_forecast(DOUBLE, DOUBLE, DOUBLE) RETURNS DOUBLE
TYPE 'linear'
OPTIONS (
    learning_rate = 0.005,
    l2_lambda = 0.01,
    max_iters = 1000,
    tolerance = 1e-6,
    partitions = 4
)
AS SELECT
    revenue_usd AS label,
    marketing_spend,
    site_visits,
    avg_order_value
FROM weekly_metrics;

Output shape​

price(f1, f2, ...) returns a DOUBLE predicted value for each row.

Tuning notes​

  • Standardise features (z-score or min-max) before training. batch gradient descent convergence is dramatically better with comparable feature scales.
  • If loss oscillates, halve learning_rate. If it crawls, try 0.05 or 0.1.
  • Add l2_lambda (start at 0.001–0.01) when feature count is high relative to row count to control overfit.
  • Bump max_iters first if converged = false is reported; otherwise relax tolerance.
  • Benchmark partitions for your workload; scaling is not necessarily proportional.

Convergence and quality​

TrainingMetrics reports iterations and converged. converged = true means consecutive-iteration weight delta dropped under tolerance; false with iterations == max_iters means the budget ran out.

EVALUATE MODEL emits rmse and mae when the held-out query labels its first column label and the prediction is named prediction (the canonical projection from a model call). Use both — RMSE penalises large errors quadratically while MAE is robust.

Last updated on