http://git-wip-us.apache.org/repos/asf/madlib-site/blob/e283664c/docs/v1.14/group__grp__nn.html
----------------------------------------------------------------------
diff --git a/docs/v1.14/group__grp__nn.html b/docs/v1.14/group__grp__nn.html
new file mode 100644
index 0000000..4c51023
--- /dev/null
+++ b/docs/v1.14/group__grp__nn.html
@@ -0,0 +1,1117 @@
+<!-- HTML header for doxygen 1.8.4-->
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
+<html xmlns="http://www.w3.org/1999/xhtml";>
+<head>
+<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
+<meta http-equiv="X-UA-Compatible" content="IE=9"/>
+<meta name="generator" content="Doxygen 1.8.13"/>
+<meta name="keywords" content="madlib,postgres,greenplum,machine learning,data
mining,deep learning,ensemble methods,data science,market basket
analysis,affinity analysis,pca,lda,regression,elastic net,huber
white,proportional hazards,k-means,latent dirichlet allocation,bayes,support
vector machines,svm"/>
+<title>MADlib: Neural Network</title>
+<link href="tabs.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="jquery.js"></script>
+<script type="text/javascript" src="dynsections.js"></script>
+<link href="navtree.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="resize.js"></script>
+<script type="text/javascript" src="navtreedata.js"></script>
+<script type="text/javascript" src="navtree.js"></script>
+<script type="text/javascript">
+ $(document).ready(initResizable);
+</script>
+<link href="search/search.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="search/searchdata.js"></script>
+<script type="text/javascript" src="search/search.js"></script>
+<script type="text/javascript">
+ $(document).ready(function() { init_search(); });
+</script>
+<script type="text/x-mathjax-config">
+ MathJax.Hub.Config({
+ extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"],
+ jax: ["input/TeX","output/HTML-CSS"],
+});
+</script><script type="text/javascript"
src="http://cdn.mathjax.org/mathjax/latest/MathJax.js";></script>
+<!-- hack in the navigation tree -->
+<script type="text/javascript" src="eigen_navtree_hacks.js"></script>
+<link href="doxygen.css" rel="stylesheet" type="text/css" />
+<link href="madlib_extra.css" rel="stylesheet" type="text/css"/>
+<!-- google analytics -->
+<script>
+ (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
+ (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new
Date();a=s.createElement(o),
+
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
+ })(window,document,'script','//www.google-analytics.com/analytics.js','ga');
+ ga('create', 'UA-45382226-1', 'madlib.apache.org');
+ ga('send', 'pageview');
+</script>
+</head>
+<body>
+<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
+<div id="titlearea">
+<table cellspacing="0" cellpadding="0">
+ <tbody>
+ <tr style="height: 56px;">
+ <td id="projectlogo"><a href="http://madlib.apache.org";><img alt="Logo"
src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td>
+ <td style="padding-left: 0.5em;">
+ <div id="projectname">
+ <span id="projectnumber">1.14</span>
+ </div>
+ <div id="projectbrief">User Documentation for Apache MADlib</div>
+ </td>
+ <td> <div id="MSearchBox" class="MSearchBoxInactive">
+ <span class="left">
+ <img id="MSearchSelect" src="search/mag_sel.png"
+ onmouseover="return searchBox.OnSearchSelectShow()"
+ onmouseout="return searchBox.OnSearchSelectHide()"
+ alt=""/>
+ <input type="text" id="MSearchField" value="Search" accesskey="S"
+ onfocus="searchBox.OnSearchFieldFocus(true)"
+ onblur="searchBox.OnSearchFieldFocus(false)"
+ onkeyup="searchBox.OnSearchFieldChange(event)"/>
+ </span><span class="right">
+ <a id="MSearchClose"
href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg"
border="0" src="search/close.png" alt=""/></a>
+ </span>
+ </div>
+</td>
+ </tr>
+ </tbody>
+</table>
+</div>
+<!-- end header part -->
+<!-- Generated by Doxygen 1.8.13 -->
+<script type="text/javascript">
+var searchBox = new SearchBox("searchBox", "search",false,'Search');
+</script>
+</div><!-- top -->
+<div id="side-nav" class="ui-resizable side-nav-resizable">
+ <div id="nav-tree">
+ <div id="nav-tree-contents">
+ <div id="nav-sync" class="sync"></div>
+ </div>
+ </div>
+ <div id="splitbar" style="-moz-user-select:none;"
+ class="ui-resizable-handle">
+ </div>
+</div>
+<script type="text/javascript">
+$(document).ready(function(){initNavTree('group__grp__nn.html','');});
+</script>
+<div id="doc-content">
+<!-- window showing the filter options -->
+<div id="MSearchSelectWindow"
+ onmouseover="return searchBox.OnSearchSelectShow()"
+ onmouseout="return searchBox.OnSearchSelectHide()"
+ onkeydown="return searchBox.OnSearchSelectKey(event)">
+</div>
+
+<!-- iframe showing the search results (closed by default) -->
+<div id="MSearchResultsWindow">
+<iframe src="javascript:void(0)" frameborder="0"
+ name="MSearchResults" id="MSearchResults">
+</iframe>
+</div>
+
+<div class="header">
+ <div class="headertitle">
+<div class="title">Neural Network<div class="ingroups"><a class="el"
href="group__grp__super.html">Supervised Learning</a></div></div> </div>
+</div><!--header-->
+<div class="contents">
+<div class="toc"><b>Contents</b><ul>
+<li class="level1">
+<a href="#mlp_classification">Classification</a> </li>
+<li class="level1">
+<a href="#mlp_regression">Regression</a> </li>
+<li class="level1">
+<a href="#optimizer_params">Optimizer Parameters</a> </li>
+<li class="level1">
+<a href="#predict">Prediction Functions</a> </li>
+<li class="level1">
+<a href="#example">Examples</a> </li>
+<li class="level1">
+<a href="#background">Technical Background</a> </li>
+<li class="level1">
+<a href="#literature">Literature</a> </li>
+<li class="level1">
+<a href="#related">Related Topics</a> </li>
+</ul>
+</div><p>Multilayer Perceptron (MLP) is a type of neural network that can be
used for regression and classification.</p>
+<p>MLPs consist of several fully connected hidden layers with non-linear
activation functions. In the case of classification, the final layer of the
neural net has as many nodes as classes, and the output of the neural net can
be interpreted as the probability that a given input feature belongs to a
specific class.</p>
+<p>MLP can be used with or without mini-batching. The advantage of using
mini-batching is that it can perform better than stochastic gradient descent
(default MADlib optimizer) because it uses more than one training example at a
time, typically resulting faster and smoother convergence [3].</p>
+<dl class="section note"><dt>Note</dt><dd>In order to use mini-batching, you
must first run the <a
href="group__grp__minibatch__preprocessing.html">Mini-Batch Preprocessor</a>,
which is a utility that prepares input data for use by models that support
mini-batch as an optimization option, such as MLP. This is a one-time operation
and you would only need to re-run the preprocessor if your input data has
changed, or if you change the grouping parameter.</dd></dl>
+<p><a class="anchor" id="mlp_classification"></a></p><dl class="section
user"><dt>Classification Training Function</dt><dd>The MLP classification
training function has the following format:</dd></dl>
+<pre class="syntax">
+mlp_classification(
+ source_table,
+ output_table,
+ independent_varname,
+ dependent_varname,
+ hidden_layer_sizes,
+ optimizer_params,
+ activation,
+ weights,
+ warm_start,
+ verbose,
+ grouping_col
+ )
+</pre><p><b>Arguments</b> </p><dl class="arglist">
+<dt>source_table </dt>
+<dd><p class="startdd">TEXT. Name of the table containing the training data.
If you are using mini-batching, this is the name of the output table from the
mini-batch preprocessor.</p>
+<p class="enddd"></p>
+</dd>
+<dt>output_table </dt>
+<dd><p class="startdd">TEXT. Name of the output table containing the model.
Details of the output table are shown below. </p>
+<p class="enddd"></p>
+</dd>
+<dt>independent_varname </dt>
+<dd><p class="startdd">TEXT. Expression list to evaluate for the independent
variables. If you are using mini-batching, set this parameter to
'independent_varname' which is the hardcoded name of the column from the
mini-batch preprocessor containing the packed independent variables.</p>
+<dl class="section note"><dt>Note</dt><dd>If you are not using mini-batching,
please note that an intercept variable should not be included as part of this
expression - this is different from other MADlib modules. Also please note that
independent variables should be encoded properly. All values are cast to DOUBLE
PRECISION, so categorical variables should be one-hot or dummy encoded as
appropriate. See <a href="group__grp__encode__categorical.html">Encoding
Categorical Variables</a> for more details. </dd></dl>
+</dd>
+<dt>dependent_varname </dt>
+<dd><p class="startdd">TEXT. Name of the dependent variable column. For
classification, supported types are: text, varchar, character varying, char,
character integer, smallint, bigint, and boolean. If you are using
mini-batching, set this parameter to 'dependent_varname' which is the hardcoded
name of the column from the mini-batch preprocessor containing the packed
dependent variables.</p>
+<p class="enddd"></p>
+</dd>
+<dt>hidden_layer_sizes (optional) </dt>
+<dd><p class="startdd">INTEGER[], default: ARRAY[100]. The number of neurons
in each hidden layer. The length of this array will determine the number of
hidden layers. For example, ARRAY[5,10] means 2 hidden layers, one with 5
neurons and the other with 10 neurons. Use ARRAY[]::INTEGER[] for no hidden
layers. </p>
+<p class="enddd"></p>
+</dd>
+<dt>optimizer_params (optional) </dt>
+<dd><p class="startdd">TEXT, default: NULL. Parameters for optimization in a
comma-separated string of key-value pairs. See the description below for
details. </p>
+<p class="enddd"></p>
+</dd>
+<dt>activation (optional) </dt>
+<dd><p class="startdd">TEXT, default: 'sigmoid'. Activation function.
Currently three functions are supported: 'sigmoid' (default), 'relu', and
'tanh'. The text can be any prefix of the three strings; for e.g., specifying
's' will use sigmoid activation. </p>
+<p class="enddd"></p>
+</dd>
+<dt>weights (optional) </dt>
+<dd>TEXT, default: 1. Weights for input rows. Column name which specifies the
weight for each input row. This weight will be incorporated into the update
during stochastic gradient descent (SGD), but will not be used for loss
calculations. If not specified, weight for each row will default to 1 (equal
weights). Column should be a numeric type. <dl class="section
note"><dt>Note</dt><dd>The 'weights' parameter is not currently for
mini-batching. </dd></dl>
+</dd>
+<dt>warm_start (optional) </dt>
+<dd><p class="startdd">BOOLEAN, default: FALSE. Initalize weights with the
coefficients from the last call of the training function. If set to true,
weights will be initialized from the output_table generated by the previous
run. Note that all parameters other than optimizer_params and verbose must
remain constant between calls when warm_start is used.</p>
+<dl class="section note"><dt>Note</dt><dd>The warm start feature works based
on the name of the output_table. When using warm start, do not drop the output
table or the output table summary before calling the training function, since
these are needed to obtain the weights from the previous run. If you are not
using warm start, the output table and the output table summary must be dropped
in the usual way before calling the training function. </dd></dl>
+</dd>
+<dt>verbose (optional) </dt>
+<dd><p class="startdd">BOOLEAN, default: FALSE. Provides verbose output of the
results of training, including the value of loss at each iteration.</p>
+<p class="enddd"></p>
+</dd>
+<dt>grouping_col (optional) </dt>
+<dd>TEXT, default: NULL. A single column or a list of comma-separated columns
that divides the input data into discrete groups, resulting in one model per
group. When this value is NULL, no grouping is used and a single model is
generated for all data. If you are using mini-batching, you must have run the
mini-batch preprocessor with exactly the same groups that you specify here for
MLP training. If you change the groups, or remove the groups, then you must
re-run the mini-batch preprocessor. </dd>
+</dl>
+<p><b>Output tables</b> <br />
+ The model table produced by MLP contains the following columns: </p><table
class="output">
+<tr>
+<th>coeffs </th><td>FLOAT8[]. Flat array containing the weights of the neural
net. </td></tr>
+<tr>
+<th>n_iterations </th><td>INTEGER. Number of iterations completed by the
stochastic gradient descent algorithm. The algorithm either converged in this
number of iterations or hit the maximum number specified in the optimization
parameters. </td></tr>
+<tr>
+<th>loss </th><td>FLOAT8. The cross entropy loss over the training data. See
Technical Background section below for more details. </td></tr>
+<tr>
+<th>grouping columns </th><td>If grouping_col is specified during training, a
column for each grouping column is created. </td></tr>
+</table>
+<p>A summary table named <output_table>_summary is also created, which
has the following columns: </p><table class="output">
+<tr>
+<th>source_table </th><td>The source table. </td></tr>
+<tr>
+<th>independent_varname </th><td>The independent variables. </td></tr>
+<tr>
+<th>dependent_varname </th><td>The dependent variable. </td></tr>
+<tr>
+<th>tolerance </th><td>The tolerance as given in optimizer_params. </td></tr>
+<tr>
+<th>learning_rate_init </th><td>The initial learning rate as given in
optimizer_params. </td></tr>
+<tr>
+<th>learning_rate_policy </th><td>The learning rate policy as given in
optimizer_params. </td></tr>
+<tr>
+<th>n_iterations </th><td>The number of iterations run. </td></tr>
+<tr>
+<th>n_tries </th><td>The number of tries as given in optimizer_params.
</td></tr>
+<tr>
+<th>layer_sizes </th><td>The number of units in each layer including the input
and output layers. </td></tr>
+<tr>
+<th>activation </th><td>The activation function. </td></tr>
+<tr>
+<th>is_classification </th><td>True if the model was trained for
classification, False if it was trained for regression. </td></tr>
+<tr>
+<th>classes </th><td>The classes which were trained against (empty for
regression). </td></tr>
+<tr>
+<th>weights </th><td>The weight column used during training. </td></tr>
+<tr>
+<th>grouping_col </th><td><p class="starttd">NULL if no grouping_col was
specified during training, and a comma separated list of grouping column names
if not. </p>
+<p class="endtd"></p>
+</td></tr>
+</table>
+<p>A standardization table named <output_table>_standardization is also
create, that has the following columns: </p><table class="output">
+<tr>
+<th>mean </th><td>The mean for all input features (used for normalization).
</td></tr>
+<tr>
+<th>std </th><td>The standard deviation for all input features (used for
normalization). </td></tr>
+<tr>
+<th>grouping columns </th><td>If grouping_col is specified during training, a
column for each grouping column is created. </td></tr>
+</table>
+<p><a class="anchor" id="mlp_regression"></a></p><dl class="section
user"><dt>Regression Training Function</dt><dd>The MLP regression training
function has the following format: <pre class="syntax">
+mlp_regression(
+ source_table,
+ output_table,
+ independent_varname,
+ dependent_varname,
+ hidden_layer_sizes,
+ optimizer_params,
+ activation,
+ weights,
+ warm_start,
+ verbose,
+ grouping_col
+ )
+</pre></dd></dl>
+<p><b>Arguments</b> </p>
+<p>Parameters for regression are largely the same as for classification. In
the model table, the loss refers to mean square error instead of cross entropy
loss. In the summary table, there is no classes column. The following arguments
have specifications which differ from mlp_classification: </p><dl
class="arglist">
+<dt>dependent_varname </dt>
+<dd>TEXT. Name of the dependent variable column. For regression, supported
types are any numeric type, or array of numeric types (for multiple
regression). </dd>
+</dl>
+<p><a class="anchor" id="optimizer_params"></a></p><dl class="section
user"><dt>Optimizer Parameters</dt><dd>Parameters in this section are supplied
in the <em>optimizer_params</em> argument as a string containing a
comma-delimited list of name-value pairs. All of these named parameters are
optional and their order does not matter. You must use the format
"<param_name> = <value>" to specify the value of a parameter,
otherwise the parameter is ignored.</dd></dl>
+<pre class="syntax">
+ 'learning_rate_init = <value>,
+ learning_rate_policy = <value>,
+ gamma = <value>,
+ power = <value>,
+ iterations_per_step = <value>,
+ n_iterations = <value>,
+ n_tries = <value>,
+ lambda = <value>,
+ tolerance = <value>,
+ batch_size = <value>,
+ n_epochs = <value>'
+</pre><p> <b>Optimizer</b> <b>Parameters</b> </p><dl class="arglist">
+<dt>learning_rate_init </dt>
+<dd><p class="startdd">Default: 0.001. Also known as the learning rate. A
small value is usually desirable to ensure convergence, while a large value
provides more room for progress during training. Since the best value depends
on the condition number of the data, in practice one often tunes this
parameter. </p>
+<p class="enddd"></p>
+</dd>
+<dt>learning_rate_policy </dt>
+<dd><p class="startdd">Default: constant. One of 'constant', 'exp', 'inv' or
'step' or any prefix of these (e.g., 's' means 'step'). These are defined
below, where 'iter' is the current iteration of SGD:</p><ul>
+<li>'constant': learning_rate = learning_rate_init</li>
+<li>'exp': learning_rate = learning_rate_init * gamma^(iter)</li>
+<li>'inv': learning_rate = learning_rate_init * (iter+1)^(-power)</li>
+<li>'step': learning_rate = learning_rate_init *
gamma^(floor(iter/iterations_per_step)) </li>
+</ul>
+<p class="enddd"></p>
+</dd>
+<dt>gamma </dt>
+<dd><p class="startdd">Default: 0.1. Decay rate for learning rate when
learning_rate_policy is 'exp' or 'step'. </p>
+<p class="enddd"></p>
+</dd>
+<dt>power </dt>
+<dd><p class="startdd">Default: 0.5. Exponent for learning_rate_policy =
'inv'. </p>
+<p class="enddd"></p>
+</dd>
+<dt>iterations_per_step </dt>
+<dd><p class="startdd">Default: 100. Number of iterations to run before
decreasing the learning rate by a factor of gamma. Valid for learning rate
policy = 'step'. </p>
+<p class="enddd"></p>
+</dd>
+<dt>n_iterations </dt>
+<dd><p class="startdd">Default: 100. The maximum number of iterations allowed.
</p>
+<p class="enddd"></p>
+</dd>
+<dt>n_tries </dt>
+<dd><p class="startdd">Default: 1. Number of times to retrain the network with
randomly initialized weights. </p>
+<p class="enddd"></p>
+</dd>
+<dt>lambda </dt>
+<dd><p class="startdd">Default: 0. The regularization coefficient for L2
regularization. </p>
+<p class="enddd"></p>
+</dd>
+<dt>tolerance </dt>
+<dd><p class="startdd">Default: 0.001. The criterion to end iterations. The
training stops whenever the difference between the training models of two
consecutive iterations is smaller than <em>tolerance</em> or the iteration
number is larger than <em>n_iterations</em>. If you want to run the full number
of iterations specified in <em>n_interations</em>, set tolerance=0.0 </p>
+<p class="enddd"></p>
+</dd>
+<dt>batch_size </dt>
+<dd><p class="startdd">Default: min(200, buffer_size) where buffer_size is set
in the mini-batch preprocessor. The 'batch_size' is the size of the mini-batch
used in the optimizer. This parameter is only used in the case of
mini-batching. </p>
+<p class="enddd"></p>
+</dd>
+<dt>n_epochs </dt>
+<dd>Default: 1. Represents the number of times each batch is used by the
optimizer. This parameter is only used in the case of mini-batching. </dd>
+</dl>
+<p><a class="anchor" id="predict"></a></p><dl class="section
user"><dt>Prediction Function</dt><dd>Used to generate predictions on novel
data given a previously trained model. The same syntax is used for
classification and regression. <pre class="syntax">
+mlp_predict(
+ model_table,
+ data_table,
+ id_col_name,
+ output_table,
+ pred_type
+ )
+</pre></dd></dl>
+<p><b>Arguments</b> </p><dl class="arglist">
+<dt>model_table </dt>
+<dd><p class="startdd">TEXT. Model table produced by the training function.</p>
+<p class="enddd"></p>
+</dd>
+<dt>data_table </dt>
+<dd><p class="startdd">TEXT. Name of the table containing the data for
prediction. This table is expected to contain the same input features that were
used during training. The table should also contain id_col_name used for
identifying each row.</p>
+<p class="enddd"></p>
+</dd>
+<dt>id_col_name </dt>
+<dd><p class="startdd">TEXT. The name of the id column in data_table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>output_table </dt>
+<dd>TEXT. Name of the table where output predictions are written. If this
table name is already in use, an error is returned. Table contains: <table
class="output">
+<tr>
+<th>id </th><td>Gives the 'id' for each prediction, corresponding to each row
from the data_table. </td></tr>
+<tr>
+<th>estimated_COL_NAME </th><td>(For pred_type='response') The estimated class
for classification or value for regression, where COL_NAME is the name of the
column to be predicted from training data. </td></tr>
+<tr>
+<th>prob_CLASS </th><td><p class="starttd">(For pred_type='prob' for
classification) The probability of a given class CLASS as given by softmax.
There will be one column for each class in the training data. </p>
+<p class="endtd"></p>
+</td></tr>
+</table>
+</dd>
+<dt>pred_type </dt>
+<dd>TEXT. The type of output requested: 'response' gives the actual
prediction, 'prob' gives the probability of each class. For regression, only
type='response' is defined. </dd>
+</dl>
+<p><a class="anchor" id="example"></a></p><dl class="section
user"><dt>Examples</dt><dd></dd></dl>
+<h4>Classification without Mini-Batching</h4>
+<ol type="1">
+<li>Create an input data set. <pre class="example">
+DROP TABLE IF EXISTS iris_data;
+CREATE TABLE iris_data(
+ id serial,
+ attributes numeric[],
+ class_text varchar,
+ class integer,
+ state varchar
+);
+INSERT INTO iris_data(id, attributes, class_text, class, state) VALUES
+(1,ARRAY[5.0,3.2,1.2,0.2],'Iris_setosa',1,'Alaska'),
+(2,ARRAY[5.5,3.5,1.3,0.2],'Iris_setosa',1,'Alaska'),
+(3,ARRAY[4.9,3.1,1.5,0.1],'Iris_setosa',1,'Alaska'),
+(4,ARRAY[4.4,3.0,1.3,0.2],'Iris_setosa',1,'Alaska'),
+(5,ARRAY[5.1,3.4,1.5,0.2],'Iris_setosa',1,'Alaska'),
+(6,ARRAY[5.0,3.5,1.3,0.3],'Iris_setosa',1,'Alaska'),
+(7,ARRAY[4.5,2.3,1.3,0.3],'Iris_setosa',1,'Alaska'),
+(8,ARRAY[4.4,3.2,1.3,0.2],'Iris_setosa',1,'Alaska'),
+(9,ARRAY[5.0,3.5,1.6,0.6],'Iris_setosa',1,'Alaska'),
+(10,ARRAY[5.1,3.8,1.9,0.4],'Iris_setosa',1,'Alaska'),
+(11,ARRAY[4.8,3.0,1.4,0.3],'Iris_setosa',1,'Alaska'),
+(12,ARRAY[5.1,3.8,1.6,0.2],'Iris_setosa',1,'Alaska'),
+(13,ARRAY[5.7,2.8,4.5,1.3],'Iris_versicolor',2,'Alaska'),
+(14,ARRAY[6.3,3.3,4.7,1.6],'Iris_versicolor',2,'Alaska'),
+(15,ARRAY[4.9,2.4,3.3,1.0],'Iris_versicolor',2,'Alaska'),
+(16,ARRAY[6.6,2.9,4.6,1.3],'Iris_versicolor',2,'Alaska'),
+(17,ARRAY[5.2,2.7,3.9,1.4],'Iris_versicolor',2,'Alaska'),
+(18,ARRAY[5.0,2.0,3.5,1.0],'Iris_versicolor',2,'Alaska'),
+(19,ARRAY[5.9,3.0,4.2,1.5],'Iris_versicolor',2,'Alaska'),
+(20,ARRAY[6.0,2.2,4.0,1.0],'Iris_versicolor',2,'Alaska'),
+(21,ARRAY[6.1,2.9,4.7,1.4],'Iris_versicolor',2,'Alaska'),
+(22,ARRAY[5.6,2.9,3.6,1.3],'Iris_versicolor',2,'Alaska'),
+(23,ARRAY[6.7,3.1,4.4,1.4],'Iris_versicolor',2,'Alaska'),
+(24,ARRAY[5.6,3.0,4.5,1.5],'Iris_versicolor',2,'Alaska'),
+(25,ARRAY[5.8,2.7,4.1,1.0],'Iris_versicolor',2,'Alaska'),
+(26,ARRAY[6.2,2.2,4.5,1.5],'Iris_versicolor',2,'Alaska'),
+(27,ARRAY[5.6,2.5,3.9,1.1],'Iris_versicolor',2,'Alaska'),
+(28,ARRAY[5.0,3.4,1.5,0.2],'Iris_setosa',1,'Tennessee'),
+(29,ARRAY[4.4,2.9,1.4,0.2],'Iris_setosa',1,'Tennessee'),
+(30,ARRAY[4.9,3.1,1.5,0.1],'Iris_setosa',1,'Tennessee'),
+(31,ARRAY[5.4,3.7,1.5,0.2],'Iris_setosa',1,'Tennessee'),
+(32,ARRAY[4.8,3.4,1.6,0.2],'Iris_setosa',1,'Tennessee'),
+(33,ARRAY[4.8,3.0,1.4,0.1],'Iris_setosa',1,'Tennessee'),
+(34,ARRAY[4.3,3.0,1.1,0.1],'Iris_setosa',1,'Tennessee'),
+(35,ARRAY[5.8,4.0,1.2,0.2],'Iris_setosa',1,'Tennessee'),
+(36,ARRAY[5.7,4.4,1.5,0.4],'Iris_setosa',1,'Tennessee'),
+(37,ARRAY[5.4,3.9,1.3,0.4],'Iris_setosa',1,'Tennessee'),
+(38,ARRAY[6.0,2.9,4.5,1.5],'Iris_versicolor',2,'Tennessee'),
+(39,ARRAY[5.7,2.6,3.5,1.0],'Iris_versicolor',2,'Tennessee'),
+(40,ARRAY[5.5,2.4,3.8,1.1],'Iris_versicolor',2,'Tennessee'),
+(41,ARRAY[5.5,2.4,3.7,1.0],'Iris_versicolor',2,'Tennessee'),
+(42,ARRAY[5.8,2.7,3.9,1.2],'Iris_versicolor',2,'Tennessee'),
+(43,ARRAY[6.0,2.7,5.1,1.6],'Iris_versicolor',2,'Tennessee'),
+(44,ARRAY[5.4,3.0,4.5,1.5],'Iris_versicolor',2,'Tennessee'),
+(45,ARRAY[6.0,3.4,4.5,1.6],'Iris_versicolor',2,'Tennessee'),
+(46,ARRAY[6.7,3.1,4.7,1.5],'Iris_versicolor',2,'Tennessee'),
+(47,ARRAY[6.3,2.3,4.4,1.3],'Iris_versicolor',2,'Tennessee'),
+(48,ARRAY[5.6,3.0,4.1,1.3],'Iris_versicolor',2,'Tennessee'),
+(49,ARRAY[5.5,2.5,4.0,1.3],'Iris_versicolor',2,'Tennessee'),
+(50,ARRAY[5.5,2.6,4.4,1.2],'Iris_versicolor',2,'Tennessee'),
+(51,ARRAY[6.1,3.0,4.6,1.4],'Iris_versicolor',2,'Tennessee'),
+(52,ARRAY[5.8,2.6,4.0,1.2],'Iris_versicolor',2,'Tennessee');
+</pre></li>
+<li>Generate a multilayer perceptron with a single hidden layer of 5 units.
Use the attributes column as the independent variables, and use the class
column as the classification. Set the tolerance to 0 so that 500 iterations
will be run. Use a hyperbolic tangent activation function. The model will be
written to mlp_model. <pre class="example">
+DROP TABLE IF EXISTS mlp_model, mlp_model_summary, mlp_model_standardization;
+-- Set seed so results are reproducible
+SELECT setseed(0);
+SELECT madlib.mlp_classification(
+ 'iris_data', -- Source table
+ 'mlp_model', -- Destination table
+ 'attributes', -- Input features
+ 'class_text', -- Label
+ ARRAY[5], -- Number of units per layer
+ 'learning_rate_init=0.003,
+ n_iterations=500,
+ tolerance=0', -- Optimizer params
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE -- Not verbose
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_model;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+coeff |
{-0.40378996718,0.0157490328855,-0.298904053444,-0.984152185093,-0.657684089715
...
+loss | 0.0103518565103
+num_iterations | 500
+</pre> View the model summary table: <pre class="example">
+SELECT * FROM mlp_model_summary;
+</pre> <pre class="result">
+-[ RECORD 1 ]--------+------------------------------
+source_table | iris_data
+independent_varname | attributes
+dependent_varname | class_text
+dependent_vartype | character varying
+tolerance | 0
+learning_rate_init | 0.003
+learning_rate_policy | constant
+n_iterations | 500
+n_tries | 1
+layer_sizes | {4,5,2}
+activation | tanh
+is_classification | t
+classes | {Iris_setosa,Iris_versicolor}
+weights | 1
+grouping_col | NULL
+</pre> View the model standardization table: <pre class="example">
+SELECT * FROM mlp_model_standardization;
+</pre> <pre class="result">
+-[ RECORD 1 ]------------------------------------------------------------------
+mean | {5.45961538461539,2.99807692307692,3.025,0.851923076923077}
+std | {0.598799958694505,0.498262513685689,1.41840579525043,0.550346179381454}
+</pre></li>
+<li>Now let's use the model to predict. In the following example we will use
the training data set for prediction as well, which is not usual but serves to
show the syntax. The prediction is in the estimated_class_text column with the
actual value in the class_text column. <pre class="example">
+DROP TABLE IF EXISTS mlp_prediction;
+\x off
+SELECT madlib.mlp_predict(
+ 'mlp_model', -- Model table
+ 'iris_data', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_prediction', -- Output table for predictions
+ 'response' -- Output classes, not probabilities
+ );
+SELECT * FROM mlp_prediction JOIN iris_data USING (id) ORDER BY id;
+</pre> <pre class="result">
+ id | estimated_class_text | attributes | class_text | class |
state
+----+----------------------+-------------------+-----------------+-------+-----------
+ 1 | Iris_setosa | {5.0,3.2,1.2,0.2} | Iris_setosa | 1 |
Alaska
+ 2 | Iris_setosa | {5.5,3.5,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 3 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 |
Alaska
+ 4 | Iris_setosa | {4.4,3.0,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 5 | Iris_setosa | {5.1,3.4,1.5,0.2} | Iris_setosa | 1 |
Alaska
+ 6 | Iris_setosa | {5.0,3.5,1.3,0.3} | Iris_setosa | 1 |
Alaska
+ 7 | Iris_setosa | {4.5,2.3,1.3,0.3} | Iris_setosa | 1 |
Alaska
+ 8 | Iris_setosa | {4.4,3.2,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 9 | Iris_setosa | {5.0,3.5,1.6,0.6} | Iris_setosa | 1 |
Alaska
+ 10 | Iris_setosa | {5.1,3.8,1.9,0.4} | Iris_setosa | 1 |
Alaska
+ 11 | Iris_setosa | {4.8,3.0,1.4,0.3} | Iris_setosa | 1 |
Alaska
+ 12 | Iris_setosa | {5.1,3.8,1.6,0.2} | Iris_setosa | 1 |
Alaska
+ 13 | Iris_versicolor | {5.7,2.8,4.5,1.3} | Iris_versicolor | 2 |
Alaska
+ 14 | Iris_versicolor | {6.3,3.3,4.7,1.6} | Iris_versicolor | 2 |
Alaska
+ 15 | Iris_versicolor | {4.9,2.4,3.3,1.0} | Iris_versicolor | 2 |
Alaska
+ 16 | Iris_versicolor | {6.6,2.9,4.6,1.3} | Iris_versicolor | 2 |
Alaska
+ 17 | Iris_versicolor | {5.2,2.7,3.9,1.4} | Iris_versicolor | 2 |
Alaska
+ 18 | Iris_versicolor | {5.0,2.0,3.5,1.0} | Iris_versicolor | 2 |
Alaska
+ 19 | Iris_versicolor | {5.9,3.0,4.2,1.5} | Iris_versicolor | 2 |
Alaska
+ 20 | Iris_versicolor | {6.0,2.2,4.0,1.0} | Iris_versicolor | 2 |
Alaska
+ 21 | Iris_versicolor | {6.1,2.9,4.7,1.4} | Iris_versicolor | 2 |
Alaska
+ 22 | Iris_versicolor | {5.6,2.9,3.6,1.3} | Iris_versicolor | 2 |
Alaska
+ 23 | Iris_versicolor | {6.7,3.1,4.4,1.4} | Iris_versicolor | 2 |
Alaska
+ 24 | Iris_versicolor | {5.6,3.0,4.5,1.5} | Iris_versicolor | 2 |
Alaska
+ 25 | Iris_versicolor | {5.8,2.7,4.1,1.0} | Iris_versicolor | 2 |
Alaska
+ 26 | Iris_versicolor | {6.2,2.2,4.5,1.5} | Iris_versicolor | 2 |
Alaska
+ 27 | Iris_versicolor | {5.6,2.5,3.9,1.1} | Iris_versicolor | 2 |
Alaska
+ 28 | Iris_setosa | {5.0,3.4,1.5,0.2} | Iris_setosa | 1 |
Tennessee
+ 29 | Iris_setosa | {4.4,2.9,1.4,0.2} | Iris_setosa | 1 |
Tennessee
+ 30 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 |
Tennessee
+ 31 | Iris_setosa | {5.4,3.7,1.5,0.2} | Iris_setosa | 1 |
Tennessee
+ 32 | Iris_setosa | {4.8,3.4,1.6,0.2} | Iris_setosa | 1 |
Tennessee
+ 33 | Iris_setosa | {4.8,3.0,1.4,0.1} | Iris_setosa | 1 |
Tennessee
+ 34 | Iris_setosa | {4.3,3.0,1.1,0.1} | Iris_setosa | 1 |
Tennessee
+ 35 | Iris_setosa | {5.8,4.0,1.2,0.2} | Iris_setosa | 1 |
Tennessee
+ 36 | Iris_setosa | {5.7,4.4,1.5,0.4} | Iris_setosa | 1 |
Tennessee
+ 37 | Iris_setosa | {5.4,3.9,1.3,0.4} | Iris_setosa | 1 |
Tennessee
+ 38 | Iris_versicolor | {6.0,2.9,4.5,1.5} | Iris_versicolor | 2 |
Tennessee
+ 39 | Iris_versicolor | {5.7,2.6,3.5,1.0} | Iris_versicolor | 2 |
Tennessee
+ 40 | Iris_versicolor | {5.5,2.4,3.8,1.1} | Iris_versicolor | 2 |
Tennessee
+ 41 | Iris_versicolor | {5.5,2.4,3.7,1.0} | Iris_versicolor | 2 |
Tennessee
+ 42 | Iris_versicolor | {5.8,2.7,3.9,1.2} | Iris_versicolor | 2 |
Tennessee
+ 43 | Iris_versicolor | {6.0,2.7,5.1,1.6} | Iris_versicolor | 2 |
Tennessee
+ 44 | Iris_versicolor | {5.4,3.0,4.5,1.5} | Iris_versicolor | 2 |
Tennessee
+ 45 | Iris_versicolor | {6.0,3.4,4.5,1.6} | Iris_versicolor | 2 |
Tennessee
+ 46 | Iris_versicolor | {6.7,3.1,4.7,1.5} | Iris_versicolor | 2 |
Tennessee
+ 47 | Iris_versicolor | {6.3,2.3,4.4,1.3} | Iris_versicolor | 2 |
Tennessee
+ 48 | Iris_versicolor | {5.6,3.0,4.1,1.3} | Iris_versicolor | 2 |
Tennessee
+ 49 | Iris_versicolor | {5.5,2.5,4.0,1.3} | Iris_versicolor | 2 |
Tennessee
+ 50 | Iris_versicolor | {5.5,2.6,4.4,1.2} | Iris_versicolor | 2 |
Tennessee
+ 51 | Iris_versicolor | {6.1,3.0,4.6,1.4} | Iris_versicolor | 2 |
Tennessee
+ 52 | Iris_versicolor | {5.8,2.6,4.0,1.2} | Iris_versicolor | 2 |
Tennessee
+(52 rows)
+</pre> Count the misclassifications: <pre class="example">
+SELECT COUNT(*) FROM mlp_prediction JOIN iris_data USING (id)
+WHERE mlp_prediction.estimated_class_text != iris_data.class_text;
+</pre> <pre class="result">
+ count
+-------+
+ 0
+</pre></li>
+</ol>
+<h4>Classification with Mini-Batching</h4>
+<ol type="1">
+<li>Use the same data set as above. Call mini-batch preprocessor: <pre
class="example">
+DROP TABLE IF EXISTS iris_data_packed, iris_data_packed_summary,
iris_data_packed_standardization;
+SELECT madlib.minibatch_preprocessor('iris_data', -- Source table
+ 'iris_data_packed', -- Output table
+ 'class_text', -- Dependent variable
+ 'attributes' -- Independent
variables
+ );
+</pre></li>
+<li>Train the classification model using similar parameters as before: <pre
class="example">
+DROP TABLE IF EXISTS mlp_model, mlp_model_summary, mlp_model_standardization;
+-- Set seed so results are reproducible
+SELECT setseed(0);
+SELECT madlib.mlp_classification(
+ 'iris_data_packed', -- Output table from mini-batch preprocessor
+ 'mlp_model', -- Destination table
+ 'independent_varname', -- Hardcode to this, from table iris_data_packed
+ 'dependent_varname', -- Hardcode to this, from table iris_data_packed
+ ARRAY[5], -- Number of units per layer
+ 'learning_rate_init=0.1,
+ n_iterations=500,
+ tolerance=0', -- Optimizer params
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE -- Not verbose
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_model;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+coeff | {-0.0780564661828377,-0.0781452670639994,0.3083605989842 ...
+loss | 0.00563534904146765
+num_iterations | 500
+</pre></li>
+<li>Now let's use the model to predict. As before we will use the training
data set for prediction as well, which is not usual but serves to show the
syntax. The prediction is in the estimated_class_text column with the actual
value in the class_text column. <pre class="example">
+DROP TABLE IF EXISTS mlp_prediction;
+\x off
+SELECT madlib.mlp_predict(
+ 'mlp_model', -- Model table
+ 'iris_data', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_prediction', -- Output table for predictions
+ 'response' -- Output classes, not probabilities
+ );
+SELECT * FROM mlp_prediction JOIN iris_data USING (id) ORDER BY id;
+</pre> <pre class="result">
+ id | estimated_class_text | attributes | class_text | class |
state
+----+----------------------+-------------------+-----------------+-------+-----------
+ 1 | Iris_setosa | {5.0,3.2,1.2,0.2} | Iris_setosa | 1 |
Alaska
+ 2 | Iris_setosa | {5.5,3.5,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 3 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 |
Alaska
+ 4 | Iris_setosa | {4.4,3.0,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 5 | Iris_setosa | {5.1,3.4,1.5,0.2} | Iris_setosa | 1 |
Alaska
+ 6 | Iris_setosa | {5.0,3.5,1.3,0.3} | Iris_setosa | 1 |
Alaska
+ 7 | Iris_setosa | {4.5,2.3,1.3,0.3} | Iris_setosa | 1 |
Alaska
+ 8 | Iris_setosa | {4.4,3.2,1.3,0.2} | Iris_setosa | 1 |
Alaska
+ 9 | Iris_setosa | {5.0,3.5,1.6,0.6} | Iris_setosa | 1 |
Alaska
+ 10 | Iris_setosa | {5.1,3.8,1.9,0.4} | Iris_setosa | 1 |
Alaska
+ 11 | Iris_setosa | {4.8,3.0,1.4,0.3} | Iris_setosa | 1 |
Alaska
+ 12 | Iris_setosa | {5.1,3.8,1.6,0.2} | Iris_setosa | 1 |
Alaska
+ 13 | Iris_versicolor | {5.7,2.8,4.5,1.3} | Iris_versicolor | 2 |
Alaska
+ 14 | Iris_versicolor | {6.3,3.3,4.7,1.6} | Iris_versicolor | 2 |
Alaska
+ 15 | Iris_versicolor | {4.9,2.4,3.3,1.0} | Iris_versicolor | 2 |
Alaska
+ 16 | Iris_versicolor | {6.6,2.9,4.6,1.3} | Iris_versicolor | 2 |
Alaska
+ 17 | Iris_versicolor | {5.2,2.7,3.9,1.4} | Iris_versicolor | 2 |
Alaska
+ 18 | Iris_versicolor | {5.0,2.0,3.5,1.0} | Iris_versicolor | 2 |
Alaska
+ 19 | Iris_versicolor | {5.9,3.0,4.2,1.5} | Iris_versicolor | 2 |
Alaska
+ 20 | Iris_versicolor | {6.0,2.2,4.0,1.0} | Iris_versicolor | 2 |
Alaska
+ 21 | Iris_versicolor | {6.1,2.9,4.7,1.4} | Iris_versicolor | 2 |
Alaska
+ 22 | Iris_versicolor | {5.6,2.9,3.6,1.3} | Iris_versicolor | 2 |
Alaska
+ 23 | Iris_versicolor | {6.7,3.1,4.4,1.4} | Iris_versicolor | 2 |
Alaska
+ 24 | Iris_versicolor | {5.6,3.0,4.5,1.5} | Iris_versicolor | 2 |
Alaska
+ 25 | Iris_versicolor | {5.8,2.7,4.1,1.0} | Iris_versicolor | 2 |
Alaska
+ 26 | Iris_versicolor | {6.2,2.2,4.5,1.5} | Iris_versicolor | 2 |
Alaska
+ 27 | Iris_versicolor | {5.6,2.5,3.9,1.1} | Iris_versicolor | 2 |
Alaska
+ 28 | Iris_setosa | {5.0,3.4,1.5,0.2} | Iris_setosa | 1 |
Tennessee
+ 29 | Iris_setosa | {4.4,2.9,1.4,0.2} | Iris_setosa | 1 |
Tennessee
+ 30 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 |
Tennessee
+ 31 | Iris_setosa | {5.4,3.7,1.5,0.2} | Iris_setosa | 1 |
Tennessee
+ 32 | Iris_setosa | {4.8,3.4,1.6,0.2} | Iris_setosa | 1 |
Tennessee
+ 33 | Iris_setosa | {4.8,3.0,1.4,0.1} | Iris_setosa | 1 |
Tennessee
+ 34 | Iris_setosa | {4.3,3.0,1.1,0.1} | Iris_setosa | 1 |
Tennessee
+ 35 | Iris_setosa | {5.8,4.0,1.2,0.2} | Iris_setosa | 1 |
Tennessee
+ 36 | Iris_setosa | {5.7,4.4,1.5,0.4} | Iris_setosa | 1 |
Tennessee
+ 37 | Iris_setosa | {5.4,3.9,1.3,0.4} | Iris_setosa | 1 |
Tennessee
+ 38 | Iris_versicolor | {6.0,2.9,4.5,1.5} | Iris_versicolor | 2 |
Tennessee
+ 39 | Iris_versicolor | {5.7,2.6,3.5,1.0} | Iris_versicolor | 2 |
Tennessee
+ 40 | Iris_versicolor | {5.5,2.4,3.8,1.1} | Iris_versicolor | 2 |
Tennessee
+ 41 | Iris_versicolor | {5.5,2.4,3.7,1.0} | Iris_versicolor | 2 |
Tennessee
+ 42 | Iris_versicolor | {5.8,2.7,3.9,1.2} | Iris_versicolor | 2 |
Tennessee
+ 43 | Iris_versicolor | {6.0,2.7,5.1,1.6} | Iris_versicolor | 2 |
Tennessee
+ 44 | Iris_versicolor | {5.4,3.0,4.5,1.5} | Iris_versicolor | 2 |
Tennessee
+ 45 | Iris_versicolor | {6.0,3.4,4.5,1.6} | Iris_versicolor | 2 |
Tennessee
+ 46 | Iris_versicolor | {6.7,3.1,4.7,1.5} | Iris_versicolor | 2 |
Tennessee
+ 47 | Iris_versicolor | {6.3,2.3,4.4,1.3} | Iris_versicolor | 2 |
Tennessee
+ 48 | Iris_versicolor | {5.6,3.0,4.1,1.3} | Iris_versicolor | 2 |
Tennessee
+ 49 | Iris_versicolor | {5.5,2.5,4.0,1.3} | Iris_versicolor | 2 |
Tennessee
+ 50 | Iris_versicolor | {5.5,2.6,4.4,1.2} | Iris_versicolor | 2 |
Tennessee
+ 51 | Iris_versicolor | {6.1,3.0,4.6,1.4} | Iris_versicolor | 2 |
Tennessee
+ 52 | Iris_versicolor | {5.8,2.6,4.0,1.2} | Iris_versicolor | 2 |
Tennessee
+(52 rows)
+</pre> Count the misclassifications: <pre class="example">
+SELECT COUNT(*) FROM mlp_prediction JOIN iris_data USING (id)
+WHERE mlp_prediction.estimated_class_text != iris_data.class_text;
+</pre> <pre class="result">
+ count
+-------+
+ 0
+</pre></li>
+</ol>
+<h4>Classification with Other Parameters</h4>
+<ol type="1">
+<li>Now, use the n_tries optimizer parameter to learn and choose the best
model among n_tries number of models learnt by the algorithm. Run only for 50
iterations and choose the best model from this short run. Note we are not using
mini-batching here. <pre class="example">
+DROP TABLE IF EXISTS mlp_model, mlp_model_summary, mlp_model_standardization;
+-- Set seed so results are reproducible
+SELECT setseed(0);
+SELECT madlib.mlp_classification(
+ 'iris_data', -- Source table
+ 'mlp_model', -- Destination table
+ 'attributes', -- Input features
+ 'class_text', -- Label
+ ARRAY[5], -- Number of units per layer
+ 'learning_rate_init=0.003,
+ n_iterations=50,
+ tolerance=0,
+ n_tries=3', -- Optimizer params, with n_tries
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE -- Not verbose
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_model;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+coeff | {0.000156316559088915,0.131131017223563,-0.293990512682215 ...
+loss | 0.142238768280717
+num_iterations | 50
+</pre></li>
+<li>Next, use the warm_start parameter to start learning a new model, using
the coefficients already present in mlp_model. Note that we must not drop the
mlp_model table, and cannot use the n_tries parameter if warm_start is used.
<pre class="example">
+SELECT madlib.mlp_classification(
+ 'iris_data', -- Source table
+ 'mlp_model', -- Destination table
+ 'attributes', -- Input features
+ 'class_text', -- Label
+ ARRAY[5], -- Number of units per layer
+ 'learning_rate_init=0.003,
+ n_iterations=450,
+ tolerance=0', -- Optimizer params
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ TRUE, -- Warm start
+ FALSE -- Not verbose
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_model;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+coeff | {0.0883013960215441,0.235944854050211,-0.398126039487036 ...
+loss | 0.00818899646775659
+num_iterations | 450
+</pre> Notice that the loss is lower compared to the previous example, despite
having the same values for every other parameter. This is because the algorithm
learnt three different models starting with a different set of initial weights
for the coefficients, and chose the best model among them as the initial
weights for the coefficients when run with warm start.</li>
+</ol>
+<h4>Classification with Grouping</h4>
+<ol type="1">
+<li>Next, group the training data by state, and learn a different model for
each state. Note we are not using mini-batching in this example. <pre
class="example">
+DROP TABLE IF EXISTS mlp_model_group, mlp_model_group_summary,
mlp_model_group_standardization;
+-- Set seed so results are reproducible
+SELECT setseed(0);
+SELECT madlib.mlp_classification(
+ 'iris_data', -- Source table
+ 'mlp_model_group', -- Destination table
+ 'attributes', -- Input features
+ 'class_text', -- Label
+ ARRAY[5], -- Number of units per layer
+ 'learning_rate_init=0.003,
+ n_iterations=500, -- Optimizer params
+ tolerance=0',
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE, -- Not verbose
+ 'state' -- Grouping column
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_model_group ORDER BY state;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+state | Alaska
+coeff |
{-0.51246602223,-0.78952457411,0.454192045225,0.223214894458,0.188804700547 ...
+loss | 0.0225081995679
+num_iterations | 500
+-[ RECORD 2
]--+------------------------------------------------------------------------------------
+state | Tennessee
+coeff |
{-0.215009937565,0.116581594162,-0.397643279185,0.919193295184,-0.0811341736111
...
+loss | 0.0182854983946
+num_iterations | 500
+</pre> A separate model is learnt for each state, and the result table
displays the name of the state (grouping column) associated with the model.</li>
+<li>Prediction based on grouping using the state column: <pre class="example">
+DROP TABLE IF EXISTS mlp_prediction;
+SELECT madlib.mlp_predict(
+ 'mlp_model_group', -- Model table
+ 'iris_data', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_prediction', -- Output table for predictions
+ 'response' -- Output classes, not probabilities
+ );
+SELECT * FROM mlp_prediction JOIN iris_data USING (state,id) ORDER BY state,
id;
+</pre> Result for the classification model: <pre class="result">
+ state | id | estimated_class_text | attributes | class_text |
class
+-----------+----+----------------------+-------------------+-----------------+-------
+ Alaska | 1 | Iris_setosa | {5.0,3.2,1.2,0.2} | Iris_setosa |
1
+ Alaska | 2 | Iris_setosa | {5.5,3.5,1.3,0.2} | Iris_setosa |
1
+ Alaska | 3 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa |
1
+ Alaska | 4 | Iris_setosa | {4.4,3.0,1.3,0.2} | Iris_setosa |
1
+ Alaska | 5 | Iris_setosa | {5.1,3.4,1.5,0.2} | Iris_setosa |
1
+ Alaska | 6 | Iris_setosa | {5.0,3.5,1.3,0.3} | Iris_setosa |
1
+ Alaska | 7 | Iris_setosa | {4.5,2.3,1.3,0.3} | Iris_setosa |
1
+ Alaska | 8 | Iris_setosa | {4.4,3.2,1.3,0.2} | Iris_setosa |
1
+ Alaska | 9 | Iris_setosa | {5.0,3.5,1.6,0.6} | Iris_setosa |
1
+ Alaska | 10 | Iris_setosa | {5.1,3.8,1.9,0.4} | Iris_setosa |
1
+ Alaska | 11 | Iris_setosa | {4.8,3.0,1.4,0.3} | Iris_setosa |
1
+ Alaska | 12 | Iris_setosa | {5.1,3.8,1.6,0.2} | Iris_setosa |
1
+ Alaska | 13 | Iris_versicolor | {5.7,2.8,4.5,1.3} | Iris_versicolor |
2
+ Alaska | 14 | Iris_versicolor | {6.3,3.3,4.7,1.6} | Iris_versicolor |
2
+ Alaska | 15 | Iris_versicolor | {4.9,2.4,3.3,1.0} | Iris_versicolor |
2
+ Alaska | 16 | Iris_versicolor | {6.6,2.9,4.6,1.3} | Iris_versicolor |
2
+ Alaska | 17 | Iris_versicolor | {5.2,2.7,3.9,1.4} | Iris_versicolor |
2
+ Alaska | 18 | Iris_versicolor | {5.0,2.0,3.5,1.0} | Iris_versicolor |
2
+ Alaska | 19 | Iris_versicolor | {5.9,3.0,4.2,1.5} | Iris_versicolor |
2
+ Alaska | 20 | Iris_versicolor | {6.0,2.2,4.0,1.0} | Iris_versicolor |
2
+ Alaska | 21 | Iris_versicolor | {6.1,2.9,4.7,1.4} | Iris_versicolor |
2
+ Alaska | 22 | Iris_versicolor | {5.6,2.9,3.6,1.3} | Iris_versicolor |
2
+ Alaska | 23 | Iris_versicolor | {6.7,3.1,4.4,1.4} | Iris_versicolor |
2
+ Alaska | 24 | Iris_versicolor | {5.6,3.0,4.5,1.5} | Iris_versicolor |
2
+ Alaska | 25 | Iris_versicolor | {5.8,2.7,4.1,1.0} | Iris_versicolor |
2
+ Alaska | 26 | Iris_versicolor | {6.2,2.2,4.5,1.5} | Iris_versicolor |
2
+ Alaska | 27 | Iris_versicolor | {5.6,2.5,3.9,1.1} | Iris_versicolor |
2
+ Tennessee | 28 | Iris_setosa | {5.0,3.4,1.5,0.2} | Iris_setosa |
1
+ Tennessee | 29 | Iris_setosa | {4.4,2.9,1.4,0.2} | Iris_setosa |
1
+ Tennessee | 30 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa |
1
+ Tennessee | 31 | Iris_setosa | {5.4,3.7,1.5,0.2} | Iris_setosa |
1
+ Tennessee | 32 | Iris_setosa | {4.8,3.4,1.6,0.2} | Iris_setosa |
1
+ Tennessee | 33 | Iris_setosa | {4.8,3.0,1.4,0.1} | Iris_setosa |
1
+ Tennessee | 34 | Iris_setosa | {4.3,3.0,1.1,0.1} | Iris_setosa |
1
+ Tennessee | 35 | Iris_setosa | {5.8,4.0,1.2,0.2} | Iris_setosa |
1
+ Tennessee | 36 | Iris_setosa | {5.7,4.4,1.5,0.4} | Iris_setosa |
1
+ Tennessee | 37 | Iris_setosa | {5.4,3.9,1.3,0.4} | Iris_setosa |
1
+ Tennessee | 38 | Iris_versicolor | {6.0,2.9,4.5,1.5} | Iris_versicolor |
2
+ Tennessee | 39 | Iris_versicolor | {5.7,2.6,3.5,1.0} | Iris_versicolor |
2
+ Tennessee | 40 | Iris_versicolor | {5.5,2.4,3.8,1.1} | Iris_versicolor |
2
+ Tennessee | 41 | Iris_versicolor | {5.5,2.4,3.7,1.0} | Iris_versicolor |
2
+ Tennessee | 42 | Iris_versicolor | {5.8,2.7,3.9,1.2} | Iris_versicolor |
2
+ Tennessee | 43 | Iris_versicolor | {6.0,2.7,5.1,1.6} | Iris_versicolor |
2
+ Tennessee | 44 | Iris_versicolor | {5.4,3.0,4.5,1.5} | Iris_versicolor |
2
+ Tennessee | 45 | Iris_versicolor | {6.0,3.4,4.5,1.6} | Iris_versicolor |
2
+ Tennessee | 46 | Iris_versicolor | {6.7,3.1,4.7,1.5} | Iris_versicolor |
2
+ Tennessee | 47 | Iris_versicolor | {6.3,2.3,4.4,1.3} | Iris_versicolor |
2
+ Tennessee | 48 | Iris_versicolor | {5.6,3.0,4.1,1.3} | Iris_versicolor |
2
+ Tennessee | 49 | Iris_versicolor | {5.5,2.5,4.0,1.3} | Iris_versicolor |
2
+ Tennessee | 50 | Iris_versicolor | {5.5,2.6,4.4,1.2} | Iris_versicolor |
2
+ Tennessee | 51 | Iris_versicolor | {6.1,3.0,4.6,1.4} | Iris_versicolor |
2
+ Tennessee | 52 | Iris_versicolor | {5.8,2.6,4.0,1.2} | Iris_versicolor |
2
+(52 rows)
+</pre></li>
+</ol>
+<h4>Regression without Mini-Batching</h4>
+<ol type="1">
+<li>Create a dataset with housing prices data. <pre class="example">
+DROP TABLE IF EXISTS lin_housing;
+CREATE TABLE lin_housing (id serial, x numeric[], zipcode int, y float8);
+INSERT INTO lin_housing(id, x, zipcode, y) VALUES
+(1,ARRAY[1,0.00632,18.00,2.310,0,0.5380,6.5750,65.20,4.0900,1,296.0,15.30,396.90,4.98],94016,24.00),
+(2,ARRAY[1,0.02731,0.00,7.070,0,0.4690,6.4210,78.90,4.9671,2,242.0,17.80,396.90,9.14],94016,21.60),
+(3,ARRAY[1,0.02729,0.00,7.070,0,0.4690,7.1850,61.10,4.9671,2,242.0,17.80,392.83,4.03],94016,34.70),
+(4,ARRAY[1,0.03237,0.00,2.180,0,0.4580,6.9980,45.80,6.0622,3,222.0,18.70,394.63,2.94],94016,33.40),
+(5,ARRAY[1,0.06905,0.00,2.180,0,0.4580,7.1470,54.20,6.0622,3,222.0,18.70,396.90,5.33],94016,36.20),
+(6,ARRAY[1,0.02985,0.00,2.180,0,0.4580,6.4300,58.70,6.0622,3,222.0,18.70,394.12,5.21],94016,28.70),
+(7,ARRAY[1,0.08829,12.50,7.870,0,0.5240,6.0120,66.60,5.5605,5,311.0,15.20,395.60,12.43],94016,22.90),
+(8,ARRAY[1,0.14455,12.50,7.870,0,0.5240,6.1720,96.10,5.9505,5,311.0,15.20,396.90,19.15],94016,27.10),
+(9,ARRAY[1,0.21124,12.50,7.870,0,0.5240,5.6310,100.00,6.0821,5,311.0,15.20,386.63,29.93],94016,16.50),
+(10,ARRAY[1,0.17004,12.50,7.870,0,0.5240,6.0040,85.90,6.5921,5,311.0,15.20,386.71,17.10],94016,18.90),
+(11,ARRAY[1,0.22489,12.50,7.870,0,0.5240,6.3770,94.30,6.3467,5,311.0,15.20,392.52,20.45],94016,15.00),
+(12,ARRAY[1,0.11747,12.50,7.870,0,0.5240,6.0090,82.90,6.2267,5,311.0,15.20,396.90,13.27],20001,18.90),
+(13,ARRAY[1,0.09378,12.50,7.870,0,0.5240,5.8890,39.00,5.4509,5,311.0,15.20,390.50,15.71],20001,21.70),
+(14,ARRAY[1,0.62976,0.00,8.140,0,0.5380,5.9490,61.80,4.7075,4,307.0,21.00,396.90,8.26],20001,20.40),
+(15,ARRAY[1,0.63796,0.00,8.140,0,0.5380,6.0960,84.50,4.4619,4,307.0,21.00,380.02,10.26],20001,18.20),
+(16,ARRAY[1,0.62739,0.00,8.140,0,0.5380,5.8340,56.50,4.4986,4,307.0,21.00,395.62,8.47],20001,19.90),
+(17,ARRAY[1,1.05393,0.00,8.140,0,0.5380,5.9350,29.30,4.4986,4,307.0,21.00,386.85,6.58],20001,
23.10),
+(18,ARRAY[1,0.78420,0.00,8.140,0,0.5380,5.9900,81.70,4.2579,4,307.0,21.00,386.75,14.67],20001,17.50),
+(19,ARRAY[1,0.80271,0.00,8.140,0,0.5380,5.4560,36.60,3.7965,4,307.0,21.00,288.99,11.69],20001,20.20),
+(20,ARRAY[1,0.72580,0.00,8.140,0,0.5380,5.7270,69.50,3.7965,4,307.0,21.00,390.95,11.28],20001,18.20);
+</pre></li>
+<li>Now train a regression model using a multilayer perceptron with two hidden
layers of twenty five nodes each: <pre class="example">
+DROP TABLE IF EXISTS mlp_regress, mlp_regress_summary,
mlp_regress_standardization;
+SELECT setseed(0);
+SELECT madlib.mlp_regression(
+ 'lin_housing', -- Source table
+ 'mlp_regress', -- Desination table
+ 'x', -- Input features
+ 'y', -- Dependent variable
+ ARRAY[25,25], -- Number of units per layer
+ 'learning_rate_init=0.001,
+ n_iterations=500,
+ lambda=0.001,
+ tolerance=0', -- Optimizer params
+ 'relu', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE -- Not verbose
+);
+</pre> View the model: <pre class="example">
+\x on
+SELECT * FROM mlp_regress;
+</pre> <pre class="result">
+[ RECORD 1
]--+-------------------------------------------------------------------------------------
+coeff |
{-0.250057620174,0.0630805938982,-0.290635490112,-0.382966162592,-0.212206338909...
+loss | 1.07042781236
+num_iterations | 500
+</pre></li>
+<li>Prediction using the regression model: <pre class="example">
+DROP TABLE IF EXISTS mlp_regress_prediction;
+SELECT madlib.mlp_predict(
+ 'mlp_regress', -- Model table
+ 'lin_housing', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_regress_prediction', -- Output table for predictions
+ 'response' -- Output values, not probabilities
+ );
+</pre> View results: <pre class="example">
+SELECT * FROM lin_housing JOIN mlp_regress_prediction USING (id) ORDER BY id;
+</pre> <pre class="result">
+ id | x
| zipcode | y | estimated_y
+----+----------------------------------------------------------------------------------+---------+------+------------------
+ 1 |
{1,0.00632,18.00,2.310,0,0.5380,6.5750,65.20,4.0900,1,296.0,15.30,396.90,4.98}
| 94016 | 24 | 23.9989087488259
+ 2 |
{1,0.02731,0.00,7.070,0,0.4690,6.4210,78.90,4.9671,2,242.0,17.80,396.90,9.14}
| 94016 | 21.6 | 21.5983177932005
+ 3 |
{1,0.02729,0.00,7.070,0,0.4690,7.1850,61.10,4.9671,2,242.0,17.80,392.83,4.03}
| 94016 | 34.7 | 34.7102398021623
+ 4 |
{1,0.03237,0.00,2.180,0,0.4580,6.9980,45.80,6.0622,3,222.0,18.70,394.63,2.94}
| 94016 | 33.4 | 33.4221257351015
+ 5 |
{1,0.06905,0.00,2.180,0,0.4580,7.1470,54.20,6.0622,3,222.0,18.70,396.90,5.33}
| 94016 | 36.2 | 36.1523886001663
+ 6 |
{1,0.02985,0.00,2.180,0,0.4580,6.4300,58.70,6.0622,3,222.0,18.70,394.12,5.21}
| 94016 | 28.7 | 28.723894783928
+ 7 |
{1,0.08829,12.50,7.870,0,0.5240,6.0120,66.60,5.5605,5,311.0,15.20,395.60,12.43}
| 94016 | 22.9 | 22.6515242795835
+ 8 |
{1,0.14455,12.50,7.870,0,0.5240,6.1720,96.10,5.9505,5,311.0,15.20,396.90,19.15}
| 94016 | 27.1 | 25.7615314879354
+ 9 |
{1,0.21124,12.50,7.870,0,0.5240,5.6310,100.00,6.0821,5,311.0,15.20,386.63,29.93}
| 94016 | 16.5 | 15.7368298351732
+ 10 |
{1,0.17004,12.50,7.870,0,0.5240,6.0040,85.90,6.5921,5,311.0,15.20,386.71,17.10}
| 94016 | 18.9 | 16.8850496141437
+ 11 |
{1,0.22489,12.50,7.870,0,0.5240,6.3770,94.30,6.3467,5,311.0,15.20,392.52,20.45}
| 94016 | 15 | 14.9150416339458
+ 12 |
{1,0.11747,12.50,7.870,0,0.5240,6.0090,82.90,6.2267,5,311.0,15.20,396.90,13.27}
| 20001 | 18.9 | 19.4541629864106
+ 13 |
{1,0.09378,12.50,7.870,0,0.5240,5.8890,39.00,5.4509,5,311.0,15.20,390.50,15.71}
| 20001 | 21.7 | 21.715554997762
+ 14 |
{1,0.62976,0.00,8.140,0,0.5380,5.9490,61.80,4.7075,4,307.0,21.00,396.90,8.26}
| 20001 | 20.4 | 20.3181247234996
+ 15 |
{1,0.63796,0.00,8.140,0,0.5380,6.0960,84.50,4.4619,4,307.0,21.00,380.02,10.26}
| 20001 | 18.2 | 18.5026399122209
+ 16 |
{1,0.62739,0.00,8.140,0,0.5380,5.8340,56.50,4.4986,4,307.0,21.00,395.62,8.47}
| 20001 | 19.9 | 19.9131696333521
+ 17 |
{1,1.05393,0.00,8.140,0,0.5380,5.9350,29.30,4.4986,4,307.0,21.00,386.85,6.58}
| 20001 | 23.1 | 23.1757650468106
+ 18 |
{1,0.78420,0.00,8.140,0,0.5380,5.9900,81.70,4.2579,4,307.0,21.00,386.75,14.67}
| 20001 | 17.5 | 17.2671872543377
+ 19 |
{1,0.80271,0.00,8.140,0,0.5380,5.4560,36.60,3.7965,4,307.0,21.00,288.99,11.69}
| 20001 | 20.2 | 20.1073474558796
+ 20 |
{1,0.72580,0.00,8.140,0,0.5380,5.7270,69.50,3.7965,4,307.0,21.00,390.95,11.28}
| 20001 | 18.2 | 18.2143446340975
+(20 rows)
+</pre> RMS error: <pre class="example">
+SELECT SQRT(SUM(ABS(y-estimated_y))/COUNT(y)) as rms_error FROM lin_housing
+JOIN mlp_regress_prediction USING (id);
+</pre> <pre class="result">
+ rms_error
+------------------+
+ 0.544960829104004
+</pre></li>
+</ol>
+<h4>Regression with Mini-Batching</h4>
+<ol type="1">
+<li>Call min-batch preprocessor using the same data set as above: <pre
class="example">
+DROP TABLE IF EXISTS lin_housing_packed, lin_housing_packed_summary,
lin_housing_packed_standardization;
+SELECT madlib.minibatch_preprocessor('lin_housing', -- Source table
+ 'lin_housing_packed', -- Output table
+ 'y', -- Dependent
variable
+ 'x' -- Independent
variables
+ );
+</pre></li>
+<li>Train regression model with mini-batching <pre class="example">
+DROP TABLE IF EXISTS mlp_regress, mlp_regress_summary,
mlp_regress_standardization;
+SELECT setseed(0);
+SELECT madlib.mlp_regression(
+ 'lin_housing_packed', -- Source table
+ 'mlp_regress', -- Desination table
+ 'independent_varname', -- Hardcode to this, from table lin_housing_packed
+ 'dependent_varname', -- Hardcode to this, from table lin_housing_packed
+ ARRAY[25,25], -- Number of units per layer
+ 'learning_rate_init=0.01,
+ n_iterations=500,
+ lambda=0.001,
+ tolerance=0', -- Optimizer params
+ 'tanh', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE -- Not verbose
+);
+</pre> View model: <pre class="example">
+\x on
+SELECT * FROM mlp_regress;
+</pre> <pre class="result">
+-[ RECORD 1 ]--+-------------------------------------------------------------
+coeff | {0.0395865908810001,-0.164860448878703,-0.132787863194324...
+loss | 0.0442383714892138
+num_iterations | 500
+</pre></li>
+<li>Prediction for regression: <pre class="example">
+DROP TABLE IF EXISTS mlp_regress_prediction;
+SELECT madlib.mlp_predict(
+ 'mlp_regress', -- Model table
+ 'lin_housing', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_regress_prediction', -- Output table for predictions
+ 'response' -- Output values, not probabilities
+ );
+\x off
+SELECT *, ABS(y-estimated_y) as abs_diff FROM lin_housing JOIN
mlp_regress_prediction USING (id) ORDER BY id;
+</pre> <pre class="result">
+ id | x
| zipcode | y | zipcode | estimated_y | abs_diff
+----+----------------------------------------------------------------------------------+---------+------+---------+------------------+--------------------
+ 1 |
{1,0.00632,18.00,2.310,0,0.5380,6.5750,65.20,4.0900,1,296.0,15.30,396.90,4.98}
| 94016 | 24 | 94016 | 23.9714991250013 | 0.0285008749987092
+ 2 |
{1,0.02731,0.00,7.070,0,0.4690,6.4210,78.90,4.9671,2,242.0,17.80,396.90,9.14}
| 94016 | 21.6 | 94016 | 22.3655180133895 | 0.765518013389535
+ 3 |
{1,0.02729,0.00,7.070,0,0.4690,7.1850,61.10,4.9671,2,242.0,17.80,392.83,4.03}
| 94016 | 34.7 | 94016 | 33.8620767428645 | 0.837923257135465
+ 4 |
{1,0.03237,0.00,2.180,0,0.4580,6.9980,45.80,6.0622,3,222.0,18.70,394.63,2.94}
| 94016 | 33.4 | 94016 | 35.3094157686524 | 1.90941576865244
+ 5 |
{1,0.06905,0.00,2.180,0,0.4580,7.1470,54.20,6.0622,3,222.0,18.70,396.90,5.33}
| 94016 | 36.2 | 94016 | 35.0379122731818 | 1.16208772681817
+ 6 |
{1,0.02985,0.00,2.180,0,0.4580,6.4300,58.70,6.0622,3,222.0,18.70,394.12,5.21}
| 94016 | 28.7 | 94016 | 27.5207943492151 | 1.17920565078487
+ 7 |
{1,0.08829,12.50,7.870,0,0.5240,6.0120,66.60,5.5605,5,311.0,15.20,395.60,12.43}
| 94016 | 22.9 | 94016 | 24.9841422781166 | 2.0841422781166
+ 8 |
{1,0.14455,12.50,7.870,0,0.5240,6.1720,96.10,5.9505,5,311.0,15.20,396.90,19.15}
| 94016 | 27.1 | 94016 | 24.5403994064793 | 2.55960059352067
+ 9 |
{1,0.21124,12.50,7.870,0,0.5240,5.6310,100.00,6.0821,5,311.0,15.20,386.63,29.93}
| 94016 | 16.5 | 94016 | 17.2588278443879 | 0.75882784438787
+ 10 |
{1,0.17004,12.50,7.870,0,0.5240,6.0040,85.90,6.5921,5,311.0,15.20,386.71,17.10}
| 94016 | 18.9 | 94016 | 17.0600407532569 | 1.8399592467431
+ 11 |
{1,0.22489,12.50,7.870,0,0.5240,6.3770,94.30,6.3467,5,311.0,15.20,392.52,20.45}
| 94016 | 15 | 94016 | 15.2284207930287 | 0.228420793028732
+ 12 |
{1,0.11747,12.50,7.870,0,0.5240,6.0090,82.90,6.2267,5,311.0,15.20,396.90,13.27}
| 20001 | 18.9 | 20001 | 19.2272848285357 | 0.327284828535671
+ 13 |
{1,0.09378,12.50,7.870,0,0.5240,5.8890,39.00,5.4509,5,311.0,15.20,390.50,15.71}
| 20001 | 21.7 | 20001 | 21.3979318641202 | 0.302068135879811
+ 14 |
{1,0.62976,0.00,8.140,0,0.5380,5.9490,61.80,4.7075,4,307.0,21.00,396.90,8.26}
| 20001 | 20.4 | 20001 | 19.7743403979155 | 0.625659602084532
+ 15 |
{1,0.63796,0.00,8.140,0,0.5380,6.0960,84.50,4.4619,4,307.0,21.00,380.02,10.26}
| 20001 | 18.2 | 20001 | 18.7400800902121 | 0.540080090212125
+ 16 |
{1,0.62739,0.00,8.140,0,0.5380,5.8340,56.50,4.4986,4,307.0,21.00,395.62,8.47}
| 20001 | 19.9 | 20001 | 19.6187933144569 | 0.281206685543061
+ 17 |
{1,1.05393,0.00,8.140,0,0.5380,5.9350,29.30,4.4986,4,307.0,21.00,386.85,6.58}
| 20001 | 23.1 | 20001 | 23.3492239648177 | 0.249223964817737
+ 18 |
{1,0.78420,0.00,8.140,0,0.5380,5.9900,81.70,4.2579,4,307.0,21.00,386.75,14.67}
| 20001 | 17.5 | 20001 | 17.0806608347814 | 0.419339165218577
+ 19 |
{1,0.80271,0.00,8.140,0,0.5380,5.4560,36.60,3.7965,4,307.0,21.00,288.99,11.69}
| 20001 | 20.2 | 20001 | 20.1559086626409 | 0.044091337359113
+ 20 |
{1,0.72580,0.00,8.140,0,0.5380,5.7270,69.50,3.7965,4,307.0,21.00,390.95,11.28}
| 20001 | 18.2 | 20001 | 18.6980897920022 | 0.498089792002183
+(20 rows)
+</pre> RMS error: <pre class="example">
+SELECT SQRT(SUM(ABS(y-estimated_y))/COUNT(y)) as rms_error FROM lin_housing
+JOIN mlp_regress_prediction USING (id);
+</pre> <pre class="result">
+ rms_error
+-------------------+
+ 0.912158035902468
+(1 row)
+</pre></li>
+</ol>
+<h4>Regression with Grouping and Mini-Batching</h4>
+<ol type="1">
+<li>To use grouping and mini-batching, we must first re-run the preprocessor
and specify grouping: <pre class="example">
+DROP TABLE IF EXISTS lin_housing_packed, lin_housing_packed_summary,
lin_housing_packed_standardization;
+SELECT madlib.minibatch_preprocessor('lin_housing', -- Source table
+ 'lin_housing_packed', -- Output table
+ 'y', -- Dependent
variable
+ 'x', -- Independent
variables
+ 'zipcode' -- Group by zipcode
+ );
+</pre></li>
+<li>Train regression model and group the training data by zipcode to learn a
different model for each zipcode. <pre class="example">
+DROP TABLE IF EXISTS mlp_regress_group, mlp_regress_group_summary,
mlp_regress_group_standardization;
+-- Set seed so results are reproducible
+SELECT setseed(0);
+SELECT madlib.mlp_regression(
+ 'lin_housing_packed', -- Source table
+ 'mlp_regress_group', -- Desination table
+ 'independent_varname', -- Input features
+ 'dependent_varname', -- Dependent variable
+ ARRAY[25,25], -- Number of units per layer
+ 'learning_rate_init=0.001,
+ n_iterations=500,
+ lambda=0.001,
+ tolerance=0', -- Optimizer params
+ 'relu', -- Activation function
+ NULL, -- Default weight (1)
+ FALSE, -- No warm start
+ FALSE, -- Not verbose
+ 'zipcode' -- Grouping column
+);
+</pre> View regression model with grouping: <pre class="example">
+\x on
+SELECT * FROM mlp_regress_group;
+</pre> <pre class="result">
+-[ RECORD 1
]--+------------------------------------------------------------------------------------
+zipcode | 200001
+coeff |
{-0.193588485849,0.063428493184,-0.30440608833,-0.355695802004,-0.175942716164
...
+loss | 0.0904009145541
+num_iterations | 500
+-[ RECORD 2
]--+------------------------------------------------------------------------------------
+zipcode | 94016
+coeff |
{-0.18965351506,0.0633650963628,-0.302423579808,-0.334367637252,-0.230043593847
...
+loss | 1.04772100552
+num_iterations | 500
+</pre></li>
+<li>Prediction using the regression model for each group based on the zipcode:
<pre class="example">
+DROP TABLE IF EXISTS mlp_regress_prediction;
+SELECT madlib.mlp_predict(
+ 'mlp_regress_group', -- Model table
+ 'lin_housing', -- Test data table
+ 'id', -- Id column in test table
+ 'mlp_regress_prediction', -- Output table for predictions
+ 'response' -- Output values, not probabilities
+ );
+\x off
+SELECT * FROM lin_housing JOIN mlp_regress_prediction USING (zipcode, id)
ORDER BY zipcode, id;
+</pre> <pre class="result">
+ zipcode | id | x
| y | estimated_y
+---------+----+----------------------------------------------------------------------------------+------+------------------
+ 20001 | 12 |
{1,0.11747,12.50,7.870,0,0.5240,6.0090,82.90,6.2267,5,311.0,15.20,396.90,13.27}
| 18.9 | 19.2272848285357
+ 20001 | 13 |
{1,0.09378,12.50,7.870,0,0.5240,5.8890,39.00,5.4509,5,311.0,15.20,390.50,15.71}
| 21.7 | 21.3979318641202
+ 20001 | 14 |
{1,0.62976,0.00,8.140,0,0.5380,5.9490,61.80,4.7075,4,307.0,21.00,396.90,8.26}
| 20.4 | 19.7743403979155
+ 20001 | 15 |
{1,0.63796,0.00,8.140,0,0.5380,6.0960,84.50,4.4619,4,307.0,21.00,380.02,10.26}
| 18.2 | 18.7400800902121
+ 20001 | 16 |
{1,0.62739,0.00,8.140,0,0.5380,5.8340,56.50,4.4986,4,307.0,21.00,395.62,8.47}
| 19.9 | 19.6187933144569
+ 20001 | 17 |
{1,1.05393,0.00,8.140,0,0.5380,5.9350,29.30,4.4986,4,307.0,21.00,386.85,6.58}
| 23.1 | 23.3492239648177
+ 20001 | 18 |
{1,0.78420,0.00,8.140,0,0.5380,5.9900,81.70,4.2579,4,307.0,21.00,386.75,14.67}
| 17.5 | 17.0806608347814
+ 20001 | 19 |
{1,0.80271,0.00,8.140,0,0.5380,5.4560,36.60,3.7965,4,307.0,21.00,288.99,11.69}
| 20.2 | 20.1559086626409
+ 20001 | 20 |
{1,0.72580,0.00,8.140,0,0.5380,5.7270,69.50,3.7965,4,307.0,21.00,390.95,11.28}
| 18.2 | 18.6980897920022
+ 94016 | 1 |
{1,0.00632,18.00,2.310,0,0.5380,6.5750,65.20,4.0900,1,296.0,15.30,396.90,4.98}
| 24 | 23.9714991250013
+ 94016 | 2 |
{1,0.02731,0.00,7.070,0,0.4690,6.4210,78.90,4.9671,2,242.0,17.80,396.90,9.14}
| 21.6 | 22.3655180133895
+ 94016 | 3 |
{1,0.02729,0.00,7.070,0,0.4690,7.1850,61.10,4.9671,2,242.0,17.80,392.83,4.03}
| 34.7 | 33.8620767428645
+ 94016 | 4 |
{1,0.03237,0.00,2.180,0,0.4580,6.9980,45.80,6.0622,3,222.0,18.70,394.63,2.94}
| 33.4 | 35.3094157686524
+ 94016 | 5 |
{1,0.06905,0.00,2.180,0,0.4580,7.1470,54.20,6.0622,3,222.0,18.70,396.90,5.33}
| 36.2 | 35.0379122731818
+ 94016 | 6 |
{1,0.02985,0.00,2.180,0,0.4580,6.4300,58.70,6.0622,3,222.0,18.70,394.12,5.21}
| 28.7 | 27.5207943492151
+ 94016 | 7 |
{1,0.08829,12.50,7.870,0,0.5240,6.0120,66.60,5.5605,5,311.0,15.20,395.60,12.43}
| 22.9 | 24.9841422781166
+ 94016 | 8 |
{1,0.14455,12.50,7.870,0,0.5240,6.1720,96.10,5.9505,5,311.0,15.20,396.90,19.15}
| 27.1 | 24.5403994064793
+ 94016 | 9 |
{1,0.21124,12.50,7.870,0,0.5240,5.6310,100.00,6.0821,5,311.0,15.20,386.63,29.93}
| 16.5 | 17.2588278443879
+ 94016 | 10 |
{1,0.17004,12.50,7.870,0,0.5240,6.0040,85.90,6.5921,5,311.0,15.20,386.71,17.10}
| 18.9 | 17.0600407532569
+ 94016 | 11 |
{1,0.22489,12.50,7.870,0,0.5240,6.3770,94.30,6.3467,5,311.0,15.20,392.52,20.45}
| 15 | 15.2284207930287
+(20 rows)
+</pre> Note that the results you get for all examples may vary with the
database you are using.</li>
+</ol>
+<p><a class="anchor" id="background"></a></p><dl class="section
user"><dt>Technical Background</dt><dd></dd></dl>
+<p>To train a neural net, the loss function is minimized using stochastic
gradient descent. In the case of classification, the loss function is cross
entropy. For regression, mean square error is used. Weights in the neural net
are updated via the backpropogation process, which uses dynamic programming to
compute the partial derivative of each weight with respect to the overall loss.
This partial derivative incorporates the activation function used, which
requires that the activation function be differentiable.</p>
+<p>For an overview of multilayer perceptrons, see [1].</p>
+<p>For details on backpropogation, see [2].</p>
+<p><a class="anchor" id="literature"></a></p><dl class="section
user"><dt>Literature</dt><dd></dd></dl>
+<p><a class="anchor" id="mlp-lit-1"></a>[1] <a
href="https://en.wikipedia.org/wiki/Multilayer_perceptron";>https://en.wikipedia.org/wiki/Multilayer_perceptron</a></p>
+<p>[2] Yu Hen Hu. "Lecture 11. MLP (III): Back-Propagation." University of
Wisconsin Madison: Computer-Aided Engineering. Web. 12 July 2017, <a
href="http://homepages.cae.wisc.edu/~ece539/videocourse/notes/pdf/lec%2011%20MLP%20(3)%20BP.pdf">http://homepages.cae.wisc.edu/~ece539/videocourse/notes/pdf/lec%2011%20MLP%20(3)%20BP.pdf</a></p>
+<p>[3] "Neural Networks for Machine Learning", Lectures 6a and 6b on
mini-batch gradient descent, Geoffrey Hinton with Nitish Srivastava and Kevin
Swersky, <a
href="http://www.cs.toronto.edu/~tijmen/csc321/slides/lecture_slides_lec6.pdf";>http://www.cs.toronto.edu/~tijmen/csc321/slides/lecture_slides_lec6.pdf</a></p>
+<p><a class="anchor" id="related"></a></p><dl class="section user"><dt>Related
Topics</dt><dd></dd></dl>
+<p>File <a class="el" href="mlp_8sql__in.html" title="SQL functions for
multilayer perceptron. ">mlp.sql_in</a> documenting the training function </p>
+</div><!-- contents -->
+</div><!-- doc-content -->
+<!-- start footer part -->
+<div id="nav-path" class="navpath"><!-- id is needed for treeview function! -->
+ <ul>
+ <li class="footer">Generated on Wed May 2 2018 13:00:11 for MADlib by
+ <a href="http://www.doxygen.org/index.html";>
+ <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.13 </li>
+ </ul>
+</div>
+</body>
+</html>
