| /* ----------------------------------------------------------------------- *//** |
| * |
| * @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="#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> |
| |
| @brief Projects a higher dimensional data point to a lower |
| dimensional subspace spanned by principal components learned through the PCA training |
| procedure. |
| |
| |
| 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 |
| grp_pca_train and some details about principal component projection calculations |
| can be found in the \ref background_project "Technical Background". |
| |
| |
| @anchor project |
| @par Projection Function |
| 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> |
| |
| |
| \b Arguments |
| <DL class="arglist"> |
| <DT>source_table</DT> |
| <DD>TEXT. 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 grp_pca_train.</DD> |
| |
| <DT>pc_table</DT> |
| <DD>TEXT. Table name for the table containing principal components. </DD> |
| |
| <DT>out_table</DT> |
| <DD>TEXT. Name of the table that will contain the low-dimensional representation of the input data. |
| |
| The <em>out_table</em> encodes a dense matrix with the projection onto the |
| principal components. The table has the following columns: |
| |
| <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> |
| </DD> |
| |
| <DT>row_id</DT> |
| <DD>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.</DD> |
| |
| <DT>col_id</DT> |
| <DD>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></DD> |
| |
| <DT>val_id</DT> |
| <DD>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></DD> |
| |
| <DT>row_dim</DT> |
| <DD>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></DD> |
| |
| <DT>col_dim</DT> |
| <DD>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></DD> |
| |
| @note 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. |
| |
| <DT>residual_table (optional)</DT> |
| <DD>TEXT, default: NULL. Name of the optional residual table. |
| |
| The <em>residual_table</em> encodes a dense residual matrix. The table has the following columns: |
| |
| <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> |
| </DD> |
| |
| <DT>result_summary_table (optional)</DT> |
| <DD>TEXT, default: NULL. Name of the optional summary table. |
| |
| The <em>result_summary_table</em> contains information about the performance time of the PCA projection. The table has the following columns: |
| <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> |
| |
| @anchor examples |
| @examp |
| -# View online help for the PCA projection function: |
| <pre class="example"> |
| SELECT madlib.pca_project(); |
| </pre> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| -# 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> |
| |
| @anchor notes |
| @par Notes |
| - 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. |
| |
| - 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. |
| |
| - 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"'). |
| |
| - 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. |
| |
| |
| @anchor background_project |
| @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] |
| |
| |
| @anchor related |
| @par Related Topics |
| File pca_project.sql_in documenting the SQL functions |
| |
| \ref grp_pca_train |
| |
| |
| */ |
| |
| -- ----------------------------------------------------------------------- |
| -- 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| -- 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| -- 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 IMMUTABLE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
| |
| CREATE OR REPLACE FUNCTION |
| MADLIB_SCHEMA.pca_project() |
| RETURNS VARCHAR AS $$ |
| BEGIN |
| RETURN MADLIB_SCHEMA.pca_project(''); |
| END; |
| $$ LANGUAGE plpgsql IMMUTABLE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
| |
| |
| -- ----------------------------------------------------------------------- |
| -- 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| -- 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| 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 |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA._pca_project_union( |
| source_table TEXT, -- Source table name (dense matrix) |
| pc_table TEXT, -- Output table name for the principal components |
| out_table TEXT, -- Output table name |
| row_id TEXT, -- Column name for the ID for each row |
| original_row_id TEXT, -- copy of the row_id originally passed |
| grouping_cols TEXT, -- Comma-separated list of grouping columns (Default: NULL) |
| grouping_cols_clause TEXT, -- Part of the SQL query to be used with grouping_cols |
| residual_table TEXT, -- Residual table name |
| result_summary_table TEXT, -- Table name to store summary of results (Default: NULL) |
| grp_id INTEGER, -- a place holder id for each group |
| grouping_where_clause TEXT, -- WHERE clause using grouping_cols |
| sparse_where_condition TEXT, -- WHERE clause used when creating temp sparse matrix table with dims |
| select_grouping_cols TEXT, -- SELECT clause using grouping_cols |
| grouping_cols_values TEXT, -- distinct values of the grouping_cols |
| temp_source_table_columns TEXT, -- SELECT caluse for creating temporary copy of the source_table |
| temp_pc_table_columns TEXT, -- non grouping_cols of the source_table |
| is_sparse BOOLEAN, -- specifies if the PCA call is for sparse or dense matrices |
| col_id TEXT, -- sparse representation based detail |
| val_id TEXT, -- sparse representation based detail |
| row_dim INTEGER, -- sparse representation based detail |
| col_dim INTEGER -- sparse representation based detail |
| ) |
| RETURNS VOID AS $$ |
| PythonFunction(pca, pca_project, _pca_project_union) |
| $$ LANGUAGE plpythonu |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| |
| -- 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 IMMUTABLE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
| |
| CREATE OR REPLACE FUNCTION |
| MADLIB_SCHEMA.pca_sparse_project() |
| RETURNS VARCHAR AS $$ |
| BEGIN |
| RETURN MADLIB_SCHEMA.pca_sparse_project(''); |
| END; |
| $$ LANGUAGE plpgsql IMMUTABLE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
