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.
See the Technical Background for an introduction to principal component analysis.
pca_train( source_table, out_table, row_id, components_param, grouping_cols, lanczos_iter, use_correlation, result_summary_table )For sparse matrices:
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 )
Arguments
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.
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.
The two standard MADlib dense matrix formats are:
{TABLE|VIEW} source_table ( row_id INTEGER, row_vec FLOAT8[], )
and
{TABLE|VIEW} source_table ( row_id INTEGER, col1 FLOAT8, col2 FLOAT8, ... )
Note that the column name row_id is taken as an input parameter, and should contain a continguous list of row indices (starting at 1) for the input matrix.
The input table for sparse PCA is expected to be in the form:
{TABLE|VIEW} source_table ( ... row_id INTEGER, col_id INTEGER, val_id FLOAT8, ... )
The row_id and col_id columns specify which entries in the matrix are nonzero, and the val_id column defines the values of the nonzero entries.
Please refer to the Matrix Operations documentation for more details on defining matrices.
TEXT. The name of the table that will contain the output. There are three possible output tables as described below.
The primary output table (out_table) encodes the principal components with the k highest eigenvalues where k is either directly provided by the user or computed according to the proportion of variance. The table has the following columns:
row_id | Eigenvalue rank in descending order of the eigenvalue size. |
---|---|
principal_components | Vectors containing elements of the principal components. |
std_dev | The standard deviation of each principal component. |
proportion | The proportion of variance covered by the principal component. |
The table out_table_mean contains the column means. This table has just one column:
column_mean | A vector containing the column means for the input matrix. |
---|
The optional table result_summary_table contains information about the performance of the PCA. The contents of this table are described under the result_summary_table argument.
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 N. For dense matrix format, it should contain all continguous integers from 1 to N describing the full matrix.
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 M. This parameter applies to sparse matrices only.
TEXT. Name of 'val_id' column in sparse matrix representation defining the values of the nonzero entries. This parameter applies to sparse matrices only.
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. This parameter applies to sparse matrices only.
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. This parameter applies to sparse matrices only.
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 (k) 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]
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.
INTEGER, default: minimum of {k+40, smallest matrix dimension} where k 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 k, 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.
BOOLEAN, default FALSE. Whether to use the correlation matrix for calculating the principal components instead of the covariance matrix. Currently use_correlation is a placeholder for forward compatibility, so this value must be set to false.
TEXT, default NULL. Name of the optional summary table. When NULL, no summary table is generated.
This sumary table has the following columns:
rows_used | INTEGER. Number of data points in the input. |
---|---|
exec_time (ms) | FLOAT8. Number of milliseconds for the PCA calculation to run. |
iter | INTEGER. Number of iterations used in the SVD calculation. |
recon_error | FLOAT8. The absolute error in the SVD approximation. |
relative_recon_error | FLOAT8. The relative error in the SVD approximation. |
use_correlation | BOOLEAN. Indicates if the correlation matrix was used. |
SELECT madlib.pca_train();or
SELECT madlib.pca_sparse_train();
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}');
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;
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)
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;
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)
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);
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;
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)
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);As an aside, this is what the sparse matrix above looks like when put in dense form:
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;
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)
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;Result (with principal components truncated for readability):
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)
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);
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;Result (with principal components truncated for readability):
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)
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
\[ \hat{\boldsymbol X} = {\boldsymbol X} - \vec{e} \hat{x}^T \]
where \( \vec{e} \) is the vector of all ones.
PCA then computes the SVD matrix factorization
\[ \hat{\boldsymbol X} = {\boldsymbol U}{\boldsymbol \Sigma}{\boldsymbol V}^T \]
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 Bessel's correction.
[1] Principal Component Analysis. http://en.wikipedia.org/wiki/Principal_component_analysis
[2] Shlens, Jonathon (2009), A Tutorial on Principal Component Analysis
File pca.sql_in documenting the SQL functions