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. It is a normalized version of covariance. The Pearson correlation coefficient is used here, which has a value between -1 and 1, where 1 implies total positive linear correlation, 0 means no linear correlation, and -1 means total negative linear correlation.

This function generates an \(N\)x \(N\) cross correlation matrix for pairs of numeric columns in a source_table. It is square symmetrical with the \( (i,j) \)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.

We also provide a covariance function which is similar in nature to correlation, and is a measure of the joint variability of two random variables.

Covariance and Correlation Functions

The correlation function has the following syntax:

correlation( source_table,
             output_table,
             target_cols,
             verbose,
             grouping_cols,
             n_groups_per_run
           )

The covariance function has a similar syntax:

covariance( source_table,
            output_table,
            target_cols,
            verbose,
            grouping_cols,
            n_groups_per_run
          )

source_table

TEXT. Name of the table containing the input data.

output_table

TEXT. Name of the table containing the cross correlation matrix. The output table has N rows, where N is the number of 'target_cols' in the 'source_table' for which correlation or covariance is being computed. It has the following columns:

column_position	An automatically generated sequential counter indicating the order of the variable in the 'output_table'.
variable	Contains the row header for the variables of interest.
grouping_cols	Contains the grouping columns, if any.
<...>	The remainder of the table is the NxN correlation matrix for the pairs of variables of interest.

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' order by 'column_position':

SELECT * FROM output_table ORDER BY column_position;

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

method	'Correlation' or 'Covariance'
source_table	VARCHAR. Data source table name.
output_table	VARCHAR. Output table name.
column_names	VARCHAR. Column names used for correlation computation, as a comma-separated string.
grouping_cols	Contains the grouping columns, if any.
mean_vector	FLOAT8[]. Mean value of column for variables of interest.
total_rows_processed	BIGINT. Total numbers of rows processed.

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 information if TRUE.

grouping_cols (optional)

TEXT, default: NULL. A comma-separated list of the columns to group by.

n_groups_per_run (optional)

INTEGER, default: 10. Number of groups to process at a time. This parameter is ignored if 'grouping_cols' is not specified. Generally the default value will work fine, but there may be cases (see below) where you will want to experiment with it to reduce execution time and memory usage.

Note: This is a lower level parameter that can potentially be used to improve performance, but should be used with caution. It is designed to handle the case where you have a large number of groups. In general, increasing 'n_groups_per_run' means we construct a larger 'UNION ALL' query which uses more memory and may slow down execution if it gets too big. If you have a large number of groups and a smaller data size, there may be benefits to increasing this value. Conversely, decreasing 'n_groups_per_run' means we issue more 'plpy.execute' commands. This increases overhead and can modestly affect the execution time.

Examples

Create an input dataset.

DROP TABLE IF EXISTS example_data CASCADE;
CREATE TABLE example_data(
    id SERIAL,
    outlook TEXT,
    temperature FLOAT8,
    humidity FLOAT8,
    windy TEXT,
    class TEXT,
    day TEXT
);
INSERT INTO example_data VALUES
(1, 'sunny', 85, 85, 'false', 'Dont Play', 'Mon'),
(2, 'sunny', 80, 90, 'true', 'Dont Play', 'Mon'),
(3, 'overcast', 83, 78, 'false', 'Play', 'Mon'),
(4, 'rain', 70, 96, 'false', 'Play', 'Mon'),
(5, 'rain', 68, 80, 'false', 'Play', 'Mon'),
(6, 'rain', 65, 70, 'true', 'Dont Play', 'Mon'),
(7, 'overcast', 64, 65, 'true', 'Play', 'Mon'),
(8, 'sunny', 72, 95, 'false', 'Dont Play', 'Mon'),
(9, 'sunny', 69, 70, 'false', 'Play', 'Mon'),
(10, 'rain', 75, 80, 'false', 'Play', 'Mon'),
(11, 'sunny', 75, 70, 'true', 'Play', 'Mon'),
(12, 'overcast', 72, 90, 'true', 'Play', 'Mon'),
(13, 'overcast', 81, 75, 'false', 'Play', 'Mon'),
(14, 'rain', 71, 80, 'true', 'Dont Play', 'Mon'),
(15, NULL, 100, 100, 'true', NULL, 'Mon'),
(16, NULL, 110, 100, 'true', NULL, 'Mon'),
(101, 'sunny', 85, 85, 'false', 'Dont Play', 'Tues'),
(102, 'sunny', 80, 90, 'true', 'Dont Play', 'Tues'),
(103, 'overcast', 83, 78, 'false', 'Play', 'Tues'),
(104, 'rain', 70, 96, 'false', 'Play', 'Tues'),
(105, 'rain', 68, 80, 'false', 'Play', 'Tues'),
(106, 'rain', 65, 70, 'true', 'Dont Play', 'Tues'),
(107, 'overcast', 64, 65, 'true', 'Play', 'Tues'),
(108, 'sunny', 72, 95, 'false', 'Dont Play', 'Tues'),
(109, 'sunny', 69, 70, 'false', 'Play', 'Tues'),
(110, 'rain', 75, 80, 'false', 'Play', 'Tues'),
(111, 'sunny', 75, 70, 'true', 'Play', 'Tues'),
(112, 'overcast', 72, 90, 'true', 'Play', 'Tues'),
(113, 'overcast', 81, 75, 'false', 'Play', 'Tues'),
(114, 'rain', 71, 80, 'true', 'Dont Play', 'Tues'),
(115, NULL, 100, 100, 'true', NULL, 'Tues'),
(116, NULL, 110, 100, 'true', NULL, 'Tues'),
(201, 'sunny', 85, 85, 'false', 'Dont Play', 'Wed'),
(202, 'sunny', 80, 90, 'true', 'Dont Play', 'Wed'),
(203, 'overcast', 83, 78, 'false', 'Play', 'Wed'),
(204, 'rain', 70, 96, 'false', 'Play', 'Wed'),
(205, 'rain', 68, 80, 'false', 'Play', 'Wed'),
(206, 'rain', 65, 70, 'true', 'Dont Play', 'Wed'),
(207, 'overcast', 64, 65, 'true', 'Play', 'Wed'),
(208, 'sunny', 7, 95, 'false', 'Dont Play', 'Wed'),
(209, 'sunny', 6, 70, 'false', 'Play', 'Wed'),
(210, 'rain', 7, 80, 'false', 'Play', 'Wed'),
(211, 'sunny', 75, 70, 'true', 'Play', 'Wed'),
(212, 'overcast', 72, 90, 'true', 'Play', 'Wed'),
(213, 'overcast', 81, 75, 'false', 'Play', 'Wed'),
(214, 'rain', 71, 80, 'true', 'Dont Play', 'Wed'),
(215, NULL, 10, 100, 'true', NULL, 'Wed'),
(216, NULL, 10, 100, 'true', NULL, 'Wed'),
(217, 'sunny', 85, 85, 'false', 'Dont Play', 'Wed'),
(218, 'sunny', 80, 9, 'true', 'Dont Play', 'Wed'),
(219, 'overcast', 83, 78, 'false', 'Play', 'Wed'),
(220, 'rain', 70, 9, 'false', 'Play', 'Wed'),
(221, 'rain', 68, 80, 'false', 'Play', 'Wed');

Get correlation between temperature and humidity:

DROP TABLE IF EXISTS example_data_output, example_data_output_summary;
SELECT madlib.correlation( 'example_data',
                           'example_data_output',
                           'temperature, humidity'
                         );

View the correlation matrix:

SELECT * FROM example_data_output ORDER BY column_position;

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

View the summary table:

\x on
SELECT * FROM example_data_output_summary;

-[ RECORD 1 ]--------+-----------------------------------
method               | Correlation
source               | example_data
output_table         | example_data_output
column_names         | {temperature,humidity}
mean_vector          | {70.188679245283,79.8679245283019}
total_rows_processed | 53

Correlation with grouping by day:

\x off
DROP TABLE IF EXISTS example_data_output, example_data_output_summary;
SELECT madlib.correlation( 'example_data',
                           'example_data_output',
                           'temperature, humidity',
                           FALSE,
                           'day'
                         );

View the correlation matrix by group:

SELECT * FROM example_data_output ORDER BY day, column_position;

 column_position |  variable   | day  |    temperature    | humidity
-----------------+-------------+------+-------------------+----------
               1 | temperature | Mon  |                 1 |
               2 | humidity    | Mon  | 0.616876934548786 |        1
               1 | temperature | Tues |                 1 |
               2 | humidity    | Tues | 0.616876934548786 |        1
               1 | temperature | Wed  |                 1 |
               2 | humidity    | Wed  | -0.28969669368457 |        1
(6 rows)

View the summary table:

\x on
SELECT * FROM example_data_output_summary ORDER BY day;

-[ RECORD 1 ]--------+------------------------------------
method               | Correlation
source               | example_data
output_table         | example_data_output
column_names         | {temperature,humidity}
day                  | Mon
mean_vector          | {77.5,82.75}
total_rows_processed | 16
-[ RECORD 2 ]--------+------------------------------------
method               | Correlation
source               | example_data
output_table         | example_data_output
column_names         | {temperature,humidity}
day                  | Tues
mean_vector          | {77.5,82.75}
total_rows_processed | 16
-[ RECORD 3 ]--------+------------------------------------
method               | Correlation
source               | example_data
output_table         | example_data_output
column_names         | {temperature,humidity}
day                  | Wed
mean_vector          | {59.0476190476191,75.4761904761905}
total_rows_processed | 21

Get covariance between temperature and humidity:

\x off
DROP TABLE IF EXISTS example_data_output, example_data_output_summary;
SELECT madlib.covariance( 'example_data',
                          'example_data_output',
                          'temperature, humidity'
                         );

View the covariance matrix:

SELECT * FROM example_data_output ORDER BY column_position;

 column_position |  variable   |   temperature    |     humidity
-----------------+-------------+------------------+------------------
               1 | temperature | 507.926664293343 |
               2 | humidity    | 2.40227839088644 | 307.359914560342
(2 rows)

View the summary table:

\x on
SELECT * FROM example_data_output_summary;

-[ RECORD 1 ]--------+-----------------------------------
method               | Covariance
source               | example_data
output_table         | example_data_output
column_names         | {temperature,humidity}
mean_vector          | {70.188679245283,79.8679245283019}
total_rows_processed | 53

Notes

Null values will be replaced by the mean of their respective columns (mean imputation/substitution). Mean imputation is a method in which the missing value on a certain variable is replaced by the mean of the available cases. This method maintains the sample size and is easy to use, but the variability in the data is reduced, so the standard deviations and the variance estimates tend to be underestimated. Please refer to [1] and [2] for details.

If the mean imputation method is not suitable for the target use case, it is advised to employ a view that handles the NULL values prior to calling the correlation/covariance functions.

Literature

[1] https://en.wikipedia.org/wiki/Imputation_(statistics)

[2] https://www.iriseekhout.com/missing-data/missing-data-methods/imputation-methods/

Related Topics

File correlation.sql_in documenting the SQL functions

Summary for general descriptive statistics for a table