correlation between rows

correlation between rows.sql

SELECT CORR(a.value, b.value), a.rowid, b.rowid
FROM
(
SELECT column, value, rowid FROM
(
SELECT 'c1' column, c1 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
(
SELECT 'c2' column, c2 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
(
SELECT 'c3' column, c3 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
) a
JOIN
(
SELECT column, value, rowid FROM
(
SELECT 'c1' column, c1 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
(
SELECT 'c2' column, c2 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
(
SELECT 'c3' column, c3 AS value, rowid
FROM
(SELECT 1 c1, 2 c2, 3 c3, 'id1' rowid), (SELECT 9 c1, 2 c2, 3 c3, 'id2' rowid), (SELECT 4 c1, 1 c2, 3 c3, 'id3' rowid)
),
) b
ON a.column=b.column
WHERE a.rowid < b.rowid
GROUP BY a.rowid, b.rowid

#http://stackoverflow.com/questions/25588916/how-to-use-bigquery-correlation-based-on-many-columns/25610878?noredirect=1#comment40009429_25610878