SQL Reference
Machine Learning Model Functions

﻿ functionality supports these SQL functions for machine learning models.
The scope of a machine learning model is the schema.
Supported Machine Learning Models
Supported machine learning models are divided into these categories. 
Regression Models
﻿Simple Linear Regression﻿ 
﻿Multiple Linear Regression﻿ 
﻿Vector Autoregression﻿﻿
﻿Polynomial Regression﻿﻿
﻿Linear Combination Refression﻿ 
﻿Nonlinear Regression﻿﻿
Classification Models
﻿K Nearest Neighbor Classification﻿ 
﻿Naive Bayes Classification﻿ 
﻿Decision Tree﻿﻿
﻿Logistic Regression﻿ 
﻿Support Vector Machine﻿﻿
Clustering and Dimension Reduction Models
﻿Principal Component Analysis﻿﻿
﻿K-Means Clustering﻿﻿
﻿Gaussian Mixture﻿﻿
﻿Linear Discriminant Analysis﻿﻿
Other Models
﻿Association Rules﻿﻿
﻿Feedforward Neural Network﻿ 
﻿
Execute a Query Using a Machine Learning Model
To create a machine learning model and manage the model, see Machine Learning Models﻿  for the corresponding syntaxes. After you have the model, you can execute a query using the model with this syntax.
Syntax
SQL
SELECT <model name>(expression, ...) FROM <tableReference>
SELECT <model name>(expression, ...) FROM <tableReference>
﻿
Example
Create a table with data for the model.
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 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 a multiple linear regression model based on the data.
SQL
CREATE MLMODEL mlr_model TYPE MULTIPLE LINEAR REGRESSION ON (SELECT * FROM mldemo.mlr) 
options('metrics' -> 'true');
Modified 0 rows
CREATE MLMODEL mlr_model TYPE MULTIPLE LINEAR REGRESSION ON (SELECT * FROM mldemo.mlr) 
options('metrics' -> 'true');
Modified 0 rows
﻿
Execute a SELECT query against the multiple linear regression to see the actual and predicted values. Limit the result set to 10 rows.
SQL
SELECT x1, x2, y AS actual, mlr_model(x1, x2) AS predicted 
FROM mldemo.mlr 
LIMIT 10;
SELECT x1, x2, y AS actual, mlr_model(x1, x2) AS predicted 
FROM mldemo.mlr 
LIMIT 10;
﻿
Output
Text
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
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
﻿
Related Links
﻿Machine Learning in Ocient﻿ 
﻿Machine Learning Models﻿ 
Spatiotemporal Operators
Regression Models