Pearson's correlation
Collaboration diagram for Pearson's correlation:

A correlation function is the degree and direction of association of two variables; how well can one random variable be predicted from the other. The coefficient of correlation varies from -1 to 1. Coefficient of 1 implies perfect correlation, 0 means no correlation, and -1 means perfect anti-correlation.

This function provides a cross-correlation matrix for all pairs of numeric columns in a source_table. A Correlation matrix describes correlation among $$M$$ variables. It is a square symmetrical $$M$$x $$M$$ matrix with the $$(ij)$$th element equal to the correlation coefficient between the $$i$$th and the $$j$$th variable. The diagonal elements (correlations of variables with themselves) are always equal to 1.0.

Usage:

Currently the correlation function can be used in the following way:

SELECT {schema_madlib}.correlation
(
source_table        TEXT,       -- Source table name (Required)
output_table        TEXT,       -- Output table name (Required)
target_cols         TEXT,       -- Comma separated columns for which summary is desired
--   (Default: NULL - produces result for all columns)
)


Output will be a table with N+2 columns and N rows, where N is the number of target columns.

• column_position: The first column provides position of the variable in the 'source_table'
• variable: The second column gives the row-header for each variable The rest of the table is the NxN correlation matrix for all pairs of numeric columns in source_table.

The output table is arranged as a lower-traingular matrix with the upper triangle set to NULL and the diagonal elements set to 1.0. To obtain the result from the 'output_table' in this matrix format ensure to order the elements using the 'column_position', as given in the example below.

sql> SELECT * FROM output_table order by column_position;

Examples:
DROP TABLE IF EXISTS example_data;
CREATE TABLE example_data(
id SERIAL,
outlook text,
temperature float8,
humidity float8,
windy text,
class text) ;

INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('sunny', 85, 85, 'false', E'Don\\'t Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('sunny', 80, 90, 'true', E'Don\\'t Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('overcast', 83, 78, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('rain', 70, 96, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('rain', 68, 80, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('rain', 65, 70, 'true', E'Don\\'t Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('overcast', 64, 65, 'true', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('sunny', 72, 95, 'false', E'Don\\'t Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('sunny', 69, 70, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('rain', 75, 80, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('sunny', 75, 70, 'true', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('overcast', 72, 90, 'true', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('overcast', 81, 75, 'false', 'Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('rain', 71, 80, 'true', E'Don\\'t Play');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES(' ', 100, 100, 'true', ' ');
INSERT INTO example_data(outlook, temperature, humidity, windy, class) VALUES('', 110, 100, 'true', '');


    SELECT * from example_data_output order by column_position;