Contents

A correlation function is the degree and direction of association of two variables—how well one random variable can be predicted from the other. The coefficient of correlation varies from -1 to 1. A 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.

Correlation Function

The correlation function has the following syntax:

correlation( source_table,
             output_table,
             target_cols,
             verbose
           )

The covariance function, with a similar syntax, can be used to compute the covariance between features.

covariance( source_table,
             output_table,
             target_cols,
             verbose
           )

source_table

TEXT. The name of the data containing the input data.

output_table

TEXT. The name of the table where the cross-correlation matrix will be saved. The output is a table with N+2 columns and N rows, where N is the number of target columns. It contains the following columns.

column_position	The first column is a sequential counter indicating the position of the variable in the 'output_table'.
variable	The second column contains the row-header for the variables.
<...>	The remainder of the table is the NxN correlation matrix for the pairs of numeric 'source_table' columns.

The output table is arranged as a lower-triangular 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 shown in the example below.

SELECT * FROM output_table ORDER BY column_position;

In addition to output table, a summary table named <output_table>_summary is also created at the same time, which has the following columns:

method	'correlation'
source_table	VARCHAR. The data source table name.
output_table	VARCHAR. The output table name.
column_names	VARCHAR. Column names used for correlation computation, comma-separated string.
mean_vector	FLOAT8[]. Vector where each is the mean of a column.
total_rows_processed	BIGINT. Total numbers of rows processed.
total_rows_skipped	BIGINT. Total numbers of rows skipped due to missing values.

target_cols (optional)

TEXT, default: '*'. A comma-separated list of the columns to correlate. If NULL or '*', results are produced for all numeric columns.

verbose (optional)

BOOLEAN, default: FALSE. Print verbose debugging information if TRUE.

Examples

View online help for the correlation function.
```
SELECT madlib.correlation();
```

Create an input data set.

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 VALUES
(1, 'sunny', 85, 85, 'false', 'Dont Play'),
(2, 'sunny', 80, 90, 'true', 'Dont Play'),
(3, 'overcast', 83, 78, 'false', 'Play'),
(4, 'rain', 70, 96, 'false', 'Play'),
(5, 'rain', 68, 80, 'false', 'Play'),
(6, 'rain', 65, 70, 'true', 'Dont Play'),
(7, 'overcast', 64, 65, 'true', 'Play'),
(8, 'sunny', 72, 95, 'false', 'Dont Play'),
(9, 'sunny', 69, 70, 'false', 'Play'),
(10, 'rain', 75, 80, 'false', 'Play'),
(11, 'sunny', 75, 70, 'true', 'Play'),
(12, 'overcast', 72, 90, 'true', 'Play'),
(13, 'overcast', 81, 75, 'false', 'Play'),
(14, 'rain', 71, 80, 'true', 'Dont Play'),
(15, NULL, 100, 100, 'true', NULL),
(16, NULL, 110, 100, 'true', NULL);

Run the correlation() function on the data set.

-- Correlate all numeric columns
SELECT madlib.correlation( 'example_data',
                           'example_data_output'
                         );
-- Setting target_cols to NULL or '*' also correlates all numeric columns
SELECT madlib.correlation( 'example_data',
                           'example_data_output',
                           '*'
                         );
-- Correlate only the temperature and humidity columns
SELECT madlib.correlation( 'example_data',
                           'example_data_output',
                           'temperature, humidity'
                         );

View the correlation matrix.

SELECT * FROM example_data_output ORDER BY column_position;

Result:

 column_position |  variable   |    temperature    | humidity
-----------------+-------------+-------------------+----------
               1 | temperature |               1.0 |
               2 | humidity    | 0.616876934548786 |      1.0
(2 rows)

Compute the covariance of features in the data set.

SELECT madlib.covariance( 'example_data',
                          'cov_output'
                         );

View the covariance matrix.

SELECT * FROM cov_output ORDER BY column_position;

Result:

 column_position |  variable   |    temperature    | humidity
-----------------+-------------+-------------------+----------
               1 | temperature |      146.25       |
               2 | humidity    |      82.125       | 121.1875
(2 rows)

Notes: Current implementation ignores a row that contains NULL entirely. This means any correlation in such a row (with NULLs) does not contribute to the final answer.

Related Topics

File correlation.sql_in documenting the SQL functions

Summary for general descriptive statistics for a table