# Build a correlation matrix

Shape / inspect

A variant of [Measure correlation](https://meridian.online/operations/measure-correlation).

Correlate every pair of chosen columns at once, giving a matrix of coefficients, covariances or mutual information.

Page: <https://meridian.online/operations/measure-correlation/build-a-correlation-matrix>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

correlate every pair of columns: coefficients, covariances and mutual information

```sql
CREATE TEMP TABLE measures AS SELECT * FROM (VALUES
  (1, 1.0, 3.0, -1.0, 2.0), (2, 2.0, 5.0, -2.0, 1.0), (3, 3.0, 7.0, -3.0, 4.0),
  (4, 4.0, 9.0, -4.0, 3.0), (5, 5.0, 11.0, -5.0, 6.0), (6, 6.0, 13.0, -6.0, 5.0)
) t(id, x, y, z, w);
-- y = 2x + 1 and z = -x; w wobbles around x
CREATE TEMP TABLE cells AS
SELECT id, c.col, c.v FROM measures, LATERAL (VALUES ('x', x), ('y', y), ('z', z), ('w', w)) c(col, v);
-- every ordered pair of columns, once: a correlation and a covariance for each
CREATE TEMP TABLE pairs AS
SELECT a.col AS col_a, b.col AS col_b, round(corr(a.v, b.v), 4) AS r, round(covar_samp(a.v, b.v), 4) AS covariance
FROM cells a JOIN cells b USING (id)
GROUP BY a.col, b.col;
-- the same, as a matrix with a row and a column for each measure
CREATE TEMP TABLE matrix AS
SELECT * FROM (PIVOT pairs ON col_b USING first(r) GROUP BY col_a) ORDER BY col_a;
-- mutual information between categorical columns, in nats: sum of p(a,b) * ln(p(a,b) / (p(a) * p(b)))
CREATE TEMP TABLE categories AS SELECT * FROM (VALUES
  (1, 'p', 'p', 'u'), (2, 'p', 'p', 'v'), (3, 'p', 'p', 'u'), (4, 'p', 'p', 'v'),
  (5, 'q', 'q', 'u'), (6, 'q', 'q', 'v'), (7, 'q', 'q', 'u'), (8, 'q', 'q', 'v')
) t(id, a, copy_of_a, independent);
CREATE TEMP TABLE labels AS
SELECT id, c.col, c.v FROM categories, LATERAL (VALUES ('a', a), ('copy_of_a', copy_of_a), ('independent', independent)) c(col, v);
CREATE TEMP TABLE joint AS
SELECT a.col AS col_a, b.col AS col_b, a.v AS va, b.v AS vb, count(*) AS n
FROM labels a JOIN labels b USING (id) GROUP BY ALL;
CREATE TEMP TABLE mutual_information AS
SELECT col_a, col_b, round(sum(n / total * ln((n / total) / ((na / total) * (nb / total)))), 6) AS mi
FROM (SELECT *, sum(n) OVER (PARTITION BY col_a, col_b) AS total,
             sum(n) OVER (PARTITION BY col_a, col_b, va) AS na,
             sum(n) OVER (PARTITION BY col_a, col_b, vb) AS nb FROM joint)
GROUP BY col_a, col_b;
SELECT * FROM matrix;
SELECT * FROM mutual_information ORDER BY col_a, col_b;
SELECT CASE WHEN ((SELECT count(*) FROM pairs) = 16 AND (SELECT r FROM pairs WHERE col_a = 'x' AND col_b = 'y') = 1.0 AND (SELECT r FROM pairs WHERE col_a = 'x' AND col_b = 'z') = -1.0 AND (SELECT r FROM pairs WHERE col_a = 'x' AND col_b = 'w') = 0.8286 AND (SELECT r FROM pairs WHERE col_a = 'w' AND col_b = 'x') = 0.8286 AND (SELECT r FROM pairs WHERE col_a = 'y' AND col_b = 'y') = 1.0 AND (SELECT covariance FROM pairs WHERE col_a = 'x' AND col_b = 'y') = 7.0 AND (SELECT covariance FROM pairs WHERE col_a = 'x' AND col_b = 'x') = 3.5 AND (SELECT list(col_a ORDER BY col_a) FROM matrix) = ['w', 'x', 'y', 'z'] AND (SELECT list(column_name) FROM (DESCRIBE matrix)) = ['col_a', 'w', 'x', 'y', 'z'] AND (SELECT mi FROM mutual_information WHERE col_a = 'a' AND col_b = 'copy_of_a') = 0.693147 AND (SELECT mi FROM mutual_information WHERE col_a = 'a' AND col_b = 'independent') = 0.0) THEN 'as expected' ELSE error('the correlation matrix, covariances or mutual information were not the expected values: x and y at 1, x and z at -1, a with a copy of itself at ln 2 and with an independent column at 0') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Association Analysis`](https://help.alteryx.com/current/en/designer/tools/data-investigation/association-analysis-tool.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Linear Correlation`](https://api.hub.knime.com/nodes/*0gFnKniNdXkr6DtW?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Rank Correlation`](https://api.hub.knime.com/nodes/*sC8qjjAYkSsMFjOc?details=full) (read 2026-09-27)
- Polars: [`DataFrame.corr`](https://docs.pola.rs/api/python/stable/reference/dataframe/miscellaneous.html) (read 2026-09-27)
- RapidMiner Studio: [`Correlation Matrix`](https://docs.rapidminer.com/latest/studio/operators/modeling/correlations/correlation_matrix.html) (read 2026-09-27)
- RapidMiner Studio: [`Covariance Matrix`](https://docs.rapidminer.com/latest/studio/operators/modeling/correlations/covariance_matrix.html) (read 2026-09-27)
- RapidMiner Studio: [`Mutual Information Matrix`](https://docs.rapidminer.com/latest/studio/operators/modeling/correlations/mututal_information_matrix.html) (read 2026-09-27)