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.
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 )
TEXT. Name of the table containing the input data.
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. |
TEXT, default: '*'. A comma-separated list of the columns to correlate. If NULL or '*'
, results are produced for all numeric columns.
BOOLEAN, default: FALSE. Print verbose information if TRUE.
TEXT, default: NULL. A comma-separated list of the columns to group by.
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');
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
\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
\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
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.
[1] https://en.wikipedia.org/wiki/Imputation_(statistics)
[2] https://www.iriseekhout.com/missing-data/missing-data-methods/imputation-methods/
File correlation.sql_in documenting the SQL functions
Summary for general descriptive statistics for a table