Build a correlation matrix

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;