---
title: Regression Analysis
slug: Ql2u-WBZtpNnaSgqfw78Y
description: Apply regression analysis within Ocient with OcientML to understand relationships in data and make predictions based on historical trends.
docTags: 
createdAt: Mon Mar 25 2024 17:57:42 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:35 GMT+0000 (Coordinated Universal Time)
---

OcientML™ enables regression analysis, which is the process of finding a best-fit function for a data set, in the Ocient® System.

## Simple Linear Regression

The simplest case is simple linear regression. Simple linear regression finds a best-fit linear relationship between one variable and another.

Suppose you have this data with a bit of noise added. Use the `CREATE TABLE AS SELECT` SQL statement to create the `mldemo.slr` table. Generate 100 rows of random data.

```sql
CREATE TABLE mldemo.slr AS 
(SELECT c1 AS x, c1 * 3.14 - 1.41 + (RAND() - 0.5) * 0.01 AS y 
FROM sys.dummy100);
Modified 100 rows
```

Create a simple linear regression model using this data.

```sql
CREATE MLMODEL my_first_model TYPE SIMPLE LINEAR REGRESSION 
ON (SELECT x, y FROM mldemo.slr);
```

Explore the created model by viewing the data in the `machine_learning_models` and  `simple_linear_regression_models` system catalog tables.

```sql
SELECT name, on_select, slope, y_intercept 
FROM sys.machine_learning_models a, sys.simple_linear_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'my_first_model';
name                                         on_select                                    slope                 y_intercept
--------------------------------------------------------------------------------------------------------------------------------------
my_first_model                               select x, y from mldemo.slr                  3.139995450114637     -1.4102410466743238

Fetched 1 row
```

You can see the calculated slope and the y-intercept from the model, and see that these values are very close to the values used to generate the data. While the input to the model creation, in this case, is very simple, the SQL statement in the ON clause of the model creation can be arbitrarily complex as long as it meets the requirements of the input for the specific model type being created.

Execute the created model by executing the SQL scalar function with the same name as the model you created. The input arguments of the function depend on the specific model type. For regression, the values are the independent variables, and then the function returns the dependent variables.

```sql
SELECT x, y AS actual, my_first_model(x) AS predicted FROM mldemo.slr LIMIT 10;
x          actual                predicted
-------------------------------------------------------
51         158.730137908257      158.72952690917217
52         161.87052128169267    161.8695223592868
53         165.0119331158625     165.00951780940144
54         168.1478263920115     168.14951325951608
55         171.28758882396247    171.28950870963072
56         174.43476312079238    174.42950415974536
57         177.57385971080322    177.56949960986
58         180.70933136639127    180.70949505997464
59         183.84856961481378    183.84949051008925
60         186.99044288540566    186.9894859602039

Fetched 10 rows
```

The model makes predictions that are very close to the actual training data. Then, you can use this data to generate predictions for data that is unknown.

### Machine Learning Model Options

You can provide options for most model types. Provide options by pairing the string for an option to its corresponding value. For example, simple linear regression models have a `metrics` option that you can use to ask the database to calculate some quality of fit metrics. Use the same model and enable metrics collection, which is off by default.

```sql
DROP MLMODEL my_first_model;
Modified 0 rows

CREATE MLMODEL my_first_model TYPE SIMPLE LINEAR REGRESSION 
ON (SELECT x, y FROM mldemo.slr) options('metrics' -> 'true');
Modified 0 rows
```

Examine the `machine_learning_models` and `simple_linear_regression_models` system catalog tables for more fields.

```sql
SELECT name, on_select, slope, y_intercept, coefficient_of_determination, 
adjusted_r2, rmse FROM sys.machine_learning_models a, sys.simple_linear_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'my_first_model';
name                                         on_select                                    slope                 y_intercept           coefficient_of_determination adjusted_r2           rmse
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my_first_model                               select x, y from mldemo.slr                  3.139995450114637     -1.4102410466743238   0.9999999990764926           0.999999999067069     2.7544625001417E-4

Fetched 1 row
```

The database also calculates the coefficient of determination (frequently named r2), the adjusted r2 value, and the RMSE (root mean squared error). All of these values indicate that this model has an extremely good fit.

In addition, you can force a specific y-intercept such that the value of `f(0)` is fixed. Use the `yIntercept` option. Create a model on the same data, but force the y-intercept to be zero. The metrics show that the model is not as good a fit as when the intercept is free to vary.&#x20;

```sql
CREATE MLMODEL force_zero TYPE SIMPLE LINEAR REGRESSION 
ON (SELECT x, y FROM mldemo.slr) options('metrics' -> 'true', 'yIntercept' -> '0');
Modified 0 rows

SELECT name, on_select, slope, y_intercept, coefficient_of_determination, 
adjusted_r2, rmse FROM sys.machine_learning_models a, sys.simple_linear_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'force_zero';
name                                         on_select                                    slope                 y_intercept           coefficient_of_determination adjusted_r2           rmse
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
force_zero                                   select x, y from mldemo.slr                  3.1189470762836766    0.0                   0.9999403831244921           0.9999397747890277    0.06998440678511778

Fetched 1 row
```

## Multiple Linear Regression

In multiple linear regression, the model is still predicting an output value. But instead of handling one input variable, the model can handle an arbitrary number of input variables. Multiple linear regression finds the best-fit model of the form:

:::BlockQuote
y = a1x1 + a2x2 + a3x3 + … + a0
:::

Simple linear regression and multiple linear regression (as well as polynomial regression and linear combination regression) all use a least-squares loss function. Some of the more advanced models allow you to define best fit yourself if the default definition is not satisfactory. Least-squares best fit means that the model minimizes the sum (over all training data rows) of the square of the model error.

Create a training data set with some noise and show that the model can reconstruct the data. Multiple linear regression models also support a metrics option (the metrics are the same as simple linear regression).&#x20;

```sql
CREATE TABLE mldemo.mlr AS (SELECT a.c1 AS x1, b.c1 AS x2, 1 + 2*a.c1 + 3*b.c1 AS y 
FROM sys.dummy10 a, sys.dummy10 b);
Modified 100 rows

CREATE MLMODEL mlr_model TYPE MULTIPLE LINEAR REGRESSION ON (SELECT * FROM mldemo.mlr) 
options('metrics' -> 'true');
Modified 0 rows

SELECT x1, x2, y AS actual, mlr_model(x1, x2) AS predicted FROM mldemo.mlr LIMIT 10;
x1         x2         actual              predicted
----------------------------------------------------------------
6          1          16                  15.999999999999975
6          2          19                  18.999999999999975
6          3          22                  21.999999999999975
6          4          25                  24.999999999999975
6          5          28                  27.999999999999975
6          6          31                  30.999999999999975
6          7          34                  33.99999999999997
6          8          37                  36.99999999999997
6          9          40                  39.99999999999997
6          10         43                  42.99999999999997

Fetched 10 rows
```

## Polynomial Regression

With polynomial regression, the database finds a least-squares best-fit polynomial of whatever degree you specify. Same as multiple linear regression, you determine the number of independent variables.&#x20;

The training data has a `1/(x1 * x2)` term that the model does not match, but that term quickly tends towards zero, so the model should provide a good fit with a quadratic polynomial of `x1` and `x2`. Some of the coefficients match the data, but some do not because the model is compensating for not being able to fit the form of the data exactly. However, the metrics indicate that this model is still a great fit.

```sql
CREATE TABLE mldemo.pr AS 
(SELECT x1, x2, 1.0 / (x1 * x2) - 2*x1 - 0.5*x1*x2 + 2.3*x1^2 + 1.1*x2^2 AS y 
FROM (SELECT a.c1 AS x1, b.c1 AS x2 FROM sys.dummy10 a, sys.dummy10 b));
Modified 100 rows

CREATE MLMODEL pr1 TYPE POLYNOMIAL REGRESSION 
ON (SELECT * FROM mldemo.pr) options('order' -> '2', 'metrics' -> 'true');
Modified 0 rows

SELECT name, on_select, num_arguments, coefficient_of_determination, 
adjusted_r2, rmse, y_intercept, coefficients 
FROM sys.machine_learning_models a, sys.polynomial_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'pr1';
name                                         on_select                                    num_arguments coefficient_of_determination adjusted_r2           rmse                  y_intercept           coefficients
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pr1                                          select * from mldemo.pr                      2             0.9999991319879137           0.9999991140907573    0.06288797037552916   0.720605599825376     [-0.10943699031755831, 1.105235143324365, -2.1094369903178176, -0.4945162377513963, 2.3052351433243388]

Fetched 1 row
```

Polynomial regression models have many available options, including everything available for multiple linear regression. The `negativePowers` option enables you to fit Laurent polynomials. If you set this option to `true`, the model includes independent variables raised to negative powers. Sums of such terms are named Laurent polynomials. The model generates all possible terms such that the sum of the absolute value of the power of each term in each product is less than or equal to the order.

For example, with two independent variables and the order set to 2, the model is:&#x20;

`y = a1*x1^2 + a2*x1^-2 + a3*x2^2 + a4*x2^-2 + a5*x1*x2 + a6*x1^-1*x2 + a7*x1*x2^-1 + a8*x1^-1*x2^-1 a9*x1 + a10*x1^-1 + a11*x2 + a12*x2^-1 + b`

```sql
CREATE TABLE mldemo.pr_negativepowers AS 
(SELECT x1, x2, 1.0 + 12*x2^-1 - 11*x2 + 10*x1^-1 + 9*x1 + 8*(x1 - 1)*(x2 - 1) + 
7*x1*x2^-1 + 6*x1^-1*x2 + 5*x1*x2 + 4*x2^-2 + 3*x2^2 + 2*x1^-2 + x1^2 AS y 
FROM (SELECT a.c1 AS x1, b.c1 AS x2 FROM sys.dummy10 a, sys.dummy10 b));
Modified 100 rows

CREATE MLMODEL pr2 TYPE POLYNOMIAL REGRESSION 
ON (SELECT * FROM mldemo.pr_negativepowers) 
options('order' -> '2', 'metrics' -> 'true', 'negativePowers' -> 'true');
Modified 0 rows

SELECT name, on_select, num_arguments, coefficient_of_determination, 
adjusted_r2, rmse, y_intercept, coefficients 
FROM sys.machine_learning_models a, sys.polynomial_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'pr2';
name                                         on_select                                    num_arguments coefficient_of_determination adjusted_r2           rmse                  y_intercept           coefficients
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pr2                                          select * from mldemo.pr                      2             1.0                          1.0                   1.4203541595255201E-10 -3.2741809263825417E-10 [1.378090447893218E-11, 7.594146281547169E-11, 1.0999999999996046, -5.800996726877696E-11, -1.9999999999236024, -0.5000000000004785, -5.027050118995952E-12, 6.257323548197746E-10, -3.953341155821705E-12, 0.9999999999510328, 2.2999999999966576, -3.637978807091713E-10]
```

You can see that now the model almost perfectly recovers the coefficients, with the rest being nearly zero.

Execute the `pr2` model.

```sql
SELECT x1, x2, y AS actual, pr2(x1, x2) AS predicted FROM mldemo.pr LIMIT 10;
x1                    x2                    actual                predicted
----------------------------------------------------------------------------------------
6.0                   1.0                   69.06666666666666     69.06666666676095
6.0                   2.0                   69.28333333333333     69.28333333346139
6.0                   3.0                   71.75555555555556     71.75555555569368
6.0                   4.0                   76.44166666666666     76.44166666681195
6.0                   5.0                   83.33333333333333     83.33333333348473
6.0                   6.0                   92.42777777777778     92.42777777793454
6.0                   7.0                   103.72380952380954    103.72380952397093
6.0                   8.0                   117.22083333333333    117.22083333349865
6.0                   9.0                   132.9185185185185     132.91851851868702
6.0                   10.0                  150.81666666666666    150.81666666683765

Fetched 10 rows
```

## Linear Combination Regression

The most complex type of linear regression that Ocient supports is linear combination regression. This regression is a generalization of polynomial regression. Polynomial regression tries to find the best linear combination of polynomial terms (`x1`, `x2`,`x1^2`,`x2^2`,`x1*x2`, and so on). Linear combination regression finds the best fit linear combination, where you can specify all the terms.&#x20;

Assume this training data. Use the `CREATE TABLE AS SELECT` SQL statement to create the `mldemo.lcr` table. Generate 1000 rows of data.

```sql
CREATE TABLE mldemo.lcr AS 
(SELECT x1, x2, x3, 10.0 * sin(x1) + 2.0 * cos(x2) - 3.0 * MOD(INT(x1)*INT(x2)*INT(x3), 4) 
AS y FROM (SELECT a.c1 AS x1, b.c1 AS x2, c.c1 AS x3 
FROM sys.dummy10 a, sys.dummy10 b, sys.dummy10 c));
Modified 1000 rows
```

Fit this data using a fifth-degree polynomial model.

```sql
CREATE MLMODEL pr_attempting_lcr TYPE POLYNOMIAL REGRESSION 
ON (SELECT * FROM mldemo.lcr) options('order' -> '5', 'metrics' -> 'true');
Modified 0 rows

SELECT name, on_select, num_arguments, coefficient_of_determination, 
adjusted_r2, rmse, y_intercept, coefficients 
FROM sys.machine_learning_models a, sys.polynomial_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'pr_attempting_lcr';
name                                         on_select                                    num_arguments coefficient_of_determination adjusted_r2           rmse                  y_intercept           coefficients
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pr_attempting_lcr                            select * from mldemo.lcr                     3             0.8319917524743956           0.8314857035360654    3.096446300059235     -55.14103736166726    [8.606630821262804, -3.232482517584192, 0.5805449365661026, -0.049741258743219, 0.0016307692308343673, 8.399340340347107, -0.5287664524114927, 0.08856961220855254, -0.0064693555605100815, 1.7800381437636605E-4, -5.4034476625452825, 0.08856961220520951, -0.008490499399775638, 2.684184502433214E-4, 1.4579535930358838, -0.006469355560037162, 2.68418450241128E-4, -0.16482119055274744, 1.780038143557162E-4, 0.006512838626194554, 84.78854869028874, -0.5287664524188649, 0.08856961220814323, -0.006469355560487793, 1.780038143772283E-4, -0.528766452427062, -0.01649034528388861, 0.0012223956025726305, -5.6680387259322435E-5, 0.08856961220929233, 0.0012223956025593338, -1.6695884464630135E-5, -0.006469355560584848, -5.6680387258156464E-5, 1.78003814376761E-4, -44.36009386495971, 0.08856961220998283, -0.008490499399632612, 2.6841845023805184E-4, 0.08856961221025506, 0.0012223956025763133, -1.669588446505692E-5, -0.008490499399797105, -1.6695884465316113E-5, 2.6841845024373886E-4, 9.206023264675196, -0.006469355560753055, 2.6841845023840556E-4, -0.006469355560705054, -5.6680387258737325E-5, 2.6841845024255106E-4, -0.8194647667934305, 1.7800381438661845E-4, 1.7800381438500682E-4, 0.026149389153476754]

Fetched 1 row
```

The coefficient of determination shows that the model is not a bad fit, but the fit is not great due to the structure of the data. However, given that the model is still a linear combination of functions, linear combination regression can recover the fit if you specify the functions to use.

```sql
CREATE MLMODEL lcr1 TYPE LINEAR COMBINATION REGRESSION 
ON (SELECT x1, x2, x3, y FROM mldemo.lcr) options('function1' -> 'sin(x1)', 
'function2' -> 'cos(x2)', 'function3' -> 'mod(int(x1)*int(x2)*int(x3),4)', 
'metrics' -> 'true');
Modified 0 rows

SELECT name, on_select, num_arguments, coefficient_of_determination, 
adjusted_r2, rmse, y_intercept, coefficients 
FROM sys.machine_learning_models a, sys.linear_combination_regression_models b 
WHERE a.id = b.machine_learning_model_id AND name = 'lcr1';
name                                         on_select                                    num_arguments coefficient_of_determination adjusted_r2           rmse                  y_intercept           coefficients
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lcr1                                         select x1, x2, x3, y from mldemo.lcr         3             1.0                          1.0                   3.597211844847171E-14 3.9968028886505635E-15 [9.99999999999995, 2.000000000000012, -2.999999999999997]

Fetched 1 row
```

You can see that the model finds the coefficients perfectly and has a coefficient of determination of 1.0.

The coefficient of determination represents the amount of variance in the data explained by the model. A coefficient of determination of 1.0 indicates that all the variance is explained and that the model is a perfect fit. A coefficient of determination of 0.0 indicates that none of the variance is explained. A negative coefficient of determination is possible, but you do not find it in linear regression models because these models always find a fit that is at least as good as the constant function (the average of all the training data). That model has a coefficient of determination of 0.0.

## Non-Linear Regression&#x20;

The non-linear regression model is similar to linear combination regression in that you define the form of the model. However, non-linear regression can be more than a linear combination of terms.&#x20;

Assume this training data. Use the `CREATE TABLE AS SELECT` SQL statement to create the `mldemo.nlo_input1` table. Generate 100 rows of data. Then, create a non-linear regression model using five parameters.

```sql
CREATE TABLE mldemo.nlo_input1 AS (
    SELECT x1,
        x2,
        2.0 * sin(3.0 * x1 - 1.0) - 4.0 + 5.0 * x2 as y
    FROM (
            SELECT a.c1 / 4.0 as x1,
                b.c1 / 4.0 as x2
            FROM sys.dummy10 a,
                sys.dummy10 b
        )
);
Modified 100 rows 

CREATE mlmodel nlo1 TYPE NONLINEAR REGRESSION ON (
    SELECT x1,
        x2,
        y
    FROM mldemo.nlo_input1
) options(
    'numParameters'->'5',
    'function'->'a1 * SIN(a2 * x1 + a3) + a4 + a5 * x2',
    'metrics'->'true'
);
Modified 0 rows
```

In this case, specify only one function that covers the whole model.

Use the `machine_learning_models` and `nonlinear_regression_models` system catalog tables to see the metrics and the values that the model chose.

```sql
SELECT name,
    on_select
FROM sys.machine_learning_models a,
    sys.nonlinear_regression_models b
WHERE a.id = b.machine_learning_model_id
    AND name = 'nlo1';
    
name                                         on_select                                    coefficients                                                                                       coefficient_of_determination num_arguments rmse                  adjusted_r2
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
nlo1                                         select x1, x2, y from mldemo.nlo_input1      [-2.085086077121484, 2.999196008218594, 2.14015015138094, -3.9822034572268925, 4.9800866766427205] 0.9985484616734742           2             0.06623956527655686   0.9985483156287689

Fetched 1 row

```

If you look at the coefficients, you can see the amplitude (2.0) is negative in the model, but that is offset by subtracting pi from the phase. The model still almost exactly matches how the data is generated, but it just found a slightly different, but equivalent, way of doing it.

Non-linear regression models (and all of the other models that use the same infrastructure) have lots of options that control how the search works. These options affect the length of time it takes for training the model, but they also affect the likelihood of getting stuck in a global minimum. The model chooses default values such that they should be sufficient for a wide range of use cases.

With non-linear regression models, you can specify a loss function other than least-squares. In this example, specify the `abs(f-y)` loss function.

```sql
CREATE MLMODEL nlo2 type NONLINEAR REGRESSION ON (
    SELECT x1,
        x2,
        y
    FROM mldemo.nlo_input1
) options(
    'numParameters'->'5',
    'function'->'a1 * sin(a2 * x1 + a3) + a4 + a5 * x2',
    'lossFunction'->'abs(f-y)'
);
Modified 0 rows
```

In this case, find the best fit that minimizes the sum of the absolute value of the model errors instead of the sum of the square of the model errors. The variable `f` represents the value of the model, and the variable `y` represents the actual value in the training data.&#x20;

The catalog indicates the model is nearly the same as the previous model. In other words, changing the loss function here did not substantially change anything. But there are cases where other loss functions are appropriate and they could substantially change the model.

## Non-Linear Regression with Neural Networks

In all of the non-linear cases, you have to make a good guess for the form of the target function. However, with neural networks, you can leave it up to the network to try to figure out that form. The downside is that aspects of the model are a black box.&#x20;

This example uses the same example as non-linear regression, but instead of specifying the form of the function, it relies on what the neural net can do with little information. The size of the network layers and the number of layers greatly influence how good of a fit a neural net model has. This example uses a simple network.

```sql
CREATE MLMODEL nn1 TYPE FEEDFORWARD NETWORK ON (
    SELECT x1,
        x2,
        y
    FROM mldemo.nlo_input1
) options(
    'metrics'->'true',
    'hiddenLayers'->'2',
    'hiddenLayerSize'->'2',
    'outputs'->'1',
    'lossFunction'->'squared_error'
);
Modified 0 rows
```

In the case of neural network models, the metric used is an average loss because RMSE and the coefficient of determination are generally only appropriate for least squares models, and neural networks might be using other loss functions.&#x20;

You can see the average loss by querying the `machine_learning_models` and `feedforward_network_models` system catalog tables.

```sql
SELECT name,
    on_select,
    num_arguments,
    average_loss
FROM sys.machine_learning_models a,
    sys.feedforward_network_models b
WHERE a.id = b.machine_learning_model_id
    AND name = 'nn1';
    
name                                         on_select                                    num_arguments average_loss
------------------------------------------------------------------------------------------------------------------------------
nn1                                          select x1, x2, y from mldemo.nlo_input1      2             0.026744549902417786

Fetched 1 row
```

Compute the coefficient of determination manually.&#x20;

```sql
SELECT AVG(y)
FROM mldemo.nlo_input1;

(sum(y))/(null_if(double(count(y)), (0)))
------------------------------------------
2.8698217335180987

Fetched 1 row

SELECT 1.0 - SUM((nn1(x1, x2) - y) ^ 2) / SUM((y - 2.8698217335180987) ^ 2) AS r2
FROM mldemo.nlo_input1;

r2
----------------------
0.9981701280250409

Fetched 1 row
```

This model fits the data very well.

## Vector-Valued Regression

Vector-valued regression is the case where the dependent variable has multiple components. You can use any type of model to learn each component separately, or you can use a feedforward network to learn all components in one model.&#x20;

The example creates 100 rows of sample data.

```sql
CREATE TABLE mldemo.vector_valued AS (
    SELECT x1,
        { { 2 * x1 + 3,
        3 * x1 -2 } } as y
    FROM (
            SELECT c1 AS x1
            FROM sys.dummy100
        )
);
Modified 100 rows
```

There is a dependent variable that depends on `x1`, which is vector-valued. Each component can fit a linear model, which can then predict vector-valued results.

```sql
CREATE MLMODEL vv1 TYPE SIMPLE LINEAR REGRESSION ON (
    SELECT x1,
        y [1,1]
    FROM mldemo.vector_valued
);
Modified 0 rows 

CREATE MLMODEL vv2 TYPE SIMPLE LINEAR REGRESSION ON (
    SELECT x1,
        y [1,2]
    FROM mldemo.vector_valued
);
Modified 0 rows
```

The query verifies that it produces reasonable results.

```sql
SELECT x1,
    y AS actual,
    { { vv1(x1),
    vv2(x1) } } AS predicted
FROM mldemo.vector_valued
LIMIT 10;

x1         actual                                                                          predicted
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
51         [[105.0, 151.0]]                                                                [[105.0, 151.0]]
52         [[107.0, 154.0]]                                                                [[107.0, 154.0]]
53         [[109.0, 157.0]]                                                                [[109.0, 157.0]]
54         [[111.0, 160.0]]                                                                [[111.0, 160.0]]
55         [[113.0, 163.0]]                                                                [[113.0, 163.0]]
56         [[115.0, 166.0]]                                                                [[115.0, 166.0]]
57         [[117.0, 169.0]]                                                                [[117.0, 169.0]]
58         [[119.0, 172.0]]                                                                [[119.0, 172.0]]
59         [[121.0, 175.0]]                                                                [[121.0, 175.0]]
60         [[123.0, 178.0]]                                                                [[123.0, 178.0]]

Fetched 10 rows
```

This example shows that a single model can perform this with the feedforward network model type.

```sql
CREATE MLMODEL vv3 TYPE FEEDFORWARD NETWORK ON (
    SELECT x1,
        y
    FROM mldemo.vector_valued
) options(
    'hiddenLayers'->'2',
    'hiddenLayerSize'->'2',
    'outputs'->'2',
    'lossFunction'->'vector_squared_error'
);
Modified 0 rows
```

`outputs`, which is set to the size of the vector, and the `lossFunction` option is set to use `vector_squared_error`.&#x20;

This example shows how well the model can predict the output.

```sql
SELECT x1,
    y AS actual,
    vv3(x1) AS predicted
FROM mldemo.vector_valued
LIMIT 10;

x1         actual                                                                          predicted
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
51         [[105.0, 151.0]]                                                                [[104.99939226614688, 150.99924962001512]]
52         [[107.0, 154.0]]                                                                [[106.99940272332195, 153.99914250568781]]
53         [[109.0, 157.0]]                                                                [[108.999413180497, 156.99903539136054]]
54         [[111.0, 160.0]]                                                                [[110.99942363767205, 159.99892827703323]]
55         [[113.0, 163.0]]                                                                [[112.9994340948471, 162.99882116270598]]
56         [[115.0, 166.0]]                                                                [[114.99944455202213, 165.99871404837862]]
57         [[117.0, 169.0]]                                                                [[116.99945500919716, 168.99860693405134]]
58         [[119.0, 172.0]]                                                                [[118.99946546637223, 171.99849981972403]]
59         [[121.0, 175.0]]                                                                [[120.99947592354728, 174.99839270539675]]
60         [[123.0, 178.0]]                                                                [[122.99948638072233, 177.99828559106948]]

Fetched 10 rows
```

## Autoregression

Autoregression is linear regression where the independent variables are lags of the dependent variable. In other words, it is a series where the next value depends linearly on the previous N values.&#x20;

These examples do not use a new model type. Instead, they use multiple linear regression models.&#x20;

One of the important things about autoregression is that some variables indicate the order of the data. This variable can be a time variable, but it does not need to be. This variable is necessary to correctly set up the model, but it is not directly used by the model. Instead, the variable helps build the correct lags.

This example builds a model to predict the Fibonacci sequence. This statement creates a table with the first several Fibonacci terms.

```sql
CREATE TABLE mldemo.fib AS (
    SELECT 0 AS t,
        0 AS f
    UNION ALL
    SELECT 1,
        1
    UNION ALL
    SELECT 2,
        1
    UNION ALL
    SELECT 3,
        2
    UNION ALL
    SELECT 4,
        3
    UNION ALL
    SELECT 5,
        5
    UNION ALL
    SELECT 6,
        8
    UNION ALL
    SELECT 7,
        13
    UNION ALL
    SELECT 8,
        21
    UNION ALL
    SELECT 9,
        34
);
Modified 10 rows
```

This example builds a model where the dependent variable depends on two lags.

```sql
CREATE MLMODEL fib TYPE MULTIPLE LINEAR REGRESSION ON (
    SELECT LAG(f, 1) OVER(
            ORDER BY t
        ),
        LAG(f, 2) OVER(
            ORDER BY t
        ),
        f
    FROM mldemo.fib
);
Modified 0 rows
```

When you execute the model, which provides the model with two lags, the model can predict the next value of the sequence that is not in the data, `55`.

```sql
SELECT fib(21, 34);

((((1.0000000000001137))*((21)))+((1.4210854715202004e-14)))+(((1))*((34)))
----------------------------------------------------------------------------
55.0000000000024

Fetched 1 row
```

## Vector Autoregression

Vector autoregression is just a more complex version of autoregression. This model uses multiple time series, where the next value for each time series depends linearly on the lags of all of the time series.&#x20;

This statement sets up some data where the next value for two time series depends on the previous two values of both time series.

```sql
CREATE TABLE mldemo.var_input AS (
    SELECT 3.1415 AS s1,
        1.4142 AS s2,
        0 AS t
    UNION ALL
    SELECT 2.7182 AS s1,
        1.618 AS s2,
        1 AS t
);
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    2 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 1
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 0
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    3 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 2
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 1
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    4 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 3
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 2
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    5 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 4
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 3
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    6 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 5
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 4
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    7 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 6
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 5
    );
INSERT INTO mldemo.var_input
SELECT 0.9 * s1l1 - 0.8 * s2l1 + 0.7 * s1l2 - 0.6 * s2l2 AS s1,
    0.5 * s1l1 - 0.4 * s2l1 + 0.3 * s1l2 - 0.2 * s2l2 AS s2,
    8 AS t
FROM (
        SELECT s1 AS s1l1,
            s2 AS s2l1
        FROM mldemo.var_input
        WHERE t = 7
    ),
    (
        SELECT s1 AS s1l2,
            s2 AS s2l2
        FROM mldemo.var_input
        WHERE t = 6
    );
```

The query inspects the data.&#x20;

```sql
SELECT *
FROM mldemo.var_input
ORDER BY t;

s1                    s2                    t
----------------------------------------------------------------
3.1415                1.4142                0
2.7182                1.618                 1
2.50251               1.37151               2
2.086991              1.1945109999999999    3
1.8515340999999994    1.0421421             4
1.5768541099999998    0.8961053099999997    5
1.3730730609999995    0.7770167409999997    6
1.1802870530999996    0.6695650050999997    7
1.0215474418099997    0.5788360946099997    8

Fetched 9 rows
```

This example fits the vector autoregression model to the data.&#x20;

```sql
CREATE MLMODEL var1 type VECTOR AUTOREGRESSION ON (
    SELECT { { s1,
        s2 } },
        { { s1_lag1,
        s2_lag1 } },
        { { s1_lag2,
        s2_lag2 } }
    FROM (
            SELECT s1,
                s2,
                LAG(s1, 1) OVER(
                    ORDER BY t
                ) as s1_lag1,
                LAG(s1, 2) OVER(
                    ORDER BY t
                ) as s1_lag2,
                LAG(s2, 1) OVER(
                    ORDER BY t
                ) as s2_lag1,
                LAG(s2, 2) OVER(
                    ORDER BY t
                ) AS s2_lag2
            FROM mldemo.var_input
        )
) options('numVariables'->'2', 'numLags'->'2');
Modified 0 rows
```

See the details of the model in the `machine_learning_models` and `vector_autoregression_models` system catalog tables.

```sql
SELECT NAME,
    coefficients,
    coefficient_of_determination
FROM sys.machine_learning_models a,
    sys.vector_autoregression_models b
WHERE a.id = b.machine_learning_model_id
    AND name = 'var1';
    
name                                         coefficients                                                                    coefficient_of_determination
----------------------------------------------------------------------------------------------------------------------------------------------------------
var1                                         [-2.2737367544323206E-12, 0.8999999999432475, -0.799999999901047, 0.6999999999643478, -0.599999999973079, -5.684341886080801E-13, 0.4999999999781721, -0.39999999997235136, 0.29999999999017746, -0.1999999999825377] NULL

Fetched 1 row
```

The model recovered the expected coefficients. The coefficient of determination is NULL here because the model did not enable metrics collection in the options.&#x20;

This example checks that the model matches the data reasonably well.

```sql
SELECT var1({ { s1_lag1, s2_lag1 } }, { { s1_lag2, s2_lag2 } }) AS predicted,
    { { s1,
    s2 } } AS actual
FROM (
        SELECT s1,
            s2,
            s1_lag1,
            s2_lag1,
            s1_lag2,
            s2_lag2
        FROM (
                SELECT s1,
                    s2,
                    LAG(s1, 1) OVER(
                        ORDER BY t
                    ) AS s1_lag1,
                    LAG(s1, 2) OVER(
                        ORDER BY t
                    ) AS s1_lag2,
                    LAG(s2, 1) OVER(
                        ORDER BY t
                    ) AS s2_lag1,
                    LAG(s2, 2) OVER(
                        ORDER BY t
                    ) AS s2_lag2
                FROM mldemo.var_input
            )
    );
    
    
predicted                                                                       actual
----------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL                                                                            [[3.1415, 1.4142]]
NULL                                                                            [[2.7182, 1.618]]
[[2.5025099999296385, 1.3715099999786722]]                                      [[2.50251, 1.37151]]
[[2.086990999938066, 1.1945109999842818]]                                       [[2.086991, 1.1945109999999999]]
[[1.8515340999451868, 1.0421420999862723]]                                      [[1.8515340999999994, 1.0421421]]
[[1.5768541099535214, 0.8961053099881894]]                                      [[1.5768541099999998, 0.8961053099999997]]
[[1.3730730609589523, 0.7770167409897997]]                                      [[1.3730730609999995, 0.7770167409999997]]
[[1.1802870530645944, 0.6695650050911028]]                                      [[1.1802870530999996, 0.6695650050999997]]
[[1.0215474417789623, 0.5788360946022622]]                                      [[1.0215474418099997, 0.5788360946099997]]

Fetched 9 rows
```

The first two values have no predictions because the lags necessary to drive the predictions do not exist.

For details, see [Vector Autoregression](<./././SQL Reference/Machine Learning Model Functions/Regression Models.md>).

## Related Links

[Machine Learning Model Functions](<./././SQL Reference/Machine Learning Model Functions.md>)&#x20;

[Regression Models](<./././SQL Reference/Machine Learning Model Functions/Regression Models.md>)&#x20;

