http://git-wip-us.apache.org/repos/asf/madlib-site/blob/e283664c/docs/v1.14/group__grp__pca__project.html
----------------------------------------------------------------------
diff --git a/docs/v1.14/group__grp__pca__project.html 
b/docs/v1.14/group__grp__pca__project.html
new file mode 100644
index 0000000..d8b6207
--- /dev/null
+++ b/docs/v1.14/group__grp__pca__project.html
@@ -0,0 +1,499 @@
+<!-- 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: Principal Component Projection</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__pca__project.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">Principal Component Projection<div class="ingroups"><a 
class="el" href="group__grp__unsupervised.html">Unsupervised Learning</a> 
&raquo; <a class="el" href="group__grp__pca.html">Dimensionality 
Reduction</a></div></div>  </div>
+</div><!--header-->
+<div class="contents">
+<div class="toc"><b>Contents</b> <ul>
+<li class="level1">
+<a href="#project">Projection Function</a> </li>
+<li class="level1">
+<a href="#examples">Examples</a> </li>
+<li class="level1">
+<a href="#notes">Notes</a> </li>
+<li class="level1">
+<a href="#background_project">Technical Background</a> </li>
+<li class="level1">
+<a href="#related">Related Topics</a> </li>
+</ul>
+</div><p>Principal component projection is a mathematical procedure that 
projects high dimensional data onto a lower dimensional space. This lower 
dimensional space is defined by the \( k \) principal components with the 
highest variance in the training data.</p>
+<p>More details on the mathematics of PCA can be found in <a class="el" 
href="group__grp__pca__train.html">Principal Component Analysis</a> and some 
details about principal component projection calculations can be found in the 
<a class="el" href="group__grp__pca__project.html#background_project">Technical 
Background</a>.</p>
+<p><a class="anchor" id="project"></a></p><dl class="section 
user"><dt>Projection Function</dt><dd>The projection functions are slightly 
different for dense and sparse matrices. For dense matrices: <pre 
class="syntax">
+madlib.pca_project( source_table,
+                    pc_table,
+                    out_table,
+                    row_id,
+                    residual_table,
+                    result_summary_table
+                  )
+</pre> For sparse matrices: <pre class="syntax">
+madlib.pca_sparse_project( source_table,
+                           pc_table,
+                           out_table,
+                           row_id,
+                           col_id,              -- Sparse matrices only
+                           val_id,              -- Sparse matrices only
+                           row_dim,             -- Sparse matrices only
+                           col_dim,             -- Sparse matrices only
+                           residual_table,
+                           result_summary_table
+                         )
+</pre></dd></dl>
+<p><b>Arguments</b> </p><dl class="arglist">
+<dt>source_table </dt>
+<dd><p class="startdd">TEXT. Source table name. Identical to <a class="el" 
href="pca_8sql__in.html#a31abf88e67a446a4f789764aa2c61e85">pca_train</a>, the 
input data matrix should have \( N \) rows and \( M \) columns, where \( N \) 
is the number of data points, and \( M \) is the number of features for each 
data point.</p>
+<p>The input table for <em> pca_project </em> is expected to be in the one of 
the two standard MADlib dense matrix formats, and the sparse input table for 
<em> pca_sparse_project </em> should be in the standard MADlib sparse matrix 
format. These formats are described in the documentation for <a class="el" 
href="group__grp__pca__train.html">Principal Component Analysis</a>.</p>
+<p class="enddd"></p>
+</dd>
+<dt>pc_table </dt>
+<dd><p class="startdd">TEXT. Table name for the table containing principal 
components. </p>
+<p class="enddd"></p>
+</dd>
+<dt>out_table </dt>
+<dd><p class="startdd">TEXT. Name of the table that will contain the 
low-dimensional representation of the input data.</p>
+<p>The <em>out_table</em> encodes a dense matrix with the projection onto the 
principal components. The table has the following columns:</p>
+<table class="output">
+<tr>
+<th>row_id </th><td>Row id of the output matrix.  </td></tr>
+<tr>
+<th>row_vec </th><td>A vector containing elements in the row of the matrix.  
</td></tr>
+</table>
+<p class="enddd"></p>
+</dd>
+<dt>row_id </dt>
+<dd><p class="startdd">TEXT. Column name containing the row IDs in the input 
source table. The column should be of type INT (or a type that can be cast to 
INT) and should only contain values between 1 and <em>N</em>. For dense matrix 
format, it should contain all continguous integers from 1 to <em>N</em> 
describing the full matrix.</p>
+<p class="enddd"></p>
+</dd>
+<dt>col_id </dt>
+<dd><p class="startdd">TEXT. Column name containing the column IDs in sparse 
matrix representation. The column should be of type INT (or a type that can be 
cast to INT) and should only contain values between 1 and <em>M</em>. <em>This 
parameter applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>val_id </dt>
+<dd><p class="startdd">TEXT. Name of 'val_id' column in sparse matrix 
representation defining the values of the nonzero entries. <em>This parameter 
applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>row_dim </dt>
+<dd><p class="startdd">INTEGER. The actual number of rows in the matrix. That 
is, if the matrix was transformed into dense format, this is the number of rows 
it would have. <em>This parameter applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>col_dim </dt>
+<dd><p class="startdd">INTEGER. The actual number of columns in the matrix. 
That is, if the matrix was transformed into dense format, this is the number of 
columns it would have. <em>This parameter applies to sparse matrices 
only.</em></p>
+<dl class="section note"><dt>Note</dt><dd>The parameters 'row_dim' and 
'col_dim' could actually be inferred from the sparse matrix representation, so 
they will be removed in the future. For now they are maintained for backward 
compatability so you must enter them. Making 'row_dim' or 'col_dim' larger than 
the actual matrix has the effect of padding it with zeros, which is probably 
not useful.</dd></dl>
+</dd>
+<dt>residual_table (optional) </dt>
+<dd><p class="startdd">TEXT, default: NULL. Name of the optional residual 
table.</p>
+<p>The <em>residual_table</em> encodes a dense residual matrix. The table has 
the following columns:</p>
+<table class="output">
+<tr>
+<th>row_id </th><td>Row id of the output matrix.  </td></tr>
+<tr>
+<th>row_vec </th><td>A vector containing elements in the row of the residual 
matrix.  </td></tr>
+</table>
+<p class="enddd"></p>
+</dd>
+<dt>result_summary_table (optional) </dt>
+<dd><p class="startdd">TEXT, default: NULL. Name of the optional summary 
table.</p>
+<p class="enddd">The <em>result_summary_table</em> contains information about 
the performance time of the PCA projection. The table has the following 
columns: </p><table class="output">
+<tr>
+<th>exec_time </th><td>Elapsed time (ms) for execution of the function.  
</td></tr>
+<tr>
+<th>residual_norm </th><td>Absolute error of the residuals.  </td></tr>
+<tr>
+<th>relative_residual_norm </th><td>Relative error of the residuals.  
</td></tr>
+</table>
+</dd>
+</dl>
+<p><a class="anchor" id="examples"></a></p><dl class="section 
user"><dt>Examples</dt><dd><ol type="1">
+<li>View online help for the PCA projection function: <pre class="example">
+SELECT madlib.pca_project();
+</pre></li>
+<li>Create sample data in dense matrix form: <pre class="example">
+DROP TABLE IF EXISTS mat;
+CREATE TABLE mat (id integer,
+                  row_vec double precision[]
+                  );
+INSERT INTO mat VALUES
+(1, '{1,2,3}'),
+(2, '{2,1,2}'),
+(3, '{3,2,1}');
+</pre></li>
+<li>Run the PCA function for a specified number of principal components and 
view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table, result_table_mean;
+SELECT madlib.pca_train('mat',             -- Source table
+                        'result_table',    -- Output table
+                        'id',              -- Row id of source table
+                         2);               -- Number of principal components
+SELECT * FROM result_table ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |                     principal_components                     |      
std_dev      |    proportion     
+--------+--------------------------------------------------------------+-------------------+-------------------
+      1 | {0.707106781186547,-6.93889390390723e-18,-0.707106781186548} |  
1.41421356237309 | 0.857142857142244
+      2 | {0,1,0}                                                      | 
0.577350269189626 | 0.142857142857041
+(2 rows)
+</pre></li>
+<li>Project the original data to a lower dimensional representation and view 
the result of the projection: <pre class="example">
+DROP TABLE IF EXISTS residual_table, result_summary_table, out_table;
+SELECT madlib.pca_project( 'mat',
+                           'result_table',
+                           'out_table',
+                           'id',
+                           'residual_table',
+                           'result_summary_table'
+                           );
+SELECT * FROM out_table ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |               row_vec                
+--------+--------------------------------------
+      1 | {-1.41421356237309,-0.33333333333}
+      2 | {2.77555756157677e-17,0.66666666667}
+      3 | {1.41421356237309,-0.33333333333}
+(3 rows)
+</pre> Check the error in the projection: <pre class="example">
+SELECT * FROM result_summary_table;
+</pre> <pre class="result">
+   exec_time   |   residual_norm   | relative_residual_norm 
+---------------+-------------------+------------------------
+ 331.792116165 | 5.89383520611e-16 |      9.68940539229e-17
+(1 row)
+</pre> Check the residuals: <pre class="example">
+SELECT * FROM residual_table ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |                              row_vec                               
+--------+--------------------------------------------------------------------
+      1 | {-2.22044604925031e-16,-1.11022302462516e-16,3.33066907387547e-16}
+      2 | {-1.12243865646685e-18,0,4.7381731349413e-17}
+      3 | {2.22044604925031e-16,1.11022302462516e-16,-3.33066907387547e-16}
+(3 rows)
+</pre></li>
+<li>Now we use grouping in dense form to learn different models for different 
groups. First, we create sample data in dense matrix form with a grouping 
column. Note we actually have different matrix sizes for the different groups, 
which is allowed for dense: <pre class="example">
+DROP TABLE IF EXISTS mat_group;
+CREATE TABLE mat_group (
+    id integer,
+    row_vec double precision[],
+    matrix_id integer
+);
+INSERT INTO mat_group VALUES
+(1, '{1,2,3}', 1),
+(2, '{2,1,2}', 1),
+(3, '{3,2,1}', 1),
+(4, '{1,2,3,4,5}', 2),
+(5, '{2,5,2,4,1}', 2),
+(6, '{5,4,3,2,1}', 2);
+</pre></li>
+<li>Run the PCA function with grouping for a specified proportion of variance 
and view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table_group, result_table_group_mean;
+SELECT madlib.pca_train('mat_group',             -- Source table
+                        'result_table_group',    -- Output table
+                        'id',                    -- Row id of source table
+                         0.8,                    -- Proportion of variance
+                        'matrix_id');            -- Grouping column
+SELECT * FROM result_table_group ORDER BY matrix_id, row_id;
+</pre> <pre class="result">
+ row_id |                                      principal_components            
                          |     std_dev     |    proportion     | matrix_id 
+--------+------------------------------------------------------------------------------------------------+-----------------+-------------------+-----------
+      1 | {0.707106781186548,0,-0.707106781186547}                             
                          | 1.4142135623731 | 0.857142857142245 |         1
+      1 | 
{-0.555378486712784,-0.388303582074091,0.0442457354870796,0.255566375612852,0.688115693174023}
 | 3.2315220311722 | 0.764102534485173 |         2
+      2 | 
{0.587384101786277,-0.485138064894743,0.311532046315153,-0.449458074050715,0.347212037159181}
  |  1.795531127192 | 0.235897465516047 |         2
+(3 rows)
+</pre></li>
+<li>Run the PCA projection on subsets of an input table based on grouping 
columns. Note that the parameter 'pc_table' used for projection must be 
generated in training using the same grouping columns. <pre class="example">
+DROP TABLE IF EXISTS mat_group_projected;
+SELECT madlib.pca_project('mat_group',
+                          'result_table_group',
+                          'mat_group_projected',
+                          'id');
+SELECT * FROM mat_group_projected ORDER BY matrix_id, row_id;
+</pre> <pre class="result">
+ row_id |                row_vec                | matrix_id 
+--------+---------------------------------------+-----------
+      1 | {1.4142135623731}                     |         1
+      2 | {7.40148683087139e-17}                |         1
+      3 | {-1.4142135623731}                    |         1
+      4 | {-3.59290479201926,0.559694003674779} |         2
+      5 | {0.924092949098971,-2.00871628417505} |         2
+      6 | {2.66881184290186,1.44902228049511}   |         2
+(6 rows)
+</pre></li>
+<li>Now let's look at sparse matrices. Create sample data in sparse matrix 
form: <pre class="example">
+DROP TABLE IF EXISTS mat_sparse;
+CREATE TABLE mat_sparse (
+    row_id integer,
+    col_id integer,
+    value double precision
+);
+INSERT INTO mat_sparse VALUES
+(1, 1, 1.0),
+(2, 2, 2.0),
+(3, 3, 3.0),
+(4, 4, 4.0),
+(1, 5, 5.0),
+(2, 4, 6.0),
+(3, 2, 7.0),
+(4, 3, 8.0);
+</pre> As an aside, this is what the sparse matrix above looks like when put 
in dense form: <pre class="example">
+DROP TABLE IF EXISTS mat_dense;
+SELECT madlib.matrix_densify('mat_sparse', 
+                            'row=row_id, col=col_id, val=value', 
+                            'mat_dense');
+SELECT * FROM mat_dense ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |    value    
+--------+-------------
+      1 | {1,0,0,0,5}
+      2 | {0,2,0,6,0}
+      3 | {0,7,3,0,0}
+      4 | {0,0,8,4,0}
+(4 rows)
+</pre></li>
+<li>Run the PCA sparse function for a specified number of principal components 
and view the results: <pre class="example">DROP TABLE IF EXISTS result_table, 
result_table_mean;
+SELECT madlib.pca_sparse_train( 'mat_sparse',       -- Source table
+                                'result_table',     -- Output table
+                                'row_id',           -- Row id of source table
+                                'col_id',           -- Column id of source 
table
+                                'value',            -- Value of matrix at 
row_id, col_id
+                                4,                  -- Actual number of rows 
in the matrix
+                                5,                  -- Actual number of 
columns in the matrix
+                                3);                 -- Number of principal 
components                            
+SELECT * FROM result_table ORDER BY row_id;
+</pre> Result (with principal components truncated for readability): <pre 
class="result">
+ row_id |         principal_components                 |     std_dev      |    
proportion     
+--------+----------------------------------------------+------------------+-------------------
+      1 | {-0.0876046030186158,-0.0968983772909994,... | 4.21362803829554 | 
0.436590030617467
+      2 | {-0.0647272661608605,0.877639526308692,...   | 3.68408023747461 | 
0.333748701544697
+      3 | {-0.0780380267884855,0.177956517174911,...   | 3.05606908060098 | 
0.229661267837836
+(3 rows)
+</pre></li>
+<li>Project the original sparse data to low-dimensional representation: <pre 
class="example">
+DROP TABLE IF EXISTS mat_sparse_out;
+SELECT madlib.pca_sparse_project(
+                    'mat_sparse',
+                    'result_table',
+                    'mat_sparse_out',
+                    'row_id',
+                    'col_id',
+                    'value',
+                    4,
+                    5
+                    );
+SELECT * FROM mat_sparse_out ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |                         row_vec                         
+--------+---------------------------------------------------------
+      1 | {4.66617015032369,-2.63552220635847,2.1865220849604}
+      2 | {0.228360685652383,-1.21616275892926,-4.46864627611561}
+      3 | {0.672067460100428,5.45249627172823,0.56445525585642}
+      4 | {-5.5665982960765,-1.6008113064405,1.71766893529879}
+(4 rows)
+</pre></li>
+<li>Now we use grouping in sparse form to learn different models for different 
groups. First, we create sample data in sparse matrix form with a grouping 
column: <pre class="example">
+DROP TABLE IF EXISTS mat_sparse_group;
+CREATE TABLE mat_sparse_group (
+    row_id integer,
+    col_id integer,
+    value double precision,
+    matrix_id integer);
+INSERT INTO mat_sparse_group VALUES
+(1, 1, 1.0, 1),
+(2, 2, 2.0, 1),
+(3, 3, 3.0, 1),
+(4, 4, 4.0, 1),
+(1, 5, 5.0, 1),
+(2, 4, 6.0, 2),
+(3, 2, 7.0, 2),
+(4, 3, 8.0, 2);
+</pre></li>
+<li>Run the PCA function with grouping for a specified proportion of variance 
and view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table_group, result_table_group_mean;
+SELECT madlib.pca_sparse_train( 'mat_sparse_group',       -- Source table
+                                'result_table_group',     -- Output table
+                                'row_id',           -- Row id of source table
+                                'col_id',           -- Column id of source 
table
+                                'value',            -- Value of matrix at 
row_id, col_id
+                                4,                 -- Actual number of rows in 
the matrix
+                                5,                 -- Actual number of columns 
in the matrix
+                                0.8,                 -- Proportion of variance
+                                'matrix_id');
+SELECT * FROM result_table_group ORDER BY matrix_id, row_id;
+</pre> Result (with principal components truncated for readability): <pre 
class="result">
+ row_id |           principal_components             |     std_dev      |    
proportion     | matrix_id 
+--------+--------------------------------------------+------------------+-------------------+-----------
+      1 | {-0.17805696611353,0.0681313257646983,...  | 2.73659933165925 | 
0.544652792875481 |         1
+      2 | {-0.0492086814863993,0.149371585357526,... | 2.06058314533194 | 
0.308800210823714 |         1
+      1 | {0,-0.479486114660443,...                  | 4.40325305087975 | 
0.520500333693473 |         2
+      2 | {0,0.689230898585949,...                   |  3.7435566458567 | 
0.376220573442628 |         2
+(4 rows)
+</pre></li>
+<li>Projection in sparse format with grouping: <pre class="example">
+DROP TABLE IF EXISTS mat_sparse_group_projected;
+SELECT madlib.pca_sparse_project(
+    'mat_sparse_group',
+    'result_table_group',
+    'mat_sparse_group_projected',
+    'row_id',
+    'col_id',
+    'value',
+    4,
+    5
+    );
+SELECT * FROM mat_sparse_group_projected ORDER BY matrix_id, row_id;
+</pre> <pre class="result">
+ row_id |                 row_vec                 | matrix_id 
+--------+-----------------------------------------+-----------
+      1 | {-4.00039298524261,-0.626820612715982}  |         1
+      2 | {0.765350785238575,0.951348276645455}   |         1
+      3 | {1.04951017256904,2.22388180170356}     |         1
+      4 | {2.185532027435,-2.54840946563303}      |         1
+      1 | {-0.627846810195469,-0.685031603549092} |         2
+      2 | {-1.64754249747757,-4.7662114622896}    |         2
+      3 | {-3.98424961281857,4.13958468655255}    |         2
+      4 | {6.25963892049161,1.31165837928614}     |         2
+(8 rows)
+</pre></li>
+</ol>
+</dd></dl>
+<p><a class="anchor" id="notes"></a></p><dl class="section 
user"><dt>Notes</dt><dd><ul>
+<li>This function is intended to operate on the principal component tables 
generated by <em> pca_train </em> or <em> pca_sparse_train</em>. The MADlib PCA 
functions generate a table containing the column-means in addition to a table 
containing the principal components. If this table is not found by the MADlib 
projection function, it will trigger an error. As long the principal component 
tables are created with MADlib functions, then the column-means table will be 
automatically found by the MADlib projection functions.</li>
+<li>Because of the centering step in PCA projection (see "Technical 
Background"), sparse matrices almost always become dense during the projection 
process. Thus, this implementation automatically densifies sparse matrix input, 
and there should be no expected performance improvement in using sparse matrix 
input over dense matrix input.</li>
+<li>Table names can be optionally schema qualified (current_schemas() is 
searched if a schema name is not provided) and all table and column names 
should follow case-sensitivity and quoting rules per the database. (For 
instance, 'mytable' and 'MyTable' both resolve to the same entity, i.e. 
'mytable'. If mixed-case or multi-byte characters are desired for entity names 
then the string should be double-quoted; in this case the input would be 
'"MyTable"').</li>
+<li>If the input table for pca_project (pca_sparse_project) contains grouping 
columns, the same grouping columns must be used in the training function used 
to generate the principal components too.</li>
+</ul>
+</dd></dl>
+<p><a class="anchor" id="background_project"></a></p><dl class="section 
user"><dt>Technical Background</dt><dd></dd></dl>
+<p>Given a table containing some principal components \( \boldsymbol P \) and 
some input data \( \boldsymbol X \), the low-dimensional representation \( 
{\boldsymbol X}&#39; \) is computed as </p><p class="formulaDsp">
+\begin{align*} {\boldsymbol {\hat{X}}} &amp; = {\boldsymbol X} - \vec{e} 
\hat{x}^T \\ {\boldsymbol X}&#39; &amp; = {\boldsymbol {\hat {X}}} {\boldsymbol 
P}. \end{align*}
+</p>
+<p> where \(\hat{x} \) is the column means of \( \boldsymbol X \) and \( 
\vec{e} \) is the vector of all ones. This step is equivalent to centering the 
data around the origin.</p>
+<p>The residual table \( \boldsymbol R \) is a measure of how well the 
low-dimensional representation approximates the true input data, and is 
computed as </p><p class="formulaDsp">
+\[ {\boldsymbol R} = {\boldsymbol {\hat{X}}} - {\boldsymbol X}&#39; 
{\boldsymbol P}^T. \]
+</p>
+<p> A residual matrix with entries mostly close to zero indicates a good 
representation.</p>
+<p>The residual norm \( r \) is simply </p><p class="formulaDsp">
+\[ r = \|{\boldsymbol R}\|_F \]
+</p>
+<p> where \( \|\cdot\|_F \) is the Frobenius norm. The relative residual norm 
\( r&#39; \) is </p><p class="formulaDsp">
+\[ r&#39; = \frac{ \|{\boldsymbol R}\|_F }{\|{\boldsymbol X}\|_F } \]
+</p>
+<p><a class="anchor" id="related"></a></p><dl class="section user"><dt>Related 
Topics</dt><dd>File <a class="el" href="pca__project_8sql__in.html" 
title="Principal Component Analysis Projection. ">pca_project.sql_in</a> 
documenting the SQL functions</dd></dl>
+<p><a class="el" href="group__grp__pca__train.html">Principal Component 
Analysis</a> </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:12 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>

http://git-wip-us.apache.org/repos/asf/madlib-site/blob/e283664c/docs/v1.14/group__grp__pca__train.html
----------------------------------------------------------------------
diff --git a/docs/v1.14/group__grp__pca__train.html 
b/docs/v1.14/group__grp__pca__train.html
new file mode 100644
index 0000000..c713c5f
--- /dev/null
+++ b/docs/v1.14/group__grp__pca__train.html
@@ -0,0 +1,456 @@
+<!-- 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: Principal Component Analysis</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__pca__train.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">Principal Component Analysis<div class="ingroups"><a 
class="el" href="group__grp__unsupervised.html">Unsupervised Learning</a> 
&raquo; <a class="el" href="group__grp__pca.html">Dimensionality 
Reduction</a></div></div>  </div>
+</div><!--header-->
+<div class="contents">
+<div class="toc"><b>Contents</b> <ul>
+<li class="level1">
+<a href="#train">Training Function</a> </li>
+<li class="level1">
+<a href="#examples">Examples</a> </li>
+<li class="level1">
+<a href="#notes">Notes</a> </li>
+<li class="level1">
+<a href="#background_pca">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>Principal component analysis (PCA) is a mathematical procedure that 
uses an orthogonal transformation to convert a set of observations of possibly 
correlated variables into a set of values of linearly uncorrelated variables 
called principal components. This transformation is defined in such a way that 
the first principal component has the largest possible variance (i.e., accounts 
for as much of the variability in the data as possible), and each succeeding 
component in turn has the highest variance possible under the constraint that 
it be orthogonal to (i.e., uncorrelated with) the preceding components.</p>
+<p>See the <a class="el" 
href="group__grp__pca__train.html#background_pca">Technical Background</a> for 
an introduction to principal component analysis.</p>
+<p><a class="anchor" id="train"></a></p><dl class="section user"><dt>Training 
Function</dt><dd>The training functions are slightly different for dense and 
sparse matrices. For dense matrices: <pre class="syntax">
+pca_train( source_table,
+           out_table,
+           row_id,
+           components_param,
+           grouping_cols,
+           lanczos_iter,
+           use_correlation,
+           result_summary_table
+         )
+</pre> For sparse matrices: <pre class="syntax">
+pca_sparse_train( source_table,
+                  out_table,
+                  row_id,
+                  col_id,           -- Sparse matrices only
+                  val_id,           -- Sparse matrices only
+                  row_dim,          -- Sparse matrices only
+                  col_dim,          -- Sparse matrices only
+                  components_param,
+                  grouping_cols,
+                  lanczos_iter,
+                  use_correlation,
+                  result_summary_table
+                )
+</pre></dd></dl>
+<p><b>Arguments</b> </p><dl class="arglist">
+<dt>source_table </dt>
+<dd><p class="startdd">TEXT. Name of the input table containing the data for 
PCA training. The input data matrix should have \( N \) rows and \( M \) 
columns, where \( N \) is the number of data points, and \( M \) is the number 
of features for each data point.</p>
+<p>A dense input table is expected to be in the one of the two standard MADlib 
dense matrix formats, and a sparse input table should be in the standard MADlib 
sparse matrix format.</p>
+<p>The two standard MADlib dense matrix formats are: </p><pre>{TABLE|VIEW} 
<em>source_table</em> (
+    <em>row_id</em> INTEGER,
+    <em>row_vec</em> FLOAT8[],
+)</pre><p> and </p><pre>{TABLE|VIEW} <em>source_table</em> (
+    <em>row_id</em> INTEGER,
+    <em>col1</em> FLOAT8,
+    <em>col2</em> FLOAT8,
+    ...
+)</pre><p>Note that the column name <em>row_id</em> is taken as an input 
parameter, and should contain a continguous list of row indices (starting at 1) 
for the input matrix.</p>
+<p>The input table for sparse PCA is expected to be in the form:</p>
+<pre>{TABLE|VIEW} <em>source_table</em> (
+    ...
+    <em>row_id</em> INTEGER,
+    <em>col_id</em> INTEGER,
+    <em>val_id</em> FLOAT8,
+    ...
+)</pre><p>The <em>row_id</em> and <em>col_id</em> columns specify which 
entries in the matrix are nonzero, and the <em>val_id</em> column defines the 
values of the nonzero entries.</p>
+<p>Please refer to the <a class="el" href="group__grp__matrix.html">Matrix 
Operations</a> documentation for more details on defining matrices. </p>
+<p class="enddd"></p>
+</dd>
+<dt>out_table </dt>
+<dd><p class="startdd">TEXT. The name of the table that will contain the 
output. There are three possible output tables as described below.</p>
+<p>The primary output table (<em>out_table</em>) encodes the principal 
components with the <em>k</em> highest eigenvalues where <em>k</em> is either 
directly provided by the user or computed according to the proportion of 
variance. The table has the following columns: </p><table class="output">
+<tr>
+<th>row_id </th><td>Eigenvalue rank in descending order of the eigenvalue 
size.  </td></tr>
+<tr>
+<th>principal_components </th><td>Vectors containing elements of the principal 
components.  </td></tr>
+<tr>
+<th>std_dev </th><td>The standard deviation of each principal component.  
</td></tr>
+<tr>
+<th>proportion </th><td>The proportion of variance covered by the principal 
component.  </td></tr>
+</table>
+<p>The table <em>out_table_mean</em> contains the column means. This table has 
just one column: </p><table class="output">
+<tr>
+<th>column_mean </th><td>A vector containing the column means for the input 
matrix.  </td></tr>
+</table>
+<p>The optional table <em>result_summary_table</em> contains information about 
the performance of the PCA. The contents of this table are described under the 
<em>result_summary_table</em> argument. </p>
+<p class="enddd"></p>
+</dd>
+<dt>row_id </dt>
+<dd><p class="startdd">TEXT. Column name containing the row IDs in the input 
source table. The column should be of type INT (or a type that can be cast to 
INT) and should only contain values between 1 and <em>N</em>. For dense matrix 
format, it should contain all continguous integers from 1 to <em>N</em> 
describing the full matrix.</p>
+<p class="enddd"></p>
+</dd>
+<dt>col_id </dt>
+<dd><p class="startdd">TEXT. Column name containing the column IDs in sparse 
matrix representation. The column should be of type INT (or a type that can be 
cast to INT) and should only contain values between 1 and <em>M</em>. <em>This 
parameter applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>val_id </dt>
+<dd><p class="startdd">TEXT. Name of 'val_id' column in sparse matrix 
representation defining the values of the nonzero entries. <em>This parameter 
applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>row_dim </dt>
+<dd><p class="startdd">INTEGER. The actual number of rows in the matrix. That 
is, if the matrix was transformed into dense format, this is the number of rows 
it would have. <em>This parameter applies to sparse matrices only.</em></p>
+<p class="enddd"></p>
+</dd>
+<dt>col_dim </dt>
+<dd><p class="startdd">INTEGER. The actual number of columns in the matrix. 
That is, if the matrix was transformed into dense format, this is the number of 
columns it would have. <em>This parameter applies to sparse matrices 
only.</em></p>
+<dl class="section note"><dt>Note</dt><dd>The parameters 'row_dim' and 
'col_dim' could actually be inferred from the sparse matrix representation, so 
they will be removed in the future. For now they are maintained for backward 
compatability so you must enter them. Making 'row_dim' or 'col_dim' larger than 
the actual matrix has the effect of padding it with zeros, which is probably 
not useful.</dd></dl>
+</dd>
+<dt>components_param </dt>
+<dd><p class="startdd">INTEGER or FLOAT. The parameter to control the number 
of principal components to calculate from the input data. If 'components_param' 
is INTEGER, it is used to denote the number of principal components 
(<em>k</em>) to compute. If 'components_param' is FLOAT, the algorithm will 
return enough principal vectors so that the ratio of the sum of the eigenvalues 
collected thus far to the sum of all eigenvalues is greater than this parameter 
(proportion of variance). The value of 'components_param' must be either a 
positive INTEGER or a FLOAT in the range (0.0,1.0]</p>
+<dl class="section note"><dt>Note</dt><dd>The difference in interpretation 
between INTEGER and FLOAT was introduced to maintain backward campatibility 
after the proportion of variance feature was introduced. A special case to be 
aware of: 'components_param' = 1 (INTEGER) will return 1 principal component, 
but 'components_param' = 1.0 (FLOAT) will return all principal components, 
i.e., proportion of variance of 100%. <br />
+ <br />
+Also, please note that the number of principal components (<em>k</em>) is 
global, even in the case where grouping is used (see 'grouping_cols' below). In 
the case of grouping, proportion of variance might be a better choice; this 
could result in different numbers of principal components for different 
groups.</dd></dl>
+</dd>
+<dt>grouping_cols (optional) </dt>
+<dd><p class="startdd">TEXT, default: NULL. A comma-separated list of column 
names, with the source data grouped using the combination of all the columns. 
An independent PCA model will be computed for each combination of the grouping 
columns.</p>
+<dl class="section note"><dt>Note</dt><dd>Dense matrices can be different 
sizes for different groups if desired. Sparse matrices cannot be different 
sizes for different groups, because the 'row_dim' and 'col_dim' parameters used 
for sparse matrices are global across all groups.</dd></dl>
+</dd>
+<dt>lanczos_iter (optional) </dt>
+<dd><p class="startdd">INTEGER, default: minimum of {<em>k+40</em>, smallest 
matrix dimension} where <em>k</em> is the number of principal components 
specified in the parameter 'components_param'. This parameter defines the 
number of Lanczos iterations for the SVD calculation. The Lanczos iteration 
number roughly corresponds to the accuracy of the SVD calculation, and a higher 
number of iterations corresponds to greater accuracy but longer computation 
time. The number of iterations must be at least as large as the value of 
<em>k</em>, but no larger than the smallest dimension of the matrix. If the 
number of iterations is set to zero, then the default number of iterations will 
be used.</p>
+<dl class="section note"><dt>Note</dt><dd>If both 'lanczos_iter' and 
proportion of variance (via the 'components_param' parameter) are defined, 
'lanczos_iter' will take precedence in determining the number of principal 
components (i.e. the number of principal components will not be greater than 
'lanczos_iter' even if the target proportion had not been reached).</dd></dl>
+</dd>
+<dt>use_correlation (optional) </dt>
+<dd><p class="startdd">BOOLEAN, default FALSE. Whether to use the correlation 
matrix for calculating the principal components instead of the covariance 
matrix. Currently <em>use_correlation</em> is a placeholder for forward 
compatibility, so this value must be set to false.</p>
+<p class="enddd"></p>
+</dd>
+<dt>result_summary_table (optional) </dt>
+<dd><p class="startdd">TEXT, default NULL. Name of the optional summary table. 
When NULL, no summary table is generated.</p>
+<p class="enddd">This sumary table has the following columns: </p><table 
class="output">
+<tr>
+<th>rows_used </th><td>INTEGER. Number of data points in the input.  </td></tr>
+<tr>
+<th>exec_time (ms) </th><td>FLOAT8. Number of milliseconds for the PCA 
calculation to run.  </td></tr>
+<tr>
+<th>iter </th><td>INTEGER. Number of iterations used in the SVD calculation.  
</td></tr>
+<tr>
+<th>recon_error </th><td>FLOAT8. The absolute error in the SVD approximation.  
</td></tr>
+<tr>
+<th>relative_recon_error </th><td>FLOAT8. The relative error in the SVD 
approximation.  </td></tr>
+<tr>
+<th>use_correlation </th><td>BOOLEAN. Indicates if the correlation matrix was 
used.  </td></tr>
+</table>
+</dd>
+</dl>
+<p><a class="anchor" id="examples"></a></p><dl class="section 
user"><dt>Examples</dt><dd></dd></dl>
+<ol type="1">
+<li>View online help for the PCA training functions: <pre class="example">
+SELECT madlib.pca_train();
+or
+SELECT madlib.pca_sparse_train();
+</pre></li>
+<li>Create sample data in dense matrix form: <pre class="example">
+DROP TABLE IF EXISTS mat;
+CREATE TABLE mat (id integer,
+                  row_vec double precision[]
+                  );
+INSERT INTO mat VALUES
+(1, '{1,2,3}'),
+(2, '{2,1,2}'),
+(3, '{3,2,1}');
+</pre></li>
+<li>Run the PCA function for a specified number of principal components and 
view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table, result_table_mean;
+SELECT madlib.pca_train('mat',             -- Source table
+                        'result_table',    -- Output table
+                        'id',              -- Row id of source table
+                         2);               -- Number of principal components
+SELECT * FROM result_table ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |                     principal_components                     |      
std_dev      |    proportion     
+--------+--------------------------------------------------------------+-------------------+-------------------
+      1 | {0.707106781186547,-6.93889390390723e-18,-0.707106781186548} |  
1.41421356237309 | 0.857142857142244
+      2 | {0,1,0}                                                      | 
0.577350269189626 | 0.142857142857041
+(2 rows)
+</pre></li>
+<li>Run the PCA function for a specified proportion of variance and view the 
results: <pre class="example">
+%sql
+DROP TABLE IF EXISTS result_table, result_table_mean;
+SELECT madlib.pca_train('mat',             -- Source table
+                        'result_table',    -- Output table
+                        'id',              -- Row id of source table
+                         0.9);             -- Proportion of variance
+SELECT * FROM result_table ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |                     principal_components                     |      
std_dev      |    proportion     
+--------+--------------------------------------------------------------+-------------------+-------------------
+      1 | {0.707106781186548,-2.77555756156289e-17,-0.707106781186548} |   
1.4142135623731 | 0.857142857142245
+      2 | {-1.11022302462516e-16,-1,0}                                 | 
0.577350269189626 | 0.142857142857041
+(2 rows)
+</pre></li>
+<li>Now we use grouping in dense form to learn different models for different 
groups. First, we create sample data in dense matrix form with a grouping 
column. Note we actually have different matrix sizes for the different groups, 
which is allowed for dense: <pre class="example">
+DROP TABLE IF EXISTS mat_group;
+CREATE TABLE mat_group (
+    id integer,
+    row_vec double precision[],
+    matrix_id integer
+);
+INSERT INTO mat_group VALUES
+(1, '{1,2,3}', 1),
+(2, '{2,1,2}', 1),
+(3, '{3,2,1}', 1),
+(4, '{1,2,3,4,5}', 2),
+(5, '{2,5,2,4,1}', 2),
+(6, '{5,4,3,2,1}', 2);
+</pre></li>
+<li>Run the PCA function with grouping for a specified proportion of variance 
and view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table_group, result_table_group_mean;
+SELECT madlib.pca_train('mat_group',             -- Source table
+                        'result_table_group',    -- Output table
+                        'id',                    -- Row id of source table
+                         0.8,                    -- Proportion of variance
+                        'matrix_id');            -- Grouping column
+SELECT * FROM result_table_group ORDER BY matrix_id, row_id;
+</pre> <pre class="result">
+ row_id |                                      principal_components            
                          |     std_dev     |    proportion     | matrix_id 
+--------+------------------------------------------------------------------------------------------------+-----------------+-------------------+-----------
+      1 | {0.707106781186548,0,-0.707106781186547}                             
                          | 1.4142135623731 | 0.857142857142245 |         1
+      1 | 
{-0.555378486712784,-0.388303582074091,0.0442457354870796,0.255566375612852,0.688115693174023}
 | 3.2315220311722 | 0.764102534485173 |         2
+      2 | 
{0.587384101786277,-0.485138064894743,0.311532046315153,-0.449458074050715,0.347212037159181}
  |  1.795531127192 | 0.235897465516047 |         2
+(3 rows)
+</pre></li>
+<li>Now let's look at sparse matrices. Create sample data in sparse matrix 
form: <pre class="example">
+DROP TABLE IF EXISTS mat_sparse;
+CREATE TABLE mat_sparse (
+    row_id integer,
+    col_id integer,
+    value double precision
+);
+INSERT INTO mat_sparse VALUES
+(1, 1, 1.0),
+(2, 2, 2.0),
+(3, 3, 3.0),
+(4, 4, 4.0),
+(1, 5, 5.0),
+(2, 4, 6.0),
+(3, 2, 7.0),
+(4, 3, 8.0);
+</pre> As an aside, this is what the sparse matrix above looks like when put 
in dense form: <pre class="example">
+DROP TABLE IF EXISTS mat_dense;
+SELECT madlib.matrix_densify('mat_sparse', 
+                            'row=row_id, col=col_id, val=value', 
+                            'mat_dense');
+SELECT * FROM mat_dense ORDER BY row_id;
+</pre> <pre class="result">
+ row_id |    value    
+--------+-------------
+      1 | {1,0,0,0,5}
+      2 | {0,2,0,6,0}
+      3 | {0,7,3,0,0}
+      4 | {0,0,8,4,0}
+(4 rows)
+</pre></li>
+<li>Run the PCA sparse function for a specified number of principal components 
and view the results: <pre class="example">DROP TABLE IF EXISTS result_table, 
result_table_mean;
+SELECT madlib.pca_sparse_train( 'mat_sparse',       -- Source table
+                                'result_table',     -- Output table
+                                'row_id',           -- Row id of source table
+                                'col_id',           -- Column id of source 
table
+                                'value',            -- Value of matrix at 
row_id, col_id
+                                4,                  -- Actual number of rows 
in the matrix
+                                5,                  -- Actual number of 
columns in the matrix
+                                3);                 -- Number of principal 
components                            
+SELECT * FROM result_table ORDER BY row_id;
+</pre> Result (with principal components truncated for readability): <pre 
class="result">
+ row_id |         principal_components                 |     std_dev      |    
proportion     
+--------+----------------------------------------------+------------------+-------------------
+      1 | {-0.0876046030186158,-0.0968983772909994,... | 4.21362803829554 | 
0.436590030617467
+      2 | {-0.0647272661608605,0.877639526308692,...   | 3.68408023747461 | 
0.333748701544697
+      3 | {-0.0780380267884855,0.177956517174911,...   | 3.05606908060098 | 
0.229661267837836
+(3 rows)
+</pre></li>
+<li>Now we use grouping in sparse form to learn different models for different 
groups. First, we create sample data in sparse matrix form with a grouping 
column: <pre class="example">
+DROP TABLE IF EXISTS mat_sparse_group;
+CREATE TABLE mat_sparse_group (
+    row_id integer,
+    col_id integer,
+    value double precision,
+    matrix_id integer);
+INSERT INTO mat_sparse_group VALUES
+(1, 1, 1.0, 1),
+(2, 2, 2.0, 1),
+(3, 3, 3.0, 1),
+(4, 4, 4.0, 1),
+(1, 5, 5.0, 1),
+(2, 4, 6.0, 2),
+(3, 2, 7.0, 2),
+(4, 3, 8.0, 2);
+</pre></li>
+<li>Run the PCA function with grouping for a specified proportion of variance 
and view the results: <pre class="example">
+DROP TABLE IF EXISTS result_table_group, result_table_group_mean;
+SELECT madlib.pca_sparse_train( 'mat_sparse_group',   -- Source table
+                                'result_table_group', -- Output table
+                                'row_id',             -- Row id of source table
+                                'col_id',             -- Column id of source 
table
+                                'value',              -- Value of matrix at 
row_id, col_id
+                                4,                    -- Actual number of rows 
in the matrix
+                                5,                    -- Actual number of 
columns in the matrix
+                                0.8,                  -- Proportion of variance
+                                'matrix_id');
+SELECT * FROM result_table_group ORDER BY matrix_id, row_id;
+</pre> Result (with principal components truncated for readability): <pre 
class="result">
+ row_id |           principal_components             |     std_dev      |    
proportion     | matrix_id 
+--------+--------------------------------------------+------------------+-------------------+-----------
+      1 | {-0.17805696611353,0.0681313257646983,...  | 2.73659933165925 | 
0.544652792875481 |         1
+      2 | {-0.0492086814863993,0.149371585357526,... | 2.06058314533194 | 
0.308800210823714 |         1
+      1 | {0,-0.479486114660443,...                  | 4.40325305087975 | 
0.520500333693473 |         2
+      2 | {0,0.689230898585949,...                   |  3.7435566458567 | 
0.376220573442628 |         2
+(4 rows)
+</pre></li>
+</ol>
+<p><a class="anchor" id="notes"></a></p><dl class="section 
user"><dt>Notes</dt><dd></dd></dl>
+<ul>
+<li>Table names can be optionally schema qualified (current_schemas() would be 
searched if a schema name is not provided) and all table and column names 
should follow case-sensitivity and quoting rules per the database. (For 
instance, 'mytable' and 'MyTable' both resolve to the same entity, i.e. 
'mytable'. If mixed-case or multi-byte characters are desired for entity names 
then the string should be double-quoted; in this case the input would be 
'"MyTable"').</li>
+<li>Because of the centering step in PCA (see <a class="el" 
href="group__grp__pca__train.html#background_pca">Technical Background</a>), 
sparse matrices almost always become dense during the training process. Since 
this implementation automatically densifies sparse matrix input, there should 
be no expected performance improvement in using sparse matrix input over dense 
matrix input.</li>
+<li>For the parameter 'components_param', INTEGER and FLOAT are interpreted 
differently. A special case to be aware of: 'components_param' = 1 (INTEGER) 
will return 1 principal component, but 'components_param' = 1.0 (FLOAT) will 
return all principal components, i.e., proportion of variance of 100%.</li>
+<li>If both 'lanczos_iter' and proportion of variance (via the 
'components_param' parameter) are defined, 'lanczos_iter' will take precedence 
in determining the number of principal components (i.e. the number of principal 
components will not be greater than 'lanczos_iter' even if the target 
proportion had not been reached).</li>
+</ul>
+<p><a class="anchor" id="background_pca"></a></p><dl class="section 
user"><dt>Technical Background</dt><dd></dd></dl>
+<p>The PCA implemented here uses a distributed SVD decomposition 
implementation to recover the principal components (as opposed to the directly 
computing the eigenvectors of the covariance matrix). Let \( \boldsymbol X \) 
be the data matrix, and let \( \hat{x} \) be a vector of the column averages of 
\( \boldsymbol{X}\). PCA computes the matrix \( \hat{\boldsymbol X} \) as 
</p><p class="formulaDsp">
+\[ \hat{\boldsymbol X} = {\boldsymbol X} - \vec{e} \hat{x}^T \]
+</p>
+<p> where \( \vec{e} \) is the vector of all ones.</p>
+<p>PCA then computes the SVD matrix factorization </p><p class="formulaDsp">
+\[ \hat{\boldsymbol X} = {\boldsymbol U}{\boldsymbol \Sigma}{\boldsymbol V}^T 
\]
+</p>
+<p> where \( {\boldsymbol \Sigma} \) is a diagonal matrix. The eigenvalues are 
recovered as the entries of \( {\boldsymbol \Sigma}/(\sqrt{(N-1)} \), and the 
principal components are the rows of \( {\boldsymbol V} \). The reasoning 
behind using N − 1 instead of N to calculate the covariance is <a 
href="https://en.wikipedia.org/wiki/Bessel%27s_correction";>Bessel's 
correction</a>.</p>
+<dl class="section note"><dt>Note</dt><dd>It is important to note that this 
PCA implementation assumes that the user will use only the principal components 
that have non-zero eigenvalues. The SVD calculation is done with the Lanczos 
method, which does not guarantee correctness for singular vectors with 
zero-valued eigenvalues. Consequently, principal components with zero-valued 
eigenvalues are not guaranteed to be correct. Generally, this will not be 
problem unless the user wants to use the principal components for the entire 
eigenspectrum.</dd></dl>
+<p><a class="anchor" id="literature"></a></p><dl class="section 
user"><dt>Literature</dt><dd></dd></dl>
+<p>[1] Principal Component Analysis. <a 
href="http://en.wikipedia.org/wiki/Principal_component_analysis";>http://en.wikipedia.org/wiki/Principal_component_analysis</a></p>
+<p>[2] Shlens, Jonathon (2009), A Tutorial on Principal Component Analysis</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="pca_8sql__in.html" title="Principal Component 
Analysis. ">pca.sql_in</a> documenting the SQL functions</p>
+<p><a class="el" href="group__grp__pca__project.html">Principal Component 
Projection</a> </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:12 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>

http://git-wip-us.apache.org/repos/asf/madlib-site/blob/e283664c/docs/v1.14/group__grp__pivot.html
----------------------------------------------------------------------
diff --git a/docs/v1.14/group__grp__pivot.html 
b/docs/v1.14/group__grp__pivot.html
new file mode 100644
index 0000000..2cdf472
--- /dev/null
+++ b/docs/v1.14/group__grp__pivot.html
@@ -0,0 +1,661 @@
+<!-- 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: Pivot</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__pivot.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">Pivot<div class="ingroups"><a class="el" 
href="group__grp__datatrans.html">Data Types and 
Transformations</a></div></div>  </div>
+</div><!--header-->
+<div class="contents">
+<div class="toc"><b>Contents</b> <ul>
+<li>
+<a href="#pivoting">Pivoting</a> </li>
+<li>
+<a href="#notes">Notes</a> </li>
+<li>
+<a href="#examples">Examples</a> </li>
+<li>
+<a href="#literature">Literature</a> </li>
+</ul>
+</div><p><a class="anchor" id="pivoting"></a>The goal of the MADlib pivot 
function is to provide a data summarization tool that can do basic OLAP type 
operations on data stored in one table and output the summarized data to a 
second table.</p>
+<pre class="syntax">
+pivot(
+    source_table,
+    output_table,
+    index,
+    pivot_cols,
+    pivot_values,
+    aggregate_func,
+    fill_value,
+    keep_null,
+    output_col_dictionary,
+    output_type
+    )
+</pre><p> <b>Arguments</b> </p><dl class="arglist">
+<dt>source_table </dt>
+<dd><p class="startdd">VARCHAR. Name of the source table (or view) containing 
data to pivot.</p>
+<p class="enddd"></p>
+</dd>
+<dt>output_table </dt>
+<dd><p class="startdd">VARCHAR. Name of output table that contains the pivoted 
data. The output table contains all the columns present in the <em>'index'</em> 
column list, plus additional columns for each distinct value in 
<em>'pivot_cols'</em>.</p>
+<dl class="section note"><dt>Note</dt><dd>The names of the columns in the 
output table are auto-generated. Please see the examples section below to see 
how this works in practice. The convention used is to concatenate the following 
strings and separate each by an underscore '_' :<ul>
+<li>name of the value column <em>'pivot_values'</em></li>
+<li>aggregate function</li>
+<li>name of the pivot column <em>'pivot_cols'</em></li>
+<li>values in the pivot column </li>
+</ul>
+</dd></dl>
+</dd>
+<dt>index  </dt>
+<dd><p class="startdd">VARCHAR. Comma-separated columns that will form the 
index of the output pivot table. By index we mean the values to group by; these 
are the rows in the output pivot table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>pivot_cols  </dt>
+<dd><p class="startdd">VARCHAR. Comma-separated columns that will form the 
columns of the output pivot table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>pivot_values  </dt>
+<dd><p class="startdd">VARCHAR. Comma-separated columns that contain the 
values to be summarized in the output pivot table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>aggregate_func (optional) </dt>
+<dd><p class="startdd">VARCHAR. default: 'AVG'. A comma-separated list of 
aggregates to be applied to values. These can be PostgreSQL built-in aggregates 
[1] or UDAs. It is possible to assign a set of aggregates per value column. 
Please refer to the examples 12-14 below for syntax details.</p>
+<dl class="section note"><dt>Note</dt><dd>Only aggregates with strict 
transition functions are permitted here. A strict transition function means 
rows with null values are ignored; the function is not called and the previous 
state value is retained. If you need some other behavior for null inputs, this 
should be done prior to calling the pivot function. Aggregates with strict 
transition functions are described in [2,3].</dd></dl>
+</dd>
+<dt>fill_value (optional) </dt>
+<dd><p class="startdd">VARCHAR. default: NULL. If specified, determines how to 
fill NULL values resulting from pivot operation. This is a global parameter 
(not applied per aggregate) and is applied post-aggregation to the output 
table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>keep_null (optional) </dt>
+<dd><p class="startdd">BOOLEAN. default: FALSE. If TRUE, then pivot columns 
are created corresponding to NULL categories. If FALSE, then no pivot columns 
will be created for NULL categories.</p>
+<p class="enddd"></p>
+</dd>
+<dt>output_col_dictionary (optional) </dt>
+<dd><p class="startdd">BOOLEAN. default: FALSE. This parameter is used to 
handle auto-generated column names that exceed the PostgreSQL limit of 63 bytes 
(could be a common occurrence). If TRUE, column names will be set as numerical 
IDs and will create a dictionary table called output_table appended with 
_dictionary. If FALSE, will auto-generate column names in the usual way unless 
the limit of 63 bytes will be exceeded. In this case, a dictionary output file 
will be created and a message given to the user. </p>
+<p class="enddd"></p>
+</dd>
+<dt>output_type (optional) </dt>
+<dd><p class="startdd">VARCHAR. default: 'column'. This parameter controls the 
output format of the pivoted variables. If 'column', a column is created for 
each pivot variable. PostgreSQL limits the number of columns in a table (250 - 
1600 depending on column types). If the total number of output columns exceeds 
this limit, then make this parameter either 'array' (to combine the output 
columns into an array) or 'svec' (to cast the array output to 
<em>'madlib.svec'</em> type). If you have an 'aggregate_func' that has an array 
return type, it cannot be combined with 'output_type'='array' or 'svec'.</p>
+<p>A dictionary will be created (<em>output_col_dictionary=TRUE</em>) when 
'output_type' is 'array' or 'svec' to define each index into the array. </p>
+<p class="enddd"></p>
+</dd>
+</dl>
+<p><a class="anchor" id="notes"></a></p><dl class="section 
note"><dt>Note</dt><dd><ul>
+<li>NULLs in the index column are treated like any other value.</li>
+<li>NULLs in the pivot column are ignored unless keep_null is TRUE.</li>
+<li>Only strict transition functions are allowed so NULLs are ignored.</li>
+<li>It is not allowed to set the fill_value parameter without setting the 
aggregate_func parameter due to possible ambiguity. Set aggregate_func to NULL 
for the default behavior and use fill_value as desired. Please note that 
full_value must be of the same type as the output of the aggregate_func (or 
capable of being cast to the same type by PostgreSQL), or else an error will 
result.</li>
+<li>It is not allowed to set the output_col_dictionary parameter without 
setting the keep_null parameter due to possible ambiguity. Set keep_null to 
NULL for the default behavior and use output_col_dictionary as desired.</li>
+<li>Expressions (instead of column names) are not supported. Create a view 
with the desired expressions and pass it as the input table (see example 3 
below).</li>
+<li>It is allowed to pass a partial mapping for the aggregate_func parameter. 
The missing value columns will be aggregated using the default function 
(average).</li>
+</ul>
+</dd></dl>
+<p><a class="anchor" id="examples"></a></p><dl class="section 
user"><dt>Examples</dt><dd></dd></dl>
+<ol type="1">
+<li>Create a simple dataset to demonstrate a basic pivot: <pre class="example">
+DROP TABLE IF EXISTS pivset CASCADE; -- View below may depend on table so use 
CASCADE
+CREATE TABLE pivset(
+                  id INTEGER,
+                  piv INTEGER,
+                  val FLOAT8
+                );
+INSERT INTO pivset VALUES
+    (0, 10, 1),
+    (0, 10, 2),
+    (0, 20, 3),
+    (1, 20, 4),
+    (1, 30, 5),
+    (1, 30, 6),
+    (1, 10, 7),
+    (NULL, 10, 8),
+    (1, NULL, 9),
+    (1, 10, NULL);
+</pre></li>
+<li>Pivot the table: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+ id | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30
+----+----------------+----------------+----------------
+  0 |            1.5 |              3 |
+  1 |              7 |              4 |            5.5
+    |              8 |                |
+</pre> Here NULL is showing as an empty cell in the output.</li>
+<li>Now let's add some more columns to our data set and create a view: <pre 
class="example">
+DROP VIEW IF EXISTS pivset_ext;
+CREATE VIEW pivset_ext AS
+    SELECT *,
+    COALESCE(id + (val / 3)::integer, 0) AS id2,
+    COALESCE(100*(val / 3)::integer, 0) AS piv2,
+    COALESCE(val + 10, 0) AS val2
+   FROM pivset;
+SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext
+ORDER BY id,id2,piv,piv2,val,val2;
+</pre> <pre class="result">
+ id | id2 | piv | piv2 | val | val2
+----+-----+-----+------+-----+------
+  0 |   0 |  10 |    0 |   1 |   11
+  0 |   1 |  10 |  100 |   2 |   12
+  0 |   1 |  20 |  100 |   3 |   13
+  1 |   0 |  10 |    0 |     |    0
+  1 |   2 |  20 |  100 |   4 |   14
+  1 |   3 |  10 |  200 |   7 |   17
+  1 |   3 |  30 |  200 |   5 |   15
+  1 |   3 |  30 |  200 |   6 |   16
+  1 |   4 |     |  300 |   9 |   19
+    |   0 |  10 |  300 |   8 |   18
+(10 rows)
+</pre></li>
+<li>Let's use a different aggregate function on the view we just created: <pre 
class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+ id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
+----+----------------+----------------+----------------
+  0 |              3 |              3 |
+  1 |              7 |              4 |             11
+    |              8 |                |
+</pre></li>
+<li>Now create a custom aggregate. Note that the aggregate must have a strict 
transition function: <pre class="example">
+DROP FUNCTION IF EXISTS array_add1 (ANYARRAY, ANYELEMENT) CASCADE;
+CREATE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$
+  SELECT $1 || $2
+$$ LANGUAGE sql STRICT;
+DROP AGGREGATE IF EXISTS array_accum1 (anyelement);
+CREATE AGGREGATE array_accum1 (anyelement) (
+    sfunc = array_add1,
+    stype = anyarray,
+    initcond = '{}'
+);
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 
'array_accum1');
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+  id | val_array_accum1_piv_10 | val_array_accum1_piv_20 | 
val_array_accum1_piv_30
+----+-------------------------+-------------------------+-------------------------
+  0 | {1,2}                   | {3}                     | {}
+  1 | {7}                     | {4}                     | {5,6}
+    | {8}                     | {}                      | {}
+</pre></li>
+<li>Keep null values in the pivot column: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True);
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+ id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 | val_sum_piv_null
+----+----------------+----------------+----------------+------------------
+  0 |              3 |              3 |                |
+  1 |              7 |              4 |             11 |                9
+    |              8 |                |                |
+</pre></li>
+<li>Fill null results with a value of interest: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111');
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+ id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
+----+----------------+----------------+----------------
+  0 |              3 |              3 |            111
+  1 |              7 |              4 |             11
+    |              8 |            111 |            111
+</pre></li>
+<li>Use multiple index columns: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');
+SELECT * FROM pivout ORDER BY id,id2;
+</pre> <pre class="result">
+ id | id2 | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30
+----+-----+----------------+----------------+----------------
+  0 |   0 |              1 |                |
+  0 |   1 |              2 |              3 |
+  1 |   0 |                |                |
+  1 |   2 |                |              4 |
+  1 |   3 |              7 |                |            5.5
+  1 |   4 |                |                |
+    |   0 |              8 |                |
+</pre></li>
+<li>Use multiple pivot columns with columnar output: <pre class="example">
+\x on
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+-[ RECORD 1 ]-----------+----
+id                      | 0
+val_avg_piv_10_piv2_0   | 1
+val_avg_piv_10_piv2_100 | 2
+val_avg_piv_10_piv2_200 |
+val_avg_piv_10_piv2_300 |
+val_avg_piv_20_piv2_0   |
+val_avg_piv_20_piv2_100 | 3
+val_avg_piv_20_piv2_200 |
+val_avg_piv_20_piv2_300 |
+val_avg_piv_30_piv2_0   |
+val_avg_piv_30_piv2_100 |
+val_avg_piv_30_piv2_200 |
+val_avg_piv_30_piv2_300 |
+-[ RECORD 2 ]-----------+----
+id                      | 1
+val_avg_piv_10_piv2_0   |
+val_avg_piv_10_piv2_100 |
+val_avg_piv_10_piv2_200 | 7
+val_avg_piv_10_piv2_300 |
+val_avg_piv_20_piv2_0   |
+val_avg_piv_20_piv2_100 | 4
+val_avg_piv_20_piv2_200 |
+val_avg_piv_20_piv2_300 |
+val_avg_piv_30_piv2_0   |
+val_avg_piv_30_piv2_100 |
+val_avg_piv_30_piv2_200 | 5.5
+val_avg_piv_30_piv2_300 |
+...
+</pre></li>
+<li>Use multiple pivot columns (same as above) with an array output: <pre 
class="example">
+DROP TABLE IF EXISTS pivout, pivout_dictionary;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val',
+                    NULL, NULL, FALSE, FALSE, 'array');
+\x off
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+   id   |                          val_avg
+--------+------------------------------------------------------------
+      0 | {1,2,NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL,NULL}
+      1 | {NULL,NULL,7,NULL,NULL,4,NULL,NULL,NULL,NULL,5.5,NULL}
+ [NULL] | {NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
+</pre> <pre class="example">
+-- Use the dictionary to understand what each index of an array corresponds to
+SELECT * FROM pivout_dictionary;
+</pre> <pre class="result">
+ __pivot_cid__ | pval | agg | piv | piv2 |         col_name
+---------------+------+-----+-----+------+---------------------------
+ 1             | val  | avg |  10 |    0 | "val_avg_piv_10_piv2_0"
+ 2             | val  | avg |  10 |  100 | "val_avg_piv_10_piv2_100"
+ 3             | val  | avg |  10 |  200 | "val_avg_piv_10_piv2_200"
+ 4             | val  | avg |  10 |  300 | "val_avg_piv_10_piv2_300"
+ 5             | val  | avg |  20 |    0 | "val_avg_piv_20_piv2_0"
+ 6             | val  | avg |  20 |  100 | "val_avg_piv_20_piv2_100"
+ 7             | val  | avg |  20 |  200 | "val_avg_piv_20_piv2_200"
+ 8             | val  | avg |  20 |  300 | "val_avg_piv_20_piv2_300"
+ 9             | val  | avg |  30 |    0 | "val_avg_piv_30_piv2_0"
+ 10            | val  | avg |  30 |  100 | "val_avg_piv_30_piv2_100"
+ 11            | val  | avg |  30 |  200 | "val_avg_piv_30_piv2_200"
+ 12            | val  | avg |  30 |  300 | "val_avg_piv_30_piv2_300"
+</pre></li>
+<li>Use multiple value columns: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');
+\x on
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+-[ RECORD 1 ]---+-----
+id              | 0
+val_avg_piv_10  | 1.5
+val_avg_piv_20  | 3
+val_avg_piv_30  |
+val2_avg_piv_10 | 11.5
+val2_avg_piv_20 | 13
+val2_avg_piv_30 |
+-[ RECORD 2 ]---+-----
+id              | 1
+val_avg_piv_10  | 7
+val_avg_piv_20  | 4
+val_avg_piv_30  | 5.5
+val2_avg_piv_10 | 8.5
+val2_avg_piv_20 | 14
+val2_avg_piv_30 | 15.5
+...
+</pre></li>
+<li>Use multiple aggregate functions on the same value column (cross product): 
<pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum');
+\x on
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+-[ RECORD 1 ]--+----
+id             | 0
+val_avg_piv_10 | 1.5
+val_avg_piv_20 | 3
+val_avg_piv_30 |
+val_sum_piv_10 | 3
+val_sum_piv_20 | 3
+val_sum_piv_30 |
+-[ RECORD 2 ]--+----
+id             | 1
+val_avg_piv_10 | 7
+val_avg_piv_20 | 4
+val_avg_piv_30 | 5.5
+val_sum_piv_10 | 7
+val_sum_piv_20 | 4
+val_sum_piv_30 | 11
+...
+</pre></li>
+<li>Use different aggregate functions for different value columns: <pre 
class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',
+    'val=avg, val2=sum');
+\x on
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+-[ RECORD 1 ]---+----
+id              | 0
+val_avg_piv_10  | 1.5
+val_avg_piv_20  | 3
+val_avg_piv_30  |
+val2_sum_piv_10 | 23
+val2_sum_piv_20 | 13
+val2_sum_piv_30 |
+-[ RECORD 2 ]---+----
+id              | 1
+val_avg_piv_10  | 7
+val_avg_piv_20  | 4
+val_avg_piv_30  | 5.5
+val2_sum_piv_10 | 17
+val2_sum_piv_20 | 14
+val2_sum_piv_30 | 31
+...
+</pre></li>
+<li>Use multiple aggregate functions for different value columns: <pre 
class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',
+    'val=avg, val2=[avg,sum]');
+\x on
+SELECT * FROM pivout ORDER BY id;
+</pre> <pre class="result">
+-[ RECORD 1 ]---+-----
+id              | 0
+val_avg_piv_10  | 1.5
+val_avg_piv_20  | 3
+val_avg_piv_30  |
+val2_avg_piv_10 | 11.5
+val2_avg_piv_20 | 13
+val2_avg_piv_30 |
+val2_sum_piv_10 | 23
+val2_sum_piv_20 | 13
+val2_sum_piv_30 |
+-[ RECORD 2 ]---+-----
+id              | 1
+val_avg_piv_10  | 7
+val_avg_piv_20  | 4
+val_avg_piv_30  | 5.5
+val2_avg_piv_10 | 8.5
+val2_avg_piv_20 | 14
+val2_avg_piv_30 | 15.5
+val2_sum_piv_10 | 17
+val2_sum_piv_20 | 14
+val2_sum_piv_30 | 31
+...
+</pre></li>
+<li>Combine all of the options: <pre class="example">
+DROP TABLE IF EXISTS pivout;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, 
val2',
+    'val=avg, val2=[avg,sum]', '111', True);
+\x on
+SELECT * FROM pivout ORDER BY id,id2;
+</pre> <pre class="result">
+-[ RECORD 1 ]--------------+-----
+id                         | 0
+id2                        | 0
+val_avg_piv_null_piv2_0    | 111
+val_avg_piv_null_piv2_100  | 111
+val_avg_piv_null_piv2_200  | 111
+val_avg_piv_null_piv2_300  | 111
+val_avg_piv_10_piv2_0      | 1
+val_avg_piv_10_piv2_100    | 111
+val_avg_piv_10_piv2_200    | 111
+val_avg_piv_10_piv2_300    | 111
+val_avg_piv_20_piv2_0      | 111
+val_avg_piv_20_piv2_100    | 111
+val_avg_piv_20_piv2_200    | 111
+val_avg_piv_20_piv2_300    | 111
+val_avg_piv_30_piv2_0      | 111
+val_avg_piv_30_piv2_100    | 111
+val_avg_piv_30_piv2_200    | 111
+val_avg_piv_30_piv2_300    | 111
+val2_avg_piv_null_piv2_0   | 111
+val2_avg_piv_null_piv2_100 | 111
+val2_avg_piv_null_piv2_200 | 111
+val2_avg_piv_null_piv2_300 | 111
+val2_avg_piv_10_piv2_0     | 11
+val2_avg_piv_10_piv2_100   | 111
+...
+-[ RECORD 2 ]--------------+-----
+id                         | 0
+id2                        | 1
+val_avg_piv_null_piv2_0    | 111
+val_avg_piv_null_piv2_100  | 111
+val_avg_piv_null_piv2_200  | 111
+val_avg_piv_null_piv2_300  | 111
+val_avg_piv_10_piv2_0      | 111
+val_avg_piv_10_piv2_100    | 2
+val_avg_piv_10_piv2_200    | 111
+val_avg_piv_10_piv2_300    | 111
+val_avg_piv_20_piv2_0      | 111
+val_avg_piv_20_piv2_100    | 3
+val_avg_piv_20_piv2_200    | 111
+val_avg_piv_20_piv2_300    | 111
+val_avg_piv_30_piv2_0      | 111
+val_avg_piv_30_piv2_100    | 111
+val_avg_piv_30_piv2_200    | 111
+val_avg_piv_30_piv2_300    | 111
+val2_avg_piv_null_piv2_0   | 111
+val2_avg_piv_null_piv2_100 | 111
+val2_avg_piv_null_piv2_200 | 111
+val2_avg_piv_null_piv2_300 | 111
+...
+</pre></li>
+<li>Create a dictionary for output column names: <pre class="example">
+DROP TABLE IF EXISTS pivout, pivout_dictionary;
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, 
val2',
+    'val=avg, val2=[avg,sum]', '111', True, True);
+\x off
+SELECT * FROM pivout_dictionary order by __pivot_cid__;
+</pre> <pre class="result">
+__pivot_cid__ | pval | agg |  piv   | piv2 |           col_name
+---------------+------+-----+--------+------+------------------------------
+ __p_1__       | val  | avg | [NULL] |    0 | "val_avg_piv_null_piv2_0"
+ __p_2__       | val  | avg | [NULL] |  100 | "val_avg_piv_null_piv2_100"
+ __p_3__       | val  | avg | [NULL] |  200 | "val_avg_piv_null_piv2_200"
+ __p_4__       | val  | avg | [NULL] |  300 | "val_avg_piv_null_piv2_300"
+ __p_5__       | val  | avg |     10 |    0 | "val_avg_piv_10_piv2_0"
+ __p_6__       | val  | avg |     10 |  100 | "val_avg_piv_10_piv2_100"
+ __p_7__       | val  | avg |     10 |  200 | "val_avg_piv_10_piv2_200"
+ __p_8__       | val  | avg |     10 |  300 | "val_avg_piv_10_piv2_300"
+ __p_9__       | val  | avg |     20 |    0 | "val_avg_piv_20_piv2_0"
+ __p_10__      | val  | avg |     20 |  100 | "val_avg_piv_20_piv2_100"
+ __p_11__      | val  | avg |     20 |  200 | "val_avg_piv_20_piv2_200"
+ __p_12__      | val  | avg |     20 |  300 | "val_avg_piv_20_piv2_300"
+ __p_13__      | val  | avg |     30 |    0 | "val_avg_piv_30_piv2_0"
+ __p_14__      | val  | avg |     30 |  100 | "val_avg_piv_30_piv2_100"
+ __p_15__      | val  | avg |     30 |  200 | "val_avg_piv_30_piv2_200"
+ __p_16__      | val  | avg |     30 |  300 | "val_avg_piv_30_piv2_300"
+ __p_17__      | val2 | avg | [NULL] |    0 | "val2_avg_piv_null_piv2_0"
+ __p_18__      | val2 | avg | [NULL] |  100 | "val2_avg_piv_null_piv2_100"
+ __p_19__      | val2 | avg | [NULL] |  200 | "val2_avg_piv_null_piv2_200"
+ __p_20__      | val2 | avg | [NULL] |  300 | "val2_avg_piv_null_piv2_300"
+ __p_21__      | val2 | avg |     10 |    0 | "val2_avg_piv_10_piv2_0"
+...
+(48 rows)
+</pre> <pre class="example">
+\x on
+SELECT * FROM pivout ORDER BY id,id2;
+</pre> <pre class="result">
+-[ RECORD 1 ]----
+id       | 0
+id2      | 0
+__p_1__  | 111
+__p_2__  | 111
+__p_3__  | 111
+__p_4__  | 111
+__p_5__  | 1
+__p_6__  | 111
+__p_7__  | 111
+__p_8__  | 111
+__p_9__  | 111
+__p_10__ | 111
+__p_11__ | 111
+__p_12__ | 111
+__p_13__ | 111
+...
+-[ RECORD 2 ]----
+id       | 0
+id2      | 1
+__p_1__  | 111
+__p_2__  | 111
+__p_3__  | 111
+__p_4__  | 111
+__p_5__  | 111
+__p_6__  | 2
+__p_7__  | 111
+__p_8__  | 111
+__p_9__  | 111
+__p_10__ | 3
+__p_11__ | 111
+__p_12__ | 111
+__p_13__ | 111
+...
+-[ RECORD 3 ]----
+id       | 1
+id2      | 0
+__p_1__  | 111
+__p_2__  | 111
+__p_3__  | 111
+__p_4__  | 111
+__p_5__  | 111
+__p_6__  | 111
+__p_7__  | 111
+__p_8__  | 111
+__p_9__  | 111
+__p_10__ | 111
+__p_11__ | 111
+__p_12__ | 111
+__p_13__ | 111
+...
+</pre></li>
+</ol>
+<p><a class="anchor" id="literature"></a></p><dl class="section 
user"><dt>Literature</dt><dd></dd></dl>
+<p>NOTE: The following links refer to documentation resources for the current 
PostgreSQL database version. Depending upon your database platform version, you 
may need to change "current" references in the links to your database 
version.</p>
+<p>If your database platform uses the Greenplum Database (or related 
variants), please check with the project community and/or your database vendor 
to identify the PostgreSQL version it is based on.</p>
+<p><a class="anchor" id="svm-lit-1"></a>[1] <a 
href="https://www.postgresql.org/docs/current/static/functions-aggregate.html";>https://www.postgresql.org/docs/current/static/functions-aggregate.html</a></p>
+<p>[2] <a 
href="https://www.postgresql.org/docs/current/static/sql-createaggregate.html";>https://www.postgresql.org/docs/current/static/sql-createaggregate.html</a></p>
+<p>[3] <a 
href="https://www.postgresql.org/docs/current/static/xaggr.html";>https://www.postgresql.org/docs/current/static/xaggr.html</a>
 </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>

Reply via email to