Given sample table with some values like this:

Our goal is to create correlation matrix using SQL in Oracle.

The result table should be like this:

I have try some formula for calculate correlation coefficient of two values, but it can't help. Also correlation function doesn't work.

CodePudding user response：

You can use the CORR function:

WITH ordered (rn, a1, a2, a3, a4, a5) AS (
  SELECT ROWNUM, a1, a2, a3, a4, a5 FROM table_name
)
SELECT key,
       CORR(a1, value) AS a1,
       CORR(a2, value) AS a2,
       CORR(a3, value) AS a3,
       CORR(a4, value) AS a4,
       CORR(a5, value) AS a5
FROM   ordered t
       INNER JOIN (
         SELECT *
         FROM   ordered
         UNPIVOT (
           value FOR key IN (a1, a2, a3, a4, a5)
         )
       ) d
       ON (t.rn = d.rn)
GROUP BY key

Which, for the sample data:

CREATE TABLE table_name (a1, a2, a3, a4, a5) AS
SELECT 12, 15, 13, 12, 21 FROM DUAL UNION ALL
SELECT 14, 23, 41, 57, 13 FROM DUAL UNION ALL
SELECT 23, 41, 57, 13, 39 FROM DUAL UNION ALL
SELECT 90, 81, 32, 76, 78 FROM DUAL;

Outputs:

KEY	A1	A2	A3	A4	A5
A1	1	.9686274863493618022648973908306711860158	-.0235907009791586343670275607353945975836	.7235664093128934907375377255687028401814	.9620656911630149496690023500551903300058
A2	.9686274863493618022648973908306711860158	1	.2142176524658728558977962209541976877097	.655979092200627387045632202651644194262	.9749312135706365317565525851529784515689
A3	-.0235907009791586343670275607353945975836	.2142176524658728558977962209541976877097	1	.009891122282267634886059354456616140169476	.079502115405919407497390073647822157651
A4	.7235664093128934907375377255687028401814	.655979092200627387045632202651644194262	.009891122282267634886059354456616140169476	1	.5244145293486588849105425925771231892634
A5	.9620656911630149496690023500551903300058	.9749312135706365317565525851529784515689	.079502115405919407497390073647822157651	.5244145293486588849105425925771231892634	1

db<>fiddle here