You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by nj...@apache.org on 2017/01/19 19:53:31 UTC
[4/6] incubator-madlib git commit: PCA: Add grouping support to PCA
PCA: Add grouping support to PCA
JIRA: MADLIB-947
- PCA can now handle grouping columns. pca_train() with grouping_cols
parameter specified learns an independent model for each group in
the input table. New columns corresponding to the columns specified
in grouping_cols will be created in the output, mean and summary
tables.
- If pca_project() is called on an input table that has grouping_cols
in it, the pc_table used in the parameter list must be a PCA model
table that is learnt with grouping_cols. If the input table for
pca_project() has grouping columns but the pc_table used does not
support grouping_cols, or vice versa, there will be an error thrown.
- Another important new feature is that the 'row_id' column in the
input tables always had to be serially increasing, starting from 1. That
requirement is now relaxed since this commit converts given 'row_id' to
a new column that follows the rules laid out by sparse and dense
matrix formats.
- Both the online and user docs are improved with more examples.
Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib/commit/8bd46ae8
Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib/tree/8bd46ae8
Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib/diff/8bd46ae8
Branch: refs/heads/master
Commit: 8bd46ae80be9f0c630638ece9b5c8ddf7152a85c
Parents: e0439ed
Author: Nandish Jayaram <nj...@users.noreply.github.com>
Authored: Wed Dec 21 14:18:38 2016 -0800
Committer: Nandish Jayaram <nj...@users.noreply.github.com>
Committed: Wed Jan 18 14:05:09 2017 -0800
----------------------------------------------------------------------
src/ports/postgres/modules/pca/pca.py_in | 807 ++++++++++------
src/ports/postgres/modules/pca/pca.sql_in | 169 +++-
.../postgres/modules/pca/pca_project.py_in | 932 ++++++++++++++-----
.../postgres/modules/pca/pca_project.sql_in | 242 ++++-
src/ports/postgres/modules/pca/test/pca.sql_in | 110 ++-
.../modules/pca/test/pca_project.sql_in | 128 ++-
6 files changed, 1824 insertions(+), 564 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/8bd46ae8/src/ports/postgres/modules/pca/pca.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/pca/pca.py_in b/src/ports/postgres/modules/pca/pca.py_in
index 327dfd7..9a13b2f 100644
--- a/src/ports/postgres/modules/pca/pca.py_in
+++ b/src/ports/postgres/modules/pca/pca.py_in
@@ -16,12 +16,13 @@ from linalg.svd import _svd_upper_wrap
from utilities.utilities import _array_to_string
from utilities.utilities import add_postfix
from utilities.utilities import __mad_version
-from utilities.utilities import unique_string
+from utilities.utilities import unique_string, split_quoted_delimited_str
from utilities.utilities import _assert
+from utilities.validate_args import get_cols, get_cols_and_types
+from utilities.control import MinWarning
from utilities.validate_args import columns_exist_in_table
from utilities.validate_args import table_exists
-
import time
import plpy
@@ -29,16 +30,43 @@ version_wrapper = __mad_version()
string_to_array = version_wrapper.select_vecfunc()
array_to_string = version_wrapper.select_vec_return()
+
+def pca_sparse(schema_madlib, source_table, pc_table, row_id,
+ col_id, val_id, row_dim, col_dim, k, grouping_cols,
+ lanczos_iter, use_correlation, result_summary_table,
+ variance, **kwargs):
+ """
+ Args:
+ @param schema_madlib
+ @param source_table
+ @param pc_table
+ @param row_id
+ @param col_id
+ @param val_id
+ @param row_dim
+ @param col_dim
+ @param k
+ @param grouping_cols
+ @param lanczos_iter
+ @param use_correlation
+ @param result_summary_table
+ @param variance
+
+ Returns:
+ None
+
+ """
+ pca_wrap(schema_madlib, source_table, pc_table, row_id,
+ k, grouping_cols, lanczos_iter, use_correlation,
+ result_summary_table, variance, True, col_id,
+ val_id, row_dim, col_dim)
+# ------------------------------------------------------------------------
+
# ========================================================================
def pca(schema_madlib, source_table, pc_table, row_id,
k, grouping_cols, lanczos_iter, use_correlation,
result_summary_table, variance, **kwargs):
"""
- Compute the PCA of the matrix in source_table.
-
- This function is the specific call for dense matrices and creates three
- tables corresponding to the three decomposition matrices.
-
Args:
@param schema_madlib
@param source_table
@@ -55,7 +83,23 @@ def pca(schema_madlib, source_table, pc_table, row_id,
None
"""
- startTime = time.time() # measure the starting time
+ pca_wrap(schema_madlib, source_table, pc_table, row_id,
+ k, grouping_cols, lanczos_iter, use_correlation,
+ result_summary_table, variance)
+# ------------------------------------------------------------------------
+
+
+def pca_wrap(schema_madlib, source_table, pc_table, row_id,
+ k, grouping_cols, lanczos_iter, use_correlation,
+ result_summary_table, variance, is_sparse=False, col_id=None,
+ val_id=None, row_dim=None, col_dim=None, **kwargs):
+ """
+ This wrapper was added to support grouping columns. This
+ function does the necessary pre-processing for handling
+ grouping_cols, if set. It then constructs a single query
+ that includes a separate "madlib._pca_union(...)" for each
+ group.
+ """
# Reset the message level to avoid random messages
old_msg_level = plpy.execute("""
SELECT setting
@@ -63,25 +107,232 @@ def pca(schema_madlib, source_table, pc_table, row_id,
WHERE name='client_min_messages'
""")[0]['setting']
plpy.execute('SET client_min_messages TO warning')
+ grouping_cols_list = []
+ _validate_args_output_table(pc_table, result_summary_table)
+ if(grouping_cols):
+ # validate the grouping columns. We currently only support grouping_cols
+ # to be column names in the source_table, and not expressions!
+ grouping_cols_list = split_quoted_delimited_str(grouping_cols)
+ _assert(columns_exist_in_table(source_table, grouping_cols_list, schema_madlib),
+ "PCA error: One or more grouping columns in {0} do not exist!".format(grouping_cols))
+ distinct_grouping_values = plpy.execute("""
+ SELECT DISTINCT {grouping_cols} FROM {source_table}
+ """.format(grouping_cols=grouping_cols, source_table=source_table))
+ else:
+ grouping_cols = ''
+ other_columns_in_table = [col for col in get_cols(source_table) if col not in grouping_cols_list]
+ grouping_cols_clause = ''
+ if grouping_cols_list:
+ cols_names_types = get_cols_and_types(source_table)
+ grouping_cols_clause = ', ' + ', '.join([c_name+" "+c_type for (c_name, c_type) in cols_names_types if c_name in grouping_cols_list])
+ ## Create all output tables
+ plpy.execute("""
+ CREATE TABLE {pc_table} (
+ row_id INTEGER,
+ principal_components double precision[],
+ std_dev double precision,
+ proportion double precision
+ {grouping_cols_clause}
+ )
+ """.format(pc_table=pc_table, grouping_cols_clause=grouping_cols_clause))
+ pc_table_mean = add_postfix(pc_table, "_mean")
+ plpy.execute("""
+ DROP TABLE IF EXISTS {pc_table_mean};
+ CREATE TABLE {pc_table_mean} (
+ column_mean double precision[]
+ {grouping_cols_clause}
+ )
+ """.format(pc_table_mean=pc_table_mean, grouping_cols_clause=grouping_cols_clause))
+ if result_summary_table:
+ plpy.execute("""
+ DROP TABLE IF EXISTS {0};
+ CREATE TABLE {0} (
+ rows_used INTEGER,
+ "exec_time (ms)" numeric,
+ iter INTEGER,
+ recon_error double precision,
+ relative_recon_error double precision,
+ use_correlation boolean
+ {1}
+ )
+ """.format(result_summary_table, grouping_cols_clause))
+ else:
+ result_summary_table = ''
+
+ # declare variables whose values will be different for each group, if
+ # grouping_cols is specified
+ grouping_where_clause = ''
+ select_grouping_cols = ''
+ temp_table_columns = ''
+ result_summary_table_temp = ''
+ other_columns_in_table.remove(row_id)
+ temp_table_columns = """ ROW_NUMBER() OVER({partition_over}) AS row_id,
+ """.format(partition_over='' if not grouping_cols else 'PARTITION BY {0}'.format(grouping_cols)) +\
+ ','.join(other_columns_in_table)
+ # We can now ignore the original row_id for all computations since we will
+ # create a new table with a row_id column that has perfect serially increasing
+ # row_id value. This is to support the scenario where users are not forced
+ # to have a row_id that follows a particular format. This restriction of having to
+ # provide a serially increasing row_id value starting from 1 becomes a pain
+ # point when grouping is used, since the row_id for each group will then have
+ # to start from 1.
+ row_id = 'row_id'
+ pca_union_call_list = []
+ grp_id = 0
+ if not is_sparse:
+ col_id = 'NULL'
+ val_id = 'NULL'
+ row_dim = 0
+ col_dim = 0
+ while True:
+ if result_summary_table:
+ result_summary_table_temp = result_summary_table + unique_string() + "_" + str(grp_id)
+ if grouping_cols:
+ grp_value_dict = distinct_grouping_values[grp_id]
+ grouping_where_clause = ' WHERE ' + ' AND '.join([str(key)+"="+str(value) for (key, value) in grp_value_dict.items()])
+ select_grouping_cols = ', ' + ', '.join([str(value)+" AS "+key for (key, value) in grp_value_dict.items()])
+
+ pca_union_call_list.append("""
+ {schema_madlib}._pca_union('{source_table}', '{pc_table}', '{pc_table_mean}', '{row_id}',
+ {k}, '{grouping_cols}', {lanczos_iter}, {use_correlation},
+ '{result_summary_table}', '{result_summary_table_temp}', {variance},
+ {grp_id}, '{grouping_where_clause}', '{select_grouping_cols}',
+ '{temp_table_columns}', {is_sparse}, '{col_id}', '{val_id}',
+ {row_dim}, {col_dim})
+ """.format(schema_madlib=schema_madlib,
+ source_table=source_table, pc_table=pc_table,
+ pc_table_mean=pc_table_mean, row_id=row_id,
+ k='NULL' if k is None else k, grouping_cols=grouping_cols,
+ lanczos_iter=lanczos_iter, use_correlation=use_correlation,
+ result_summary_table=result_summary_table,
+ result_summary_table_temp=result_summary_table_temp,
+ variance='NULL' if variance==None else variance,
+ grp_id=grp_id, grouping_where_clause=grouping_where_clause,
+ select_grouping_cols=select_grouping_cols,
+ temp_table_columns=temp_table_columns, is_sparse=is_sparse,
+ col_id=col_id, val_id=val_id, row_dim=row_dim, col_dim=col_dim))
+ grp_id += 1
+ if not grouping_cols_list or len(distinct_grouping_values) == grp_id:
+ break
+ # "SELECT <query_1>, <query_2>, <query_3>, ..." is expected to run each
+ # <query_i> in parallel.
+ pca_union_call = 'SELECT ' + ', '.join(pca_union_call_list)
+ try:
+ plpy.execute(pca_union_call)
+ except Exception as e:
+ ## drop the output tables that were created if PCA errored out.
+ plpy.execute("""
+ DROP TABLE IF EXISTS {0};
+ DROP TABLE IF EXISTS {1};
+ """.format(pc_table, pc_table_mean))
+ if result_summary_table:
+ plpy.execute("""
+ DROP TABLE IF EXISTS {0};
+ """.format(result_summary_table))
+ plpy.error(str(e) + "\n" + str(e.args) + "\n" + str(e.strerror))
+ raise
+
+ plpy.execute("SET client_min_messages TO %s" % old_msg_level)
+
+
+def _pca_union(schema_madlib, source_table, pc_table, pc_table_mean,
+ row_id, k, grouping_cols, lanczos_iter, use_correlation,
+ result_summary_table, result_summary_table_temp, variance,
+ grp_id, grouping_where_clause, select_grouping_cols,
+ temp_table_columns, is_sparse, col_id, val_id, row_dim,
+ col_dim, **kwargs):
+ """
+ This function does all the heavy lifting of PCA, for both pca and pca_sparse.
+ Compute the PCA of the matrix in source_table. This function is the specific
+ call for dense matrices and creates three tables corresponding to the three
+ decomposition matrices.
+ Args:
+ @param source_table TEXT, -- Source table name (dense matrix)
+ @param pc_table TEXT, -- Output table name for the principal components
+ @param pc_table_mean TEXT, -- Output table name for the principal components
+ @param row_id TEXT, -- Column name for the ID for each row
+ @param k INTEGER, -- Number of principal components to compute
+ @param grouping_cols TEXT, -- Comma-separated list of grouping columns (Default: NULL)
+ @param lanczos_iter INTEGER, -- The number of Lanczos iterations for the SVD calculation (Default: min(k+40, smallest Matrix dimension))
+ @param use_correlation BOOLEAN, -- If True correlation matrix is used for principal components (Default: False)
+ @param result_summary_table TEXT, -- Table name to store summary of results (Default: NULL)
+ @param result_summary_table_temp TEXT, -- Table name to store summary of results (Default: NULL)
+ @param variance DOUBLE PRECISION, -- The proportion of variance (Default: NULL)
+ @param grp_id INTEGER, -- a place holder id for each group
+ @param grouping_where_clause TEXT, -- WHERE clause using grouping_cols
+ @param select_grouping_cols TEXT, -- SELECT clause using grouping_cols
+ @param temp_table_columns TEXT, -- SELECT caluse for creating temporary copy of the source_table
+ @param is_sparse BOOLEAN, -- specifies if the PCA call is for sparse or dense matrices
+ @param col_id TEXT, -- sparse representation based detail
+ @param val_id TEXT, -- sparse representation based detail
+ @param row_dim INTEGER, -- sparse representation based detail
+ @param col_dim INTEGER -- sparse representation based detail
+
+ Returns:
+ None
+ """
+ ## Creation of this temp table is unnecessary if the scenario does not involve
+ ## grouping, and/or, the input table had perfect values for the row_id column.
+ ## This temp table will ensure pca works even when row_id of the source_table
+ ## does not have serially increasing numbers starting from 1;
+ source_table_grouped = "pg_temp." + unique_string() + "group_" + str(grp_id)
+ plpy.execute("""
+ CREATE TEMP TABLE {source_table_grouped} AS
+ SELECT {temp_table_columns}
+ FROM {source_table}
+ {grouping_where_clause}
+ """.format(source_table_grouped=source_table_grouped,
+ source_table=source_table, grouping_where_clause=grouping_where_clause,
+ temp_table_columns=temp_table_columns))
+ startTime = time.time() # measure the starting time
# Step 1: Validate the input arguments
- _validate_args(schema_madlib, source_table, pc_table, k,
- row_id, None, None, None, None,
- grouping_cols, lanczos_iter, use_correlation,
- result_summary_table,variance)
+ if is_sparse:
+ _validate_args(schema_madlib, source_table_grouped, k, row_id, col_id,
+ val_id, row_dim, col_dim, lanczos_iter,
+ use_correlation, variance)
+ # Step 1.1: Densify the matrix for sparse input tables
+ # We densify the matrix because the recentering process will generate a
+ # dense matrix, so we just wrap around regular PCA.
+ # First we must copy the sparse matrix and add in the dimension information
+ sparse_temp = "pg_temp." + unique_string() + "_sparse"
+
+ # Add in the dimension information needed by the densifying process
+ create_temp_sparse_matrix_table_with_dims(source_table_grouped, sparse_temp,
+ row_id, col_id, val_id,
+ row_dim, col_dim)
+
+ x_dense = "pg_temp." + unique_string() + "_dense"
+ plpy.execute("""
+ SELECT {schema_madlib}.matrix_densify(
+ '{sparse_temp}',
+ 'row={row_id}, col={col_id}, val={val_id}',
+ '{x_dense}', 'row=row_id, val=row_vec')
+ """.format(schema_madlib=schema_madlib,
+ sparse_temp=sparse_temp, row_id=row_id,
+ col_id=col_id, val_id=val_id, x_dense=x_dense))
+ plpy.execute("""
+ DROP TABLE IF EXISTS {0};
+ """.format(sparse_temp))
+ source_table_grouped = x_dense
+ row_id = 'row_id'
+ else:
+ _validate_args(schema_madlib, source_table_grouped, k,
+ row_id, None, None, None, None,
+ lanczos_iter, use_correlation, variance)
# Make sure that the table has row_id and row_vec
source_table_copy = "pg_temp." + unique_string() + "_reformated_names"
created_new_table = cast_dense_input_table_to_correct_columns(
- schema_madlib, source_table, source_table_copy, row_id)
+ schema_madlib, source_table_grouped, source_table_copy, row_id)
if(created_new_table):
- source_table = source_table_copy
+ source_table_grouped = source_table_copy
- [row_dim, col_dim] = get_dims(source_table,
+ [row_dim, col_dim] = get_dims(source_table_grouped,
{'row': 'row_id', 'col': 'col_id',
'val': 'row_vec'})
- validate_dense(source_table,
+ validate_dense(source_table_grouped,
{'row': 'row_id', 'val': 'row_vec'},
check_col=False, row_dim=row_dim)
if k:
@@ -102,13 +353,13 @@ def pca(schema_madlib, source_table, pc_table, row_id,
curK = lanczos_iter
# Note: we currently don't support grouping columns or correlation matrices
- if grouping_cols is None and not use_correlation:
+ if not use_correlation:
# Step 2: Normalize the data (Column means)
dimension = col_dim
scaled_source_table = "pg_temp." + unique_string() + "_scaled_table"
column_mean_str = _recenter_data(schema_madlib,
- source_table,
+ source_table_grouped,
scaled_source_table,
'row_id',
'row_vec',
@@ -116,19 +367,19 @@ def pca(schema_madlib, source_table, pc_table, row_id,
# Step 3: Create temporary output & result summary table
svd_output_temp_table = "pg_temp."+ unique_string()+ "_svd_out_tbl"
- if result_summary_table is None:
+ if result_summary_table_temp is None:
result_summary_table_string = ''
else:
- result_summary_table_string = ", '{0}'".format(result_summary_table)
+ result_summary_table_string = ", '{0}'".format(result_summary_table_temp)
# Step 4: Perform SVD
# Step 4.1: Perform upper part of SVD
- if result_summary_table:
+ if result_summary_table_temp:
t0 = time.time()
(source_table_svd,bd_pref) = _svd_upper_wrap(schema_madlib,
scaled_source_table, svd_output_temp_table,
- row_id, curK, lanczos_iter, result_summary_table)
+ row_id, curK, lanczos_iter, result_summary_table_temp)
# Calculate the sum of values for proportion
svd_var_s = add_postfix(svd_output_temp_table, "_s")
@@ -195,9 +446,9 @@ def pca(schema_madlib, source_table, pc_table, row_id,
svd_output_temp_table, row_id, curK, lanczos_iter, bd_pref)
# Step 4.4: Create the SVD result table
- if result_summary_table:
+ if result_summary_table_temp:
t1 = time.time()
- [row_dim, col_dim] = get_dims(source_table,
+ [row_dim, col_dim] = get_dims(source_table_grouped,
{'row': 'row_id', 'col': 'col_id', 'val': 'row_vec'})
arguments = {'schema_madlib': schema_madlib,
'source_table': scaled_source_table,
@@ -206,7 +457,7 @@ def pca(schema_madlib, source_table, pc_table, row_id,
'matrix_s': add_postfix(tmp_matrix_table, "_s"),
'row_dim': row_dim,
'col_dim': col_dim,
- 'result_summary_table': result_summary_table,
+ 'result_summary_table': result_summary_table_temp,
'temp_prefix': "pg_temp." + unique_string(),
't0': t0, 't1': t1}
create_summary_table(**arguments)
@@ -229,11 +480,12 @@ def pca(schema_madlib, source_table, pc_table, row_id,
# Step 6: Insert the output of SVD into the PCA table
plpy.execute(
"""
- CREATE TABLE {pc_table} AS
+ INSERT INTO {pc_table}
SELECT {svd_v_transpose}.row_id,
row_vec AS principal_components,
value / sqrt({row_dim} - 1) AS std_dev,
((value*value)/ {eigen_sum}) AS proportion
+ {select_grouping_cols}
FROM {svd_v_transpose},
{svd_output_temp_table_s}
WHERE ({svd_v_transpose}.row_id = {svd_output_temp_table_s}.row_id)
@@ -244,30 +496,24 @@ def pca(schema_madlib, source_table, pc_table, row_id,
svd_v_transpose=svd_v_transpose,
pc_table=pc_table,
row_dim=row_dim,
- eigen_sum=eigen_sum))
+ eigen_sum=eigen_sum,
+ select_grouping_cols=select_grouping_cols))
# Output the column mean
- pc_table_mean = add_postfix(pc_table, "_mean")
plpy.execute(
"""
- DROP TABLE IF EXISTS {pc_table_mean};
- CREATE TABLE {pc_table_mean} AS
+ INSERT INTO {pc_table_mean}
SELECT '{column_mean_str}'::FLOAT8[] AS column_mean
+ {select_grouping_cols}
""".format(pc_table_mean=pc_table_mean,
- column_mean_str=column_mean_str))
+ column_mean_str=column_mean_str,
+ select_grouping_cols=select_grouping_cols))
# Step 7: Append to the SVD summary table to get the PCA summary table
- if result_summary_table:
+ if result_summary_table_temp:
stopTime = time.time()
dt = (stopTime - startTime) * 1000.
- summary_table_tmp_name = unique_string()
- plpy.execute(
- """
- ALTER TABLE {result_summary_table}
- RENAME TO {tmp_name};
- """.format(result_summary_table=result_summary_table,
- tmp_name=summary_table_tmp_name))
plpy.execute(
"""
- CREATE TABLE {result_summary_table} AS
+ INSERT INTO {result_summary_table}
SELECT
rows_used,
{dt} AS "exec_time (ms)",
@@ -275,13 +521,15 @@ def pca(schema_madlib, source_table, pc_table, row_id,
recon_error,
relative_recon_error,
{use_correlation} AS use_correlation
- FROM {tmp_name};
+ {select_grouping_cols}
+ FROM {result_summary_table_temp};
""".format(result_summary_table=result_summary_table,
dt=str(dt), iter=curK,
use_correlation=bool(use_correlation),
- tmp_name=summary_table_tmp_name))
- plpy.execute("DROP TABLE {tmp_name};".format(
- tmp_name=summary_table_tmp_name))
+ result_summary_table_temp=result_summary_table_temp,
+ select_grouping_cols=select_grouping_cols))
+ plpy.execute("DROP TABLE {result_summary_table_temp};".format(
+ result_summary_table_temp=result_summary_table_temp))
# Step 8: Output handling & cleanup
plpy.execute(
@@ -294,6 +542,7 @@ def pca(schema_madlib, source_table, pc_table, row_id,
DROP TABLE IF EXISTS {svd_output_temp_table_u};
DROP TABLE IF EXISTS {svd_output_temp_table_v};
DROP TABLE IF EXISTS {scaled_source_table};
+ DROP TABLE IF EXISTS {source_table_grouped};
""".format(svd_output_temp_table=svd_output_temp_table,
svd_output_temp_table_s=svd_output_temp_table_s,
svd_output_temp_table_u=svd_output_temp_table_u,
@@ -301,9 +550,8 @@ def pca(schema_madlib, source_table, pc_table, row_id,
scaled_source_table=scaled_source_table,
svd_v_transpose=svd_v_transpose,
source_table_copy=source_table_copy,
- tmp_matrix_s_table=tmp_matrix_s_table))
-
- plpy.execute("SET client_min_messages TO %s" % old_msg_level)
+ tmp_matrix_s_table=tmp_matrix_s_table,
+ source_table_grouped=source_table_grouped))
# ------------------------------------------------------------------------
# ------------------------------------------------------------------------
@@ -311,24 +559,20 @@ def pca(schema_madlib, source_table, pc_table, row_id,
# ------------------------------------------------------------------------
def _validate_args(schema_madlib,
source_table,
- pc_table,
k,
row_id,
col_id=None,
val_id=None,
row_dim=None,
col_dim=None,
- grouping_cols=None,
lanczos_iter=0,
use_correlation=False,
- result_summary_table=None,
variance=None):
"""
Validates all arguments passed to the PCA function
Args:
@param schema_madlib Name of the schema where MADlib is installed
@param source_table Name of the source table
- @param output_table Name of the output table
@param k Number of singular vectors to return
@param row_id Name of the row_id column
@param col_id Name of the col_id column
@@ -336,7 +580,6 @@ def _validate_args(schema_madlib,
@param grouping_cols The columns that the data should be grouped by
@param lanczos_iter The number of lanczos iterations to use in the SVD calculation
@param use_correlation If the correlation matrix should be used instead of the covariance matrix
- @param result_summary_table Name of summary table
@param variance Proportion of variance
Returns:
@@ -360,23 +603,11 @@ def _validate_args(schema_madlib,
if (variance <= 0) or (variance >1):
plpy.error("""PCA error: components_param must be either
a positive integer or a float in the range (0.0,1.0]!""")
- # confirm output tables are valid
- if pc_table:
- _assert(not table_exists(pc_table, only_first_schema=True) and
- not table_exists(pc_table + '_mean', only_first_schema=True),
- "PCA error: Output table {pc_table}/{pc_table}_mean "
- "already exist!".format(pc_table=pc_table))
- else:
- plpy.error("PCA error: Invalid output table prefix!")
_assert(columns_exist_in_table(source_table, [row_id], schema_madlib),
"PCA error: {1} column does not exist in {0}!".
format(source_table, "NULL" if row_id is None else row_id))
- if(grouping_cols):
- plpy.error("PCA error: Grouping columns are not currently supported!\
- This value must be set to NULL")
-
if (lanczos_iter < 0):
plpy.error("PCA error: lanczos_iter can't be negative! (Use zero for \
default value) The provided value is {0}".format(str(lanczos_iter)))
@@ -415,15 +646,27 @@ def _validate_args(schema_madlib,
if use_correlation:
plpy.error("PCA error: Using the correlation matrix is not enabled! \
This value must be set to FALSE")
+# ========================================================================
+def _validate_args_output_table(pc_table, result_summary_table=None):
+ """
+ confirm output tables are valid
+ @param pc_table Name of the output table
+ @param result_summary_table Name of summary table
+ """
+ if pc_table:
+ _assert(not table_exists(pc_table, only_first_schema=True) and
+ not table_exists(pc_table + '_mean', only_first_schema=True),
+ "PCA error: Output table {pc_table}/{pc_table}_mean "
+ "already exist!".format(pc_table=pc_table))
+ else:
+ plpy.error("PCA error: Invalid output table prefix!")
if result_summary_table:
if not result_summary_table.strip():
plpy.error("PCA error: Invalid result summary table name!")
_assert(not table_exists(result_summary_table, only_first_schema=True),
"PCA error: Result summary table {0} \
already exists!".format(result_summary_table))
-# ========================================================================
-
def _recenter_data(schema_madlib, source_table, output_table, row_id,
col_name, dimension):
@@ -476,123 +719,7 @@ def _recenter_data(schema_madlib, source_table, output_table, row_id,
return x_mean_str
# ------------------------------------------------------------------------
-
-def pca_sparse(schema_madlib,
- source_table,
- pc_table,
- row_id,
- col_id,
- val_id,
- row_dim,
- col_dim,
- k,
- grouping_cols,
- lanczos_iter,
- use_correlation,
- result_summary_table,
- variance,
- **kwargs):
- """
- Compute the PCA of a sparse matrix in source_table.
-
- This function is the specific call for dense matrices and creates three
- tables corresponding to the three decomposition matrices.
-
- Args:
- @param schema_madlib
- @param source_table
- @param pc_table
- @param row_id
- @param col_id
- @param val_id
- @param row_dim
- @param col_dim
- @param k
- @param grouping_cols
- @param lanczos_iter
- @param use_correlation
- @param result_summary_table
- @param variance
-
- Returns:
- None
-
- """
- startTime = time.time()
- # Reset the message level to avoid random messages
- old_msg_level = plpy.execute("""
- SELECT setting
- FROM pg_settings
- WHERE name='client_min_messages'
- """)[0]['setting']
- plpy.execute('SET client_min_messages TO warning')
-
- # Step 1: Validate the input arguments
- _validate_args(schema_madlib, source_table, pc_table, k, row_id, col_id,
- val_id, row_dim, col_dim, grouping_cols, lanczos_iter,
- use_correlation, result_summary_table, variance)
-
- # Step 2: Densify the matrix
- # We densify the matrix because the recentering process will generate a
- # dense matrix, so we just wrap around regular PCA.
- # First we must copy the sparse matrix and add in the dimension information
-
- sparse_temp = "pg_temp." + unique_string() + "_sparse"
-
- # Add in the dimension information need by the densifying process
- create_temp_sparse_matrix_table_with_dims(source_table, sparse_temp,
- row_id, col_id, val_id,
- row_dim, col_dim)
-
- x_dense = "pg_temp." + unique_string() + "_dense"
- plpy.execute("""
- SELECT {schema_madlib}.matrix_densify(
- '{sparse_temp}',
- 'row={row_id}, col={col_id}, val={val_id}',
- '{x_dense}', 'row=row_id, val=row_vec')
- """.format(**locals()))
-
- # Step 3: Pass the densified matrix to regular PCA
- pca(schema_madlib, x_dense, pc_table, 'row_id',
- k, grouping_cols, lanczos_iter, use_correlation,
- result_summary_table, variance)
-
- # Step 4: Clean up
- plpy.execute("""
- DROP TABLE IF EXISTS {x_dense};
- DROP TABLE IF EXISTS {sparse_temp};
- """.format(x_dense=x_dense, sparse_temp=sparse_temp))
-
- if result_summary_table:
- stopTime = time.time()
- dt = (stopTime - startTime) * 1000.
- summary_table_tmp_name = unique_string()
- plpy.execute(
- """
- ALTER TABLE {result_summary_table}
- RENAME TO {tmp_name};
- """.format(result_summary_table=result_summary_table,
- tmp_name=summary_table_tmp_name))
- plpy.execute(
- """
- CREATE TABLE {result_summary_table} AS
- SELECT
- rows_used,
- {dt} AS "exec_time (ms)",
- iter,
- recon_error,
- relative_recon_error,
- use_correlation
- FROM {tmp_name};
- """.format(result_summary_table=result_summary_table,
- dt=str(dt), tmp_name=summary_table_tmp_name))
- plpy.execute("DROP TABLE {tmp_name};".format(
- tmp_name=summary_table_tmp_name))
-
- plpy.execute("SET client_min_messages TO %s" % old_msg_level)
-# ------------------------------------------------------------------------
-
-
+# Sparse PCA train help function
def pca_sparse_help_message(schema_madlib, message=None, **kwargs):
"""
Given a help string, provide usage information
@@ -607,58 +734,66 @@ def pca_sparse_help_message(schema_madlib, message=None, **kwargs):
if message is not None and \
message.lower() in ("usage", "help", "?"):
return """
- -----------------------------------------------------------------------
- USAGE
- -----------------------------------------------------------------------
- SELECT {schema_madlib}.pca_sparse_train(
- source_table -- TEXT, Name of data table
- pc_table -- TEXT, Name of the table containing the principle components
- row_id -- TEXT, Column name for the row coordinates.
- col_id -- TEXT, Column name for the column coordinates.
- val_id -- TEXT, Column name for the sparse values.
- row_dim, -- INTEGER, The number of rows in the sparse matrix
- col_dim, -- INTEGER, The number of columns in the sparse matrix
- components_param -- INTEGER OR FLOAT, The parameter to control the number of principal components to calculate from the input data.
- [
- grouping_cols -- TEXT, Comma-separated list of grouping columns
- (Default: NULL)
- lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation
- (Default: minimum of of the smallest input
- matrix dimension and k+40)
- use_correlation -- BOOLEAN, If True correlation matrix is used for principal components
- (Default: False)
- rslt_summary_table -- TEXT, Table name to store summary of results
- (Default: NULL)
- ]
- );
- If components_param is INTEGER it is used for denoting the number of principal components to compute.
- If components_param is FLOAT it is used as the target proportion of variance.
- -------------------------------------------------------------------------
- OUTPUT TABLES
- -------------------------------------------------------------------------
- The output table ("pc_table" above) has the following columns:
- row_id -- INTEGER, The ranking of the eigenvalues
- prin_comp -- FLOAT[], The principal components
- eigen_values -- FLOAT[] The eigenvalues associated with each principal component
-
- A secondary output table named "pc_table"_mean is also generated.
- This table has only the single column:
- column_mean -- FLOAT[], The column means of the input data
-
- -------------------------------------------------------------------------
- RESULT SUMMARY TABLE
- -------------------------------------------------------------------------
- The result summary table ("rslt_summary_table" above) has the following columns
- rows_used -- INTEGER, Number of rows used in the PCA calculation
- exec_time -- FLOAT, Number of milliseconds the PCA calculation took
- use_correlation -- BOOLEAN, Value of parameter use_correlation
- iter -- INTEGER, Number of iterations the SVD took to converge
- recon_error -- FLOAT, Absolute error in the approximation
- relative_recon_error -- FLOAT Relative error in the approximation
+-----------------------------------------------------------------------
+ USAGE
+-----------------------------------------------------------------------
+SELECT {schema_madlib}.pca_sparse_train(
+ source_table -- TEXT, Name of data table
+ pc_table -- TEXT, Name of the table containing the principal components
+ row_id -- TEXT, Column name for the row coordinates.
+ col_id -- TEXT, Column name for the column coordinates.
+ val_id -- TEXT, Column name for the sparse values.
+ row_dim, -- INTEGER, The number of rows in the sparse matrix
+ col_dim, -- INTEGER, The number of columns in the sparse matrix
+ components_param -- INTEGER OR FLOAT, The parameter to control the number of
+ principal components to calculate from the input data.
+ grouping_cols -- TEXT, Comma-separated list of grouping columns
+ (Default: NULL)
+ lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation
+ (Default: minimum of of the smallest input
+ matrix dimension and k+40)
+ use_correlation -- BOOLEAN, If True correlation matrix is used for principal components
+ (Default: False)
+ rslt_summary_table -- TEXT, Table name to store summary of results
+ (Default: NULL)
+);
+If components_param is INTEGER it is used for denoting the number of principal components to compute.
+If components_param is FLOAT it is used as the target proportion of variance.
+-------------------------------------------------------------------------
+ OUTPUT TABLES
+-------------------------------------------------------------------------
+A PCA model is created for each group, if grouping_cols is specified.
+The output table ("pc_table" above) has the following columns:
+ row_id -- INTEGER, The ranking of the eigenvalues
+ prin_comp -- FLOAT[], The principal components
+ eigen_values -- FLOAT[] The eigenvalues associated with each principal component
+ grouping_cols -- The grouping columns (with their types), if any,
+ specified in grouping_cols
+
+A secondary output table named "pc_table"_mean is also generated.
+This table has only the single column:
+ column_mean -- FLOAT[], The column means of the input data
+
+-------------------------------------------------------------------------
+ RESULT SUMMARY TABLE
+-------------------------------------------------------------------------
+The result summary table ("rslt_summary_table" above) has the following columns
+ rows_used -- INTEGER, Number of rows used in the PCA calculation
+ exec_time -- FLOAT, Number of milliseconds the PCA calculation took
+ use_correlation -- BOOLEAN, Value of parameter use_correlation
+ iter -- INTEGER, Number of iterations the SVD took to converge
+ recon_error -- FLOAT, Absolute error in the approximation
+ relative_recon_error -- FLOAT Relative error in the approximation
+ grouping_cols -- The grouping columns (with their types), if any,
+ specified in grouping_cols
""".format(schema_madlib=schema_madlib)
else:
- if message.lower() in ("example", "examples"):
+ if message is not None and \
+ message.lower() in ("example", "examples"):
return """
+----------------------------------------------------------------
+ Examples
+----------------------------------------------------------------
DROP TABLE IF EXISTS sparse_mat;
CREATE TABLE sparse_mat (
row_id integer,
@@ -676,13 +811,46 @@ COPY sparse_mat (row_id, col_id, val_id) FROM stdin delimiter '|';
9|3|4
9|8|2
\.
-DROP TABLE IF EXISTS result_table;
-DROP TABLE IF EXISTS result_table_mean;
-SELECT pca_sparse_train('sparse_mat', 'result_table',
+
+DROP TABLE IF EXISTS result_table_sparse;
+DROP TABLE IF EXISTS result_table_sparse_mean;
+SELECT {schema_madlib}.pca_sparse_train('sparse_mat', 'result_table_sparse',
'row_id', 'col_id', 'val_id', 10, 10, 10);
- """
+
+DROP TABLE IF EXISTS sparse_mat_with_grouping;
+CREATE TABLE sparse_mat_with_grouping (
+ row_id integer,
+ col_id integer,
+ val_id integer,
+ matrix_id integer
+);
+COPY sparse_mat_with_grouping (row_id, col_id, val_id, matrix_id) FROM stdin delimiter '|';
+1|2|4|1
+1|5|6|1
+3|8|4|1
+5|4|2|1
+6|6|12|1
+8|1|2|1
+8|7|2|1
+9|3|4|1
+9|8|2|1
+1|2|4|2
+1|5|6|2
+6|6|12|2
+8|1|2|2
+8|7|2|2
+\.
+
+DROP TABLE IF EXISTS result_table_sparsed_grouped;
+DROP TABLE IF EXISTS result_table_sparsed_grouped_mean;
+SELECT {schema_madlib}.pca_sparse_train('sparse_mat_with_grouping', 'result_table_sparsed_grouped',
+'row_id', 'col_id', 'val_id', 10, 10, 10, 'matrix_id');
+ """.format(schema_madlib=schema_madlib)
else:
return """
+----------------------------------------------------------------
+ Summary: Sparse PCA Training
+----------------------------------------------------------------
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
@@ -692,8 +860,10 @@ 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.
-
-For an overview on usage, run: SELECT {schema_madlib}.pca_sparse_train('usage');
+--
+For an overview on usage, run:
+SELECT {schema_madlib}.pca_sparse_train('usage');
+--
""".format(schema_madlib=schema_madlib)
@@ -711,75 +881,122 @@ def pca_help_message(schema_madlib, message=None, **kwargs):
if message is not None and \
message.lower() in ("usage", "help", "?"):
return """
- -----------------------------------------------------------------------
- USAGE
- -----------------------------------------------------------------------
- SELECT {schema_madlib}.pca_train(
- source_table -- TEXT, Name of data table
- pc_table -- TEXT, Name of the table containing the principle components
- row_id -- TEXT, Column name for the row coordinates.
- components_param -- INTEGER OR FLOAT, The parameter to control the number of principal components to calculate from the input data.
- [
- grouping_cols -- TEXT, Comma-separated list of grouping columns
- (Default: NULL)
- lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation
- (Default: minimum of of the smallest input
- matrix dimension and k+40)
- use_correlation -- BOOLEAN, If True correlation matrix is used for principal components
- (Default: False)
- rslt_summary_table -- TEXT, Table name to store summary of results
- (Default: NULL)
- variance -- DOUBLE PRECISION, Proportion of variance
- (Default: NULL)
- ]
- );
- If components_param is INTEGER it is used for denoting the number of principal components to compute.
- If components_param is FLOAT it is used as the target proportion of variance.
- -------------------------------------------------------------------------
- OUTPUT TABLES
- -------------------------------------------------------------------------
- The output table ("pc_table" above) has the following columns:
- row_id -- INTEGER, The ranking of the eigenvalues
- prin_comp -- FLOAT[], The principal components
- eigen_values -- FLOAT[] The eigenvalues associated with each principal component
-
- A secondary output table named "pc_table"_mean is also generated.
- This table has only the single column:
- column_mean -- FLOAT[], The column means of the input data
- -------------------------------------------------------------------------
- RESULT SUMMARY TABLE
- -------------------------------------------------------------------------
- The result summary table ("rslt_summary_table" above) has the following columns
- rows_used -- INTEGER, Number of rows used in the PCA calculation
- exec_time -- FLOAT, Number of milliseconds the PCA calculation took
- use_correlation -- BOOLEAN, Value of parameter use_correlation
- iter -- INTEGER, Number of iterations the SVD took to converge
- recon_error -- FLOAT, Absolute error in the approximation
- relative_recon_error -- FLOAT Relative error in the approximation
+-----------------------------------------------------------------------
+ USAGE
+-----------------------------------------------------------------------
+SELECT {schema_madlib}.pca_train(
+ source_table -- TEXT, Name of data table
+ pc_table -- TEXT, Name of the table containing the principal components
+ row_id -- TEXT, Column name for the row coordinates.
+ components_param -- INTEGER OR FLOAT, The parameter to control the number of
+ principal components to calculate from
+ the input data.
+
+ grouping_cols -- TEXT, Comma-separated list of grouping column names
+ (Default: NULL)
+ lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation
+ (Default: minimum of of the smallest input
+ matrix dimension and k+40)
+ use_correlation -- BOOLEAN, If True correlation matrix is used for principal components
+ (Default: False)
+ rslt_summary_table -- TEXT, Table name to store summary of results
+ (Default: NULL)
+ variance -- DOUBLE PRECISION, Proportion of variance
+ (Default: NULL)
+);
+If components_param is INTEGER it is used for denoting the number of
+principal components to compute. If components_param is FLOAT it is used
+as the target proportion of variance.
+-------------------------------------------------------------------------
+ OUTPUT TABLES
+-------------------------------------------------------------------------
+A PCA model is created for each group, if grouping_cols is specified.
+The output table ("pc_table" above) has the following columns:
+ row_id -- INTEGER, The ranking of the eigenvalues
+ prin_comp -- FLOAT[], The principal components
+ eigen_values -- FLOAT[], The eigenvalues associated with each
+ principal component
+ grouping_cols -- The grouping columns (with their types), if any,
+ specified in grouping_cols
+
+A secondary output table named "pc_table"_mean is also generated.
+This table has only the single column:
+ column_mean -- FLOAT[], The column means of the input data
+ grouping_cols -- The grouping columns (with their types), if any,
+ specified in grouping_cols
+-------------------------------------------------------------------------
+ RESULT SUMMARY TABLE
+-------------------------------------------------------------------------
+The result summary table ("rslt_summary_table" above) has the following columns
+ rows_used -- INTEGER, Number of rows used in the PCA calculation
+ exec_time -- FLOAT, Number of milliseconds the PCA calculation took
+ use_correlation -- BOOLEAN, Value of parameter use_correlation
+ iter -- INTEGER, Number of iterations the SVD took to converge
+ recon_error -- FLOAT, Absolute error in the approximation
+ relative_recon_error -- FLOAT Relative error in the approximation
+ grouping_cols -- The grouping columns (with their types), if any,
+ specified in grouping_cols
""".format(schema_madlib=schema_madlib)
else:
- if message.lower() in ("example", "examples"):
+ if message is not None and \
+ message.lower() in ("example", "examples"):
return """
+----------------------------------------------------------------
+ Examples
+----------------------------------------------------------------
DROP TABLE IF EXISTS mat;
CREATE TABLE mat (
row_id integer,
row_vec double precision[]
);
COPY mat (row_id, row_vec) FROM stdin DELIMITER '|';
-1|{1,2,3}
-2|{2,1,2}
-3|{3,2,1}
+1|{{1,2,3}}
+2|{{2,1,2}}
+3|{{3,2,1}}
\.
+
DROP TABLE IF EXISTS result_table;
DROP TABLE IF EXISTS result_table_mean;
-SELECT pca_train( 'mat',
- 'result_table',
- 'row_id',
- 3
+SELECT {schema_madlib}.pca_train( 'mat',
+ 'result_table',
+ 'row_id',
+ 3
);
- """
+
+DROP TABLE IF EXISTS mat_with_grouping;
+CREATE TABLE mat_with_grouping (
+ row_id integer,
+ row_vec double precision[],
+ matrix_id integer
+);
+COPY mat_with_grouping (row_id, row_vec, matrix_id) FROM stdin DELIMITER '|';
+1|{{1,2,3}}|1
+2|{{2,1,2}}|1
+3|{{3,2,1}}|1
+4|{{1,2,3,4,5}}|2
+5|{{2,1,2,4,5}}|2
+6|{{3,2,1,4,5}}|2
+7|{{1,2,4,4,5}}|2
+8|{{1,3,3,4,5}}|2
+9|{{2,2,2,4,5}}|2
+10|{{3,3,2,4,5}}|2
+11|{{2,2,3,4,5}}|2
+\.
+
+DROP TABLE IF EXISTS result_table_grp;
+DROP TABLE IF EXISTS result_table_grp_mean;
+SELECT {schema_madlib}.pca_train( 'mat_with_grouping',
+ 'result_table_grp',
+ 'row_id',
+ 0.9,
+ 'matrix_id'
+ );
+ """.format(schema_madlib=schema_madlib)
else:
return """
+----------------------------------------------------------------
+ Summary: PCA Training
+----------------------------------------------------------------
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
@@ -789,6 +1006,8 @@ 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.
-
-For an overview on usage, run: SELECT {schema_madlib}.pca_train('usage');
+--
+For an overview on usage, run:
+SELECT {schema_madlib}.pca_train('usage');
+--
""".format(schema_madlib=schema_madlib)
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/8bd46ae8/src/ports/postgres/modules/pca/pca.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/pca/pca.sql_in b/src/ports/postgres/modules/pca/pca.sql_in
index 9f573f3..3edf304 100644
--- a/src/ports/postgres/modules/pca/pca.sql_in
+++ b/src/ports/postgres/modules/pca/pca.sql_in
@@ -210,18 +210,13 @@ component, but 'components_param' = 1.0 (FLOAT) will return all
principal components, i.e., proportion of variance of 100%.
<DT>grouping_cols (optional)</DT>
-<DD>TEXT, default: NULL.
-
-@note <em>Not currently implemented. Any non-NULL value is ignored.
- Grouping support will be added in a future release. </em> The parameter
- is planned to be implemented as 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.</DD>
+<DD>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.</DD>
<DT>lanczos_iter (optional)</DT>
<DD>INTEGER, default: minimum of {<em>k+40</em>, smallest matrix dimension}
-where <em>k</em> is the number of principle components specified in the
+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
@@ -300,7 +295,7 @@ INSERT INTO mat VALUES
(3, '{3,2,1}');
</pre>
--# Run the PCA function for a specified number of principle components and view the results:
+-# 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',
@@ -333,6 +328,49 @@ SELECT * FROM result_table;
2 | {2.22044604925031e-16,-1,1.11022302462516e-16} | 0.577350269189626 | 0.142857142857041
</pre>
+-# An example of using grouping_cols to learn different models for each group.
+Create sample data in dense matrix form:
+<pre class="example">
+DROP TABLE IF EXISTS mat_with_grouping;
+CREATE TABLE mat_with_grouping (
+ row_id integer,
+ row_vec double precision[],
+ matrix_id integer
+);
+COPY mat_with_grouping (row_id, row_vec, matrix_id) FROM stdin DELIMITER '|';
+1|{1,2,3}|1
+2|{2,1,2}|1
+3|{3,2,1}|1
+4|{1,2,3,4,5}|2
+5|{2,1,2,4,5}|2
+6|{3,2,1,4,5}|2
+7|{1,2,4,4,5}|2
+8|{1,3,3,4,5}|2
+9|{2,2,2,4,5}|2
+10|{3,3,2,4,5}|2
+11|{2,2,3,4,5}|2
+\.
+
+-# Run the PCA function for a specified number of principal components and view the results:
+<pre class="example">
+DROP TABLE IF EXISTS result_table_grp, result_table_grp_mean;
+SELECT {schema_madlib}.pca_train( 'mat_with_grouping',
+ 'result_table_grp',
+ 'row_id',
+ 0.9,
+ 'matrix_id'
+ );
+SELECT * FROM result_table_grp;
+</pre>
+<pre class="result">
+ row_id | principal_components | std_dev | proportion | matrix_id
+--------+------------------------------------------------------------------------------------------------------+-------------------+-------------------+-----------
+ 1 | {-0.707106781186547,1.7650811368064e-16,0.707106781186547} | 1.4142135623731 | 0.857142857142245 | 1
+ 2 | {1.38777878078145e-17,1,4.16333634234434e-17} | 0.577350269189625 | 0.142857142857041 | 1
+ 1 | {0.6597973999259,-0.0411675274295868,-0.750315017666817,-8.77661658627016e-17,-1.98408997564847e-16} | 1.19430528584267 | 0.726149513493974 | 2
+ 2 | {-0.608369791899382,0.114069335981134,0.608369791899382,0.0380149274618831,0.495293291381477} | 1.03132841282174 | 0.541488586593004 | 2
+</pre>
+
-# Create sample data in sparse matrix form:
<pre class="example">
DROP TABLE IF EXISTS sparse_mat;
@@ -357,8 +395,8 @@ INSERT INTO sparse_mat VALUES
<pre class="example">
DROP TABLE IF EXISTS dense_mat;
SELECT madlib.matrix_densify(
- 'sparse_mat',
- 'row=row_id, col=col_id, val=val_id',
+ 'sparse_mat',
+ 'row=row_id, col=col_id, val=val_id',
'dense_mat');
SELECT * FROM dense_mat order by row_id;
</pre>
@@ -376,24 +414,24 @@ SELECT * FROM dense_mat order by row_id;
9 | {0,0,4,0,0,0,0,2}
</pre>
--# Run the PCA sparse function for a specified number of principle components and view the results:
+-# 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(
- 'sparse_mat',
+ 'sparse_mat',
'result_table',
- 'row_id',
- 'col_id',
- 'val_id',
- 9,
- 8,
+ 'row_id',
+ 'col_id',
+ 'val_id',
+ 9,
+ 8,
5);
SELECT * FROM result_table;
</pre>
-Result (with principle components truncated for readability):
+Result (with principal components truncated for readability):
<pre class="result">
- row_id | principal_components | std_dev | proportion
---------+----------------------------------------------------------------------------------------
+ row_id | principal_components | std_dev | proportion
+--------+---------------------------------------------+-------------------+----------------------
1 | {0.0189854059340971,0.0593979357345431,\u2026 | 4.03069474374092 | 0.604208682045711
2 | {0.0346801706473592,-0.536234300404824,\u2026 | 2.42282285507368 | 0.218308410262949
3 | {0.166190350977087,-0.112693750915351,\u2026 | 1.54680674776235 | 0.0889814051004931
@@ -401,6 +439,63 @@ Result (with principle components truncated for readability):
5 | {0.645363366217337,0.0403370697192613,\u2026 | 0.906957663197704 | 0.0305915282045503
</pre>
+-# Create sample data in sparse matrix form, with a grouping column:
+<pre class="example">
+DROP TABLE IF EXISTS sparse_mat_with_grouping;
+CREATE TABLE sparse_mat_with_grouping (
+ row_id integer,
+ col_id integer,
+ val_id integer,
+ matrix_id integer
+);
+COPY sparse_mat_with_grouping (row_id, col_id, val_id, matrix_id) FROM stdin delimiter '|';
+1|2|4|1
+1|5|6|1
+3|8|4|1
+5|4|2|1
+6|6|12|1
+8|1|2|1
+8|7|2|1
+9|3|4|1
+9|8|2|1
+1|2|4|2
+1|5|6|2
+6|6|12|2
+8|1|2|2
+8|7|2|2
+\.
+</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_grouped, result_table_grouped_mean;
+SELECT {schema_madlib}.pca_sparse_train (
+ 'sparse_mat_with_grouping',
+ 'result_table_grouped',
+ 'row_id',
+ 'col_id',
+ 'val_id',
+ 10,
+ 10,
+ 0.9,
+ 'matrix_id'
+ );
+
+SELECT * FROM result_table_grouped;
+</pre>
+Result (with principal components truncated for readability):
+<pre class="result">
+ row_id | principal_components | std_dev | proportion | matrix_id
+--------+----------------------------------------------+------------------+--------------------+-----------
+ 1 | {0.0169618842872617,0.0374636722968646,... | 3.81515913912922 | 0.600912629871244 | 1
+ 2 | {-0.0290315981382334,-0.0981953629958789,... | 1.925316518345 | 0.153034831478482 | 1
+ 3 | {-0.0321284612209643,-0.396464491482199,... | 1.42677134361186 | 0.0840416890026057 | 1
+ 4 | {-4.61214190235381e-16,0.707106781186545,... | 1.33333333333333 | 0.0733944954128442 | 1
+ 1 | {0.0178733514257156,0.0394965128556239,... | 3.80635791249958 | 0.710213752845497 | 2
+ 2 | {0.0417974843144143,0.144805266892335,... | 1.89702728276027 | 0.176407476055726 | 2
+ 3 | {-1.3150650111236,7.0774402964125e-17,... | 1.69008042534288 | 0.140018227653292 | 2
+</pre>
+
@anchor notes
@par Notes
@@ -478,8 +573,6 @@ File pca.sql_in documenting the SQL functions
\ref grp_pca_project
-
-
*/
-- -----------------------------------------------------------------------
@@ -913,3 +1006,31 @@ BEGIN
END;
$$ LANGUAGE plpgsql IMMUTABLE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `');
+
+
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA._pca_union(
+ source_table TEXT, -- Source table name (dense matrix)
+ pc_table TEXT, -- Output table name for the principal components
+ pc_table_mean TEXT, -- Output table name for the principal components
+ row_id TEXT, -- Column name for the ID for each row
+ k INTEGER, -- Number of principal components to compute
+ grouping_cols TEXT, -- Comma-separated list of grouping columns (Default: NULL)
+ lanczos_iter INTEGER, -- The number of Lanczos iterations for the SVD calculation (Default: min(k+40, smallest Matrix dimension))
+ use_correlation BOOLEAN, -- If True correlation matrix is used for principal components (Default: False)
+ result_summary_table TEXT, -- Table name to store summary of results (Default: NULL)
+ result_summary_table_temp TEXT, -- Table name to store summary of results (Default: NULL)
+ variance DOUBLE PRECISION, -- The proportion of variance (Default: NULL)
+ grp_id INTEGER, -- a place holder id for each group
+ grouping_where_clause TEXT, -- WHERE clause using grouping_cols
+ select_grouping_cols TEXT, -- SELECT clause using grouping_cols
+ temp_table_columns TEXT, -- SELECT caluse for creating temporary copy 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, _pca_union)
+$$ LANGUAGE plpythonu
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');