Regression Models

CREATE MLMODEL model_name
    TYPE model_type ON (
        select_statement
)
    [ options( option_list ) ]

CREATE MLMODEL my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM public.my_table
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);

SELECT my_model(col1) FROM my_table;

CREATE MLMODEL my_model
TYPE MULTIPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    x2,
    x3,
    y
  FROM public.my_table
)
options(
  'metrics' -> 'true'
);

SELECT my_model(col1, col2, col3) FROM my_table;

Estimate <x1(t), x2(t)> based on x1(t-1), x2(t-1), x1(t-2), and x2(t-2)

CREATE MLMODEL my_model
TYPE VECTOR AUTOREGRESSION ON (
  SELECT
    {{x1, x2, x3}},
    LAG_VECTORS(x1, x2, x3, 1, 4) OVER(ORDER BY t)
  FROM public.my_table
)
OPTIONS(
  'metrics' -> 'true',
  'numVariables' -> '3',
  'numLags' -> '4'
);

SELECT my_model(lag_vector_1, lag_vector_2, lag_vector_3, lag_vector_4) AS predicted,
    {{x1, x2, x3}} AS actual
FROM (
    SELECT x1, x2, x3,
        LAG_VECTORS(x1, x2, x3, 1, 4) OVER(ORDER BY t)
    FROM my_table
);

CREATE MLMODEL my_model
TYPE POLYNOMIAL REGRESSION ON (
  SELECT
    x1,
    x2,
    x3,
    y
  FROM public.my_table
)
options(
  'order' -> '3',
  'metrics' -> 'true'
);

SELECT my_model(col1, col2, col3) FROM my_table;

CREATE MLMODEL my_model
TYPE LINEAR COMBINATION REGRESSION
ON (
  SELECT
    x1,
    x2,
    x3,
    y1
  FROM public.my_table
)
options(
  'function1' -> 'sin(x1 * x2 + x3)',
  'function2' -> 'cos(x1 * x3)'
);

SELECT my_model(col1, col2, col3) FROM my_table;

CREATE MLMODEL my_model
TYPE NONLINEAR REGRESSION
ON (
  SELECT
    x1,
    x2,
    y1
  FROM public.my_table
)
options(
  'numParameters' -> '5',
  'function' -> 'a1 * sin(a2 * x1 + a3) + a4 + a5 * x2'
);

SELECT my_model(x1, x2) FROM my_table;

CREATE MLMODEL my_reg_tree
TYPE REGRESSION TREE
ON (
    SELECT feature1, feature2, feature3, target
    FROM training_data
)
OPTIONS (
    'maxDepth' -> '5',
    'enableResplits' -> 'true'
);

SELECT my_reg_tree(feature1, feature2, feature3)
FROM scoring_data;

CREATE MLMODEL house_price_gbt
TYPE GRADIENT BOOSTED TREES
ON (
    SELECT square_feet, bedrooms, bathrooms, age_years, price
    FROM housing_data
    WHERE price IS NOT NULL
)
OPTIONS (
    'numChildren' -> '100',
    'learningRate' -> '0.1',
    'lossFunction' -> 'squared_error'
);

SELECT house_id,
    house_price_gbt(square_feet, bedrooms, bathrooms, age_years) AS predicted_price
FROM new_listings
WHERE square_feet IS NOT NULL
    AND bedrooms IS NOT NULL
    AND bathrooms IS NOT NULL
    AND age_years IS NOT NULL;