給定帶有一些值的示例表，如下所示：
| a1 | a2 | a3 | a4 | a5 |
|---|---|---|---|---|
| 12 | 15 | 13 | 12 | 21 |
| 14 | 23 | 41 | 57 | 13 |
| 23 | 41 | 57 | 13 | 39 |
| 90 | 81 | 32 | 76 | 78 |
我們的目標是在 Oracle 中使用 SQL 創建相關矩陣。
結果表應該是這樣的：
| # | a1 | a2 | a3 | a4 | a5 |
|---|---|---|---|---|---|
| a1 | 1 | 0.968627486 | -0.023590701 | 0.723566409 | 0.962065691 |
| a2 | 0.968627486 | 1 | 0.214217652 | 0.655979092 | 0.974931214 |
| a3 | -0.023590701 | 0.214217652 | 1 | 0.009891122 | 0.079502115 |
| a4 | 0.723566409 | 0.655979092 | 0.009891122 | 1 | 0.524414529 |
| a5 | 0.962065691 | 0.974931214 | 0.079502115 | 0.524414529 | 1 |
我嘗試了一些公式來計算兩個值的相關系數，但它無濟于事。相關功能也不起作用。
uj5u.com熱心網友回復：
您可以使用以下CORR功能：
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
其中，對于樣本資料：
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;
輸出：
鑰匙 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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/476293.html
上一篇：多對多關系knexjs