pca_project.sql_in

Go to the documentation of this file.

/* ----------------------------------------------------------------------- *//**
 *
 * @file pca_project.sql_in
 *
 * @brief Principal Component Analysis Projection
 *
 * @sa For a brief introduction to Principal Component Analysis, see the module
 *     description \ref grp_pca.
 *
 *//* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')

/**
@addtogroup grp_pca_project

<div class ="toc"><b>Contents</b>
<ul>
<li class="level1"><a href="#pca_train">About</a></li>
<li class="level1"><a href="#help">Online Help</a></li>
<li class="level1"><a href="#train">Training Function</a></li>
<li class="level1"><a href="#output">Output Tables</a></li>
<li class="level1"><a href="#examples">Examples</a></li>
<li class="level1"><a href="#seealso">See Also</a></li>
<li class="level1"><a href="#background">Technical Background</a></li>
</ul>
</div>

@anchor about
@about

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 \f$ k  \f$  principal components with the highest variance in
the training data.  More details on the mathematics of PCA can be found in  \ref
pca_train and some details about the principal component projection calculations
can be found in the \ref background "Technical Background".

@anchor help
@par Online Help

View short help messages using the following statements:
@verbatim
-- Summary of PCA projection
madlib.pca_project()
madlib.pca_project('?')
madlib.pca_project('help')

-- Projection function syntax and output table format
madlib.pca_project('usage')

-- Summary of PCA projection with sparse matrices
madlib.pca_sparse_project()
madlib.pca_sparse_project('?')
madlib.pca_sparse_project('help')

-- Projection function syntax and output table format
madlib.pca_sparse_project('usage')
@endverbatim

@anchor train
@par Training Function
The training functions have the following formats:
@verbatim
madlib.pca_project( source_table, pc_table, out_table, row_id,
    residual_table := NULL, result_summary_table := NULL)
@endverbatim
and
@verbatim
madlib.pca_sparse_project( source_table, pc_table, out_table, row_id,
    col_id, val_id, row_dim, col_dim, residual_table := NULL,
    result_summary_table := NULL)
@endverbatim
\note 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.

\note Because of the centering step in PCA projection (see \ref background "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.

@par Arguments
<DL class="arglist">
<DT>source_table</DT>
<DD>Text value.  Source table name.
Identical to \ref pca_train, the input data matrix should have  \f$ N \f$ rows
and \f$ M \f$ columns, where \f$ N \f$ is the number of data points, and \f$ M
\f$ is the number of features for each data point.

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 \ref pca_train.</DD>

<DT>pc_table</DT>
<DD>Text value.  Table name for the table containing principal components. </DD>

<DT>out_table</DT>
<DD>Text value.  Name of the table that will contain the low-dimensional representation of the input data.</DD>

<DT>row_id</DT>
<DD>Text value.  Column name containing the row IDs in the input source table.</DD>

<DT>col_id</DT>
<DD>Text value.  Name of 'col_id' column in sparse matrix representation (sparse matrices only). </DD>

<DT>val_id</DT>
<DD>Text value.  Name of 'val_id' column in sparse matrix representation (sparse matrices only). </DD>


<DT>row_dim</DT>
<DD>Integer value.  The number of rows in the sparse matrix (sparse matrices only). </DD>

<DT>col_dim</DT>
<DD>Integer value.  The number of columns in the sparse matrix (sparse matrices only). </DD>

<DT>residual_table</DT>
<DD>Text value.  Name of the optional residual table. Default: NULL.</DD>

<DT>result_summary_table</DT>
<DD>Text value. Name of the optional summary table. Default: NULL.</DD>
</DL>

@anchor output
@par Output Tables

The output is divided into three tables (two of which are optional).

The output table ('<em>out_table</em>' above) encodes a dense matrix
 with the projection onto the principal components. The table has the following columns:
\par
<DL class="arglist">
<DT>row_id</DT>
<DD>Row id of the output matrix.</DD>
<DT>row_vec</DT>
<DD>A vector containing elements in the row of the matrix.</DD>
</DL>

The residual table ('<em>residual_table</em>' above) encodes a dense residual
 matrix.  The table has the following columns:
\par
<DL class="arglist">
<DT>row_id</DT>
<DD>Row id of the output matrix.</DD>
<DT>row_vec</DT>
<DD>A vector containing elements in the row of the residual matrix.</DD>
</DL>

The result summary table ('<em>result_summary_table</em>' above) contains information about the performance time of the PCA projection. The table has the following columns:
\par
<DL class="arglist">
<DT>exec_time</DT>
<DD>Wall clock time (ms) of the function.</DD>
<DT>residual_norm</DT>
<DD>Absolute error of the residuals.</DD>
<DT>relative_residual_norm</DT>
<DD>Relative error of the residuals.</DD>
</DL>

@anchor examples
@examp
-# Create the sample data.
@verbatim
sql> DROP TABLE IF EXISTS mat;
sql> CREATE TABLE mat (
    row_id integer,
    row_vec double precision[]
);

sql> COPY mat (row_id, row_vec) FROM stdin;
1   {1,2,5}
0   {4,7,5}
3   {9,2,4}
2   {7,4,4}
5   {0,5,5}
4   {8,5,7}
\.

@endverbatim
-# Run the PCA function and keep only the top two PCs:
@verbatim
sql> DROP TABLE IF EXISTS result_table;
sql> SELECT pca_train(
    'mat',              -- name of the input table
    'result_table',     -- name of the output table
    'row_id',           -- column containing the matrix indices
    2                   -- Number of PCA components to compute
);
@endverbatim
-# Project the original data into a low-dimensional representation.
@verbatim
sql> DROP TABLE IF EXISTS residual_table, result_summary_table, out_table;
sql> SELECT pca_project(
    'mat',              -- name of the input table
    'result_table',     -- name of the table containing the PCs
    'out_table'         -- name of the table containing the projection
    'row_id',           -- column containing the input matrix indices
    'residual_table',         -- Name of the optional residual table
    'result_summary_table'    -- Name of the optional summary table
);
@endverbatim
-# Check the error in the projection.
@verbatim
sql> SELECT * FROM result_summary_table;
   exec_time   | residual_norm | relative_residual_norm
---------------+---------------+------------------------
 5685.40501595 | 2.19726255664 |         0.099262204234
@endverbatim

@anchor seealso
@sa File pca_project.sql_in documenting the SQL functions.
@sa grp_pca_train

@anchor background
@par Technical Background

Given a table containing some principal components \f$ \boldsymbol P \f$ and
some input data \f$  \boldsymbol X \f$, the low-dimensional representation \f$
{\boldsymbol X}' \f$ is computed as  \f{align*}{ {\boldsymbol {\hat{X}}} & =
{\boldsymbol X} - \vec{e} \hat{x}^T \\  {\boldsymbol X}' & =   {\boldsymbol
{\hat {X}}} {\boldsymbol P}. \f} where \f$\hat{x}  \f$ is the column means of
\f$  \boldsymbol X \f$ and  \f$ \vec{e} \f$ is the vector of all ones.  This
step is equivalent to centering the data around the origin.

The residual table \f$ \boldsymbol R \f$ is a measure of how well the
low-dimensional representation approximates the true input data, and is computed
as \f[ {\boldsymbol R} = {\boldsymbol {\hat{X}}}  - {\boldsymbol X}' {\boldsymbol
P}^T. \f] A residual matrix with entries mostly close to zero indicates a good
representation.

The residual norm \f$ r \f$ is simply
\f[
r = \|{\boldsymbol R}\|_F
\f]
where  \f$ \|\cdot\|_F \f$ is the Frobenius norm.  The relative residual norm   \f$ r' \f$  is
\f[
r' = \frac{ \|{\boldsymbol R}\|_F }{\|{\boldsymbol X}\|_F }
\f]




**/

-- -----------------------------------------------------------------------
--  PCA projection for Dense matrices
-- -----------------------------------------------------------------------
/*
@brief Compute principal compoents for a dense matrix stored in a
        database table
*/
CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT,    -- Column name for the ID for each row
    residual_table          TEXT,    -- Residual table (Default: NULL)
    result_summary_table    TEXT     -- Table name to store summary of results (Default: NULL)
)
RETURNS VOID AS $$
PythonFunction(pca, pca_project, pca_project)
$$ LANGUAGE plpythonu;

-- Overloaded functions for optional parameters
-- -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT     -- Column name for the ID for each row
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.pca_project($1, $2, $3, $4, NULL, NULL)
$$ LANGUAGE SQL;


CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT,    -- Column name for the ID for each row
    residual_table          TEXT     -- Residual table (Default: NULL)
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.pca_project($1, $2, $3, $4, $5, NULL)
$$ LANGUAGE SQL;


-- Help and usage functions
-----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_project(
    usage_string            TEXT     -- Usage string
)
RETURNS VARCHAR AS $$
PythonFunction(pca, pca_project, pca_project_help)
$$ LANGUAGE plpythonu;


CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_project()
RETURNS VARCHAR AS $$
BEGIN
    RETURN MADLIB_SCHEMA.pca_project('');
END;
$$ LANGUAGE plpgsql VOLATILE;



-- -----------------------------------------------------------------------
--  PCA sparse projection for dense matrices
-- -----------------------------------------------------------------------
/*
@brief Compute principal compoents for a dense matrix stored in a
        database table
*/
CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_sparse_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT,    -- Column name for the row id
    col_id                  TEXT,    -- Column name for the col id
    val_id                  TEXT,    -- Column name for the value id
    row_dim                 INT4,    -- Row dimension of the sparse matrix
    col_dim                 INT4,    -- Column dimension of the sparse matrix
    residual_table          TEXT,    -- Residual table (Default: NULL)
    result_summary_table    TEXT     -- Table name to store summary of results (Default: NULL)
)
RETURNS VOID AS $$
PythonFunction(pca, pca_project, pca_sparse_project)
$$ LANGUAGE plpythonu;

-- Overloaded functions for optional parameters
-- -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_sparse_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT,    -- Column name for the row id
    col_id                  TEXT,    -- Column name for the col id
    val_id                  TEXT,    -- Column name for the value id
    row_dim                 INT4,    -- Row dimension of the sparse matrix
    col_dim                 INT4     -- Column dimension of the sparse matrix
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.pca_sparse_project($1, $2, $3, $4, $5, $6, $7, $8, NULL, NULL)
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_sparse_project(
    source_table            TEXT,    -- Source table name (dense matrix)
    pc_table                TEXT,    -- Principal components table (output from pca module)
    out_table               TEXT,    -- Output table name for the principal components
    row_id                  TEXT,    -- Column name for the row id
    col_id                  TEXT,    -- Column name for the col id
    val_id                  TEXT,    -- Column name for the value id
    row_dim                 INT4,    -- Row dimension of the sparse matrix
    col_dim                 INT4,    -- Column dimension of the sparse matrix
    residual_table          TEXT     -- Residual table (Default: NULL)
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.pca_sparse_project($1, $2, $3, $4, $5, $6, $7, $8, $9, NULL)
$$ LANGUAGE SQL;


-- Help and usage functions
-----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_sparse_project(
    usage_string            TEXT     -- Usage string
)
RETURNS VARCHAR AS $$
PythonFunction(pca, pca_project, pca_sparse_project_help)
$$ LANGUAGE plpythonu;


CREATE OR REPLACE FUNCTION
MADLIB_SCHEMA.pca_sparse_project()
RETURNS VARCHAR AS $$
BEGIN
    RETURN MADLIB_SCHEMA.pca_sparse_project('');
END;
$$ LANGUAGE plpgsql VOLATILE;