| /* ----------------------------------------------------------------------------- |
| * pca Install Check. |
| * -------------------------------------------------------------------------- */ |
| |
| -- sparse pca Project : Make sure all possible default calls for sparse pca work |
| ----------------------------------------------------------------------------- |
| DROP TABLE IF EXISTS sparse_mat; |
| CREATE TABLE sparse_mat ( |
| row_id integer, |
| col_id integer, |
| val_id integer, |
| grp integer |
| ); |
| COPY sparse_mat (row_id, col_id, val_id, grp) FROM stdin delimiter '|'; |
| 1|2|4|1 |
| 1|5|6|1 |
| 3|8|4|1 |
| 5|4|2|1 |
| 6|6|12|2 |
| 8|7|2|2 |
| 8|1|2|2 |
| 9|8|2|2 |
| 9|3|4|2 |
| \. |
| |
| DROP TABLE IF EXISTS result; |
| CREATE TABLE result ( |
| row_id integer, |
| principal_components double precision[], |
| std_dev double precision, |
| proportion double precision |
| ); |
| |
| DROP TABLE IF EXISTS result_mean; |
| CREATE TABLE result_mean ( |
| column_mean double precision[] |
| ); |
| |
| DROP TABLE IF EXISTS result_group; |
| CREATE TABLE result_group ( |
| row_id integer, |
| principal_components double precision[], |
| std_dev double precision, |
| proportion double precision, |
| grp integer |
| ); |
| |
| DROP TABLE IF EXISTS result_group_mean; |
| CREATE TABLE result_group_mean ( |
| column_mean double precision[], |
| grp integer |
| ); |
| |
| --To reduce runtime, we are inserting the component data instead of calling pca_sparse_train |
| COPY result (row_id, principal_components, std_dev, proportion) FROM stdin delimiter '|'; |
| 1|{0.0999483551416712,0.186293575444218,-0.118253137264426,0.236506274528851,0.926943280943193,-1.62840666505373e-16,-0.0999483551416712,-6.96314261757053e-17,-0.0368062600152977,0.00231445141929613}|4.35282723606613|0.782219929006202 |
| 2|{-0.0171850881648854,-0.0534652518097792,-0.0409281912199027,-0.0166446671936693,-0.0801978777146687,0.992249633847597,-0.0171850881648855,-0.0600634256041047,-2.08166817117217e-17,8.67361737988404e-19}|3.81805344506797|0.601824720112655 |
| 3|{-0.0316385375782742,0.539566703194661,-0.123399728985307,-0.028721304569667,0.809350054791992,0.0773750091851923,-0.0316385375782742,-0.172565839595857,-1.40946282423116e-18,2.37169225231204e-19}|2.29447575235881|0.217346655061755 |
| \. |
| |
| COPY result_mean (column_mean) FROM stdin delimiter '|'; |
| {0.2,0.4,0.4,0.2,0.6,1.2,0.2,0.6,0,0} |
| \. |
| |
| COPY result_group (row_id, principal_components, std_dev, proportion, grp) FROM stdin delimiter '|'; |
| 3|{-0.182544798072401,-0.436977013082767,0.27687531281655,0.280621489401581,-0.68150315453697,-0.105111966780498,-0.14079019991833,0.89798876608506,-0.21827616203899,-0.262932041761337}|4.84986923763189|3.26683772529558|1 |
| 3|{-0.378720182887023,-0.425459238877718,-0.332386677884365,0.0298931936012393,0.229882503707473,-2.17777632543416e-16,-7.20532327823141e-06,0.275690140537054,-0.499114937086524,-0.20670029258349}|2.41708217578269|0.339667804911999|2 |
| 1|{-0.158438766654802,-0.383298969013691,0.240647163470311,0.239561855864886,-0.574948454322478,-0.0927462275046096,-0.122622966374082,0.766597938767636,-0.186702336853992,-0.230349157170997}|54.8848224061402|418.381073687993|1 |
| 2|{-0.1292258497261,-0.31261892453374,0.196276056125631,0.19539886500465,-0.468970115660293,-0.0756428943466703,-0.100012977021065,0.62527636801488,-0.1522832633109,-0.187873463792752} |12.5227817838342|21.7805643896568|1 |
| 1|{-0.470348847404415,-0.528408396316686,-0.412780108669563,0.0371266083475147,0.285511787639426,-2.12437312716349e-16,-1.42413422145714e-10,0.342350268492189,-0.619885623945693,-0.256718881794756}|10.5574225170722|6.48018431418507|2 |
| 2|{-0.019176406118837,-6.85215773010839e-17,-0.0425387417727328,3.84891771232354e-18,3.33934269125535e-17,0.998500175366646,-0.0191764061188369,-0.0212693708863663,-6.33174068731534e-17,-3.68628738645071e-17}|3.79982164552605|0.839456077779552|2 |
| \. |
| |
| COPY result_group_mean (column_mean, grp) FROM stdin delimiter '|'; |
| {0.2,0,0.4,0,0,1.2,0.2,0.2,0,0}|2 |
| {0,0.4,0,0.2,0.6,0,0,0.4,0,0}|1 |
| \. |
| |
| drop table if exists out_table; |
| select pca_sparse_project( 'sparse_mat', |
| 'result', |
| 'out_table', |
| 'row_id', |
| 'col_id', |
| 'val_id', |
| 10, |
| 10); |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| select pca_sparse_project( 'sparse_mat', |
| 'result', |
| 'out_table', |
| 'row_id', |
| 'col_id', |
| 'val_id', |
| 10, |
| 10, |
| 'residual_table'); |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| drop table if exists summary_table; |
| select pca_sparse_project( 'sparse_mat', |
| 'result', |
| 'out_table', |
| 'row_id', |
| 'col_id', |
| 'val_id', |
| 10, |
| 10, |
| 'residual_table', |
| 'summary_table'); |
| |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| drop table if exists summary_table; |
| select pca_sparse_project( 'sparse_mat', |
| 'result_group', |
| 'out_table', |
| 'row_id', |
| 'col_id', |
| 'val_id', |
| 10, |
| 10, |
| 'residual_table', |
| 'summary_table'); |
| |
| |
| ----------------------------------------------------------------------------- |
| -- DENSE pca Project : Make sure all possible default calls for dense pca work |
| -- Test data (Indentical to SVD). See the SVD module for details |
| -- on how the data was generated |
| ----------------------------------------------------------------------------- |
| 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|{396,840,353,446,318,886,15,584,159,383} |
| 2|{691,58,899,163,159,533,604,582,269,390} |
| 3|{293,742,298,75,404,857,941,662,846,2} |
| 4|{462,532,787,265,982,306,600,608,212,885} |
| 5|{304,151,337,387,643,753,603,531,459,652} |
| 6|{327,946,368,943,7,516,272,24,591,204} |
| 7|{877,59,260,302,891,498,710,286,864,675} |
| 8|{458,959,774,376,228,354,300,669,718,565} |
| 9|{824,390,818,844,180,943,424,520,65,913} |
| 10|{882,761,398,688,761,405,125,484,222,873} |
| 11|{528,1,860,18,814,242,314,965,935,809} |
| 12|{492,220,576,289,321,261,173,1,44,241} |
| 13|{415,701,221,503,67,393,479,218,219,916} |
| 14|{350,192,211,633,53,783,30,444,176,932} |
| 15|{909,472,871,695,930,455,398,893,693,838} |
| 16|{739,651,678,577,273,935,661,47,373,618} |
| \. |
| |
| DROP TABLE IF EXISTS mat_group; |
| CREATE TABLE mat_group ( |
| row_id integer, |
| row_vec double precision[], |
| grp integer |
| ); |
| |
| |
| COPY mat_group (row_id, row_vec, grp) FROM stdin delimiter '|'; |
| 1|{396,840,353,446,318,886,15,584,159,383}|1 |
| 2|{691,58,899,163,159,533,604,582,269,390}|1 |
| 3|{293,742,298,75,404,857,941,662,846,2}|1 |
| 4|{462,532,787,265,982,306,600,608,212,885}|1 |
| 5|{304,151,337,387,643,753,603,531,459,652}|1 |
| 6|{327,946,368,943,7,516,272,24,591,204}|1 |
| 7|{877,59,260,302,891,498,710,286,864,675}|1 |
| 8|{458,959,774,376,228,354,300,669,718,565}|2 |
| 9|{824,390,818,844,180,943,424,520,65,913}|2 |
| 10|{882,761,398,688,761,405,125,484,222,873}|2 |
| 11|{528,1,860,18,814,242,314,965,935,809}|2 |
| 12|{492,220,576,289,321,261,173,1,44,241}|2 |
| 13|{415,701,221,503,67,393,479,218,219,916}|2 |
| 14|{350,192,211,633,53,783,30,444,176,932}|2 |
| 15|{909,472,871,695,930,455,398,893,693,838}|2 |
| 16|{739,651,678,577,273,935,661,47,373,618}|2 |
| \. |
| |
| --To reduce runtime, we are inserting the component data instead of calling pca_train |
| TRUNCATE TABLE result, result_mean, result_group, result_group_mean; |
| |
| COPY result (row_id, principal_components, std_dev, proportion) FROM stdin delimiter '|'; |
| 1|{0.16526842327063,-0.366421389980424,0.241612602847798,-0.303119245735438,0.568045953978241,-0.206302218707431,0.178226995160025,0.39309351984262,0.323231747889946,0.179334422182879}|505.194492329765|0.315197084359835 |
| 2|{0.291457976390416,-0.152774780364566,0.158586144860698,0.362871710823108,0.0928706367378119,-0.0669125483419687,-0.366869478896256,0.0124188755831604,-0.459395514735697,0.613182472316484}|388.886052223584|0.18677111487815 |
| 3|{0.07948069143047,0.802423314376242,0.0934862932507145,0.296473514849316,0.227988732332495,-0.0983034126965543,-0.124462345725037,0.278419755665847,0.306654203895387,0.0669010655283283}|319.94755392303|0.126421973507206 |
| \. |
| |
| COPY result_mean (column_mean) FROM stdin delimiter '|'; |
| {559.1875,479.6875,544.3125,450.25,439.4375,570,415.5625,469.875,427.8125,618.5} |
| \. |
| |
| COPY result_group (row_id, principal_components, std_dev, proportion, grp) FROM stdin delimiter '|'; |
| 1|{0.230219314081358,-0.542242124552029,0.147734185781293,-0.322201449600498,0.498178086795134,-0.157790952827593,0.304950076242085,0.153878629599491,0.0189526060640416,0.370434350635706}|592.418326222895|0.415145025936221|1 |
| 2|{0.0198601335248146,0.0353640364279603,0.349333504333401,0.215734993678988,0.0233755640628498,-0.21475528290828,-0.489891255526381,-0.00497765469636668,-0.617779673796083,0.401800052390508}|423.545330298508|0.212198673645447|1 |
| 1|{-0.101488844326835,0.119369931754402,-0.310223993817712,0.19930458504368,-0.501309318267757,0.254412987467252,0.00653916222710583,-0.520818300384267,-0.50072976761573,-0.0377865889188156}|565.35074904599|0.410065096886063|2 |
| 2|{0.435393702288651,0.30470017121216,0.160574409501055,0.532086050032385,0.148045649274778,0.447672416710375,0.19213242272299,0.193225823107265,-0.047547461393774,0.331436976559054}|392.275631914938|0.19742380729566|2 |
| 3|{-0.153642439448284,0.0138170355145617,0.341725537346657,-0.488600076600387,-0.274742978759367,0.241301189217692,0.115147987581317,0.529352987769959,-0.347133032937027,-0.270206878233498}|360.878832274989|0.154051413251267|1 |
| 3|{-0.0529180382877882,-0.77716928473307,-0.159850272773744,0.104984650954637,0.0188949196817422,0.235893903903494,-0.26910834825779,0.244899539920774,-0.21727858095155,0.347887447409336}|321.710775659659|0.132784712370613|2 |
| \. |
| |
| COPY result_group_mean (column_mean, grp) FROM stdin delimiter '|'; |
| {478.571428571,475.428571429,471.714285714,368.714285714,486.285714286,621.285714286,535,468.142857143,485.714285714,455.857142857}|1 |
| {621.888888889,483,600.777777778,513.666666667,403,530.111111111,322.666666667,471.222222222,382.777777778,745}|2 |
| \. |
| |
| drop table if exists out_table; |
| select pca_project( 'mat', |
| 'result', |
| 'out_table', |
| 'row_id'); |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| select pca_project( 'mat', |
| 'result', |
| 'out_table', |
| 'row_id', |
| 'residual_table'); |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| drop table if exists result_summary_table; |
| select pca_project( 'mat', |
| 'result', |
| 'out_table', |
| 'row_id', |
| 'residual_table', |
| 'result_summary_table'); |
| |
| |
| drop table if exists out_table; |
| drop table if exists residual_table; |
| drop table if exists result_summary_table; |
| select pca_project( 'mat_group', |
| 'result_group', |
| 'out_table', |
| 'row_id', |
| 'residual_table', |
| 'result_summary_table'); |
| |
| ----------------------------------------------------------------------------- |
| -- Matrix in the column format |
| ----------------------------------------------------------------------------- |
| DROP TABLE IF EXISTS cmat; |
| CREATE TABLE cmat ( |
| row_id integer, |
| val0 float8, |
| val1 float8, |
| val2 float8, |
| val3 float8, |
| val4 float8, |
| val5 float8, |
| val6 float8, |
| val7 float8, |
| val8 float8, |
| val9 float8 |
| ); |
| |
| COPY cmat (row_id, val0, val1, val2, val3, val4, val5, val6, val7, val8, val9) FROM stdin delimiter '|'; |
| 1|396|840|353|446|318|886|15|584|159|383 |
| 2|691|58|899|163|159|533|604|582|269|390 |
| 3|293|742|298|75|404|857|941|662|846|2 |
| 4|462|532|787|265|982|306|600|608|212|885 |
| 5|304|151|337|387|643|753|603|531|459|652 |
| 6|327|946|368|943|7|516|272|24|591|204 |
| 7|877|59|260|302|891|498|710|286|864|675 |
| 8|458|959|774|376|228|354|300|669|718|565 |
| 9|824|390|818|844|180|943|424|520|65|913 |
| 10|882|761|398|688|761|405|125|484|222|873 |
| 11|528|1|860|18|814|242|314|965|935|809 |
| 12|492|220|576|289|321|261|173|1|44|241 |
| 13|415|701|221|503|67|393|479|218|219|916 |
| 14|350|192|211|633|53|783|30|444|176|932 |
| 15|909|472|871|695|930|455|398|893|693|838 |
| 16|739|651|678|577|273|935|661|47|373|618 |
| \. |
| |
| DROP TABLE IF EXISTS cmat_group; |
| CREATE TABLE cmat_group ( |
| row_id integer, |
| val0 float8, |
| val1 float8, |
| val2 float8, |
| val3 float8, |
| val4 float8, |
| val5 float8, |
| val6 float8, |
| val7 float8, |
| val8 float8, |
| val9 float8, |
| grp integer |
| ); |
| |
| COPY cmat_group (row_id, val0, val1, val2, val3, val4, val5, val6, val7, val8, val9, grp) FROM stdin delimiter '|'; |
| 1|396|840|353|446|318|886|15|584|159|383|1 |
| 2|691|58|899|163|159|533|604|582|269|390|1 |
| 3|293|742|298|75|404|857|941|662|846|2|1 |
| 4|462|532|787|265|982|306|600|608|212|885|1 |
| 5|304|151|337|387|643|753|603|531|459|652|1 |
| 6|327|946|368|943|7|516|272|24|591|204|1 |
| 7|877|59|260|302|891|498|710|286|864|675|2 |
| 8|458|959|774|376|228|354|300|669|718|565|2 |
| 9|824|390|818|844|180|943|424|520|65|913|2 |
| 10|882|761|398|688|761|405|125|484|222|873|2 |
| 11|528|1|860|18|814|242|314|965|935|809|2 |
| 12|492|220|576|289|321|261|173|1|44|241|2 |
| 13|415|701|221|503|67|393|479|218|219|916|2 |
| 14|350|192|211|633|53|783|30|444|176|932|2 |
| 15|909|472|871|695|930|455|398|893|693|838|2 |
| 16|739|651|678|577|273|935|661|47|373|618|2 |
| \. |
| |
| TRUNCATE TABLE result, result_mean, result_group, result_group_mean; |
| |
| COPY result (row_id, principal_components, std_dev, proportion) FROM stdin delimiter '|'; |
| 1|{0.16526842327063,-0.366421389980424,0.241612602847799,-0.303119245735437,0.568045953978241,-0.206302218707431,0.178226995160025,0.393093519842619,0.323231747889946,0.17933442218288}|505.194492329765|0.315197084359835 |
| 2|{-0.291457976390416,0.152774780364567,-0.158586144860697,-0.362871710823108,-0.0928706367378111,0.0669125483419682,0.366869478896256,-0.0124188755831594,0.459395514735699,-0.613182472316484}|388.886052223585|0.186771114878151 |
| 3|{0.0794806914304694,0.802423314376242,0.0934862932507136,0.296473514849315,0.227988732332495,-0.0983034126965556,-0.12446234572504,0.278419755665849,0.306654203895387,0.0669010655283296}|319.94755392303|0.126421973507206 |
| \. |
| |
| COPY result_mean (column_mean) FROM stdin delimiter '|'; |
| {559.1875,479.6875,544.3125,450.25,439.4375,570,415.5625,469.875,427.8125,618.5} |
| \. |
| |
| COPY result_group (row_id, principal_components, std_dev, proportion, grp) FROM stdin delimiter '|'; |
| 3|{0.3126699556027,-0.480455806040447,0.365771876715083,-0.0248784150805745,-0.667928817764757,-0.0833039032046658,0.00145313873349431,-0.108976941380223,-0.0747047624229475,-0.257806871882112}|352.453209760956|0.150716180376676|1 |
| 3|{0.429006141917003,-0.416972314810983,-0.220263975502882,0.166224088396097,0.339803907418546,0.377523866573627,0.385724443710736,-0.395409397739303,-0.0359139495956205,0.0390952300098906}|331.708037339255|0.137638649469556|2 |
| 1|{-0.127725142493489,0.479647904584435,-0.299209594347237,0.369401787380975,-0.431054199595109,0.144578733626064,-0.253105744571601,-0.27991982977847,0.190412606601466,-0.377166794318468}|597.088781627901|0.432548452157205|1 |
| 2|{0.0910325267318057,0.0569658554351505,0.189890670102063,0.394733939534352,0.0453182970026563,-0.260762369447368,-0.54735245135603,-0.210335172851437,-0.454318621438935,0.419884339653369}|452.817321874559|0.248772862162561|1 |
| 1|{0.137934923022718,-0.207524741901,0.211627409235461,-0.233293422691737,0.553242778182427,-0.24082142031348,0.0711398805803833,0.419441528991871,0.541741239578994,0.0121358762879366}|555.362260788629|0.385816806377295|2 |
| 2|{0.312937798955528,0.428753804356753,0.297723295750696,0.498480479798465,0.0642491590756592,0.334813108188443,0.041447271690094,0.38378454149808,-0.0440020653137627,0.337558521488735}|377.340894842065|0.178113223378847|2 |
| \. |
| |
| COPY result_group_mean (column_mean, grp) FROM stdin delimiter '|'; |
| {647.4,440.6,566.7,492.5,451.8,526.9,361.4,452.7,430.9,738}|2 |
| {412.166666667,544.833333333,507,379.833333333,418.833333333,641.833333333,505.833333333,498.5,422.666666667,419.333333333}|1 |
| \. |
| |
| drop table if exists out_table; |
| select pca_project( 'cmat', |
| 'result', |
| 'out_table', |
| 'row_id'); |
| |
| drop table if exists out_table; |
| select pca_project( 'cmat_group', |
| 'result_group', |
| 'out_table', |
| 'row_id'); |
