Home » SQL & PL/SQL » SQL & PL/SQL » matrix correlation
matrix correlation [message #440544] 
Mon, 25 January 2010 08:55 
joshua82
Messages: 31 Registered: December 2009

Member 


Hi!
I'm in a situation like this, where TIT is stock values:
TIT1  TIT2  TIT3
  
1.4  2.1  1.1
1.5  2.2  0.9
1.3  2  1.2
1.5  2  1.1
1.7  2.3  1
and so on...
I need to extract a correlation matrix like this:
1  x  y
x  1  z
y  z  1
or, as a query result, like this:
1,corr(TIT1,TIT2),corr(TIT1,TIT3)
corr(TIT1,TIT2),1,corr(TIT2,TIT3)
thanks!




Re: matrix correlation [message #440602 is a reply to message #440544] 
Tue, 26 January 2010 01:14 
_jum
Messages: 508 Registered: February 2008

Senior Member 


As first step you can have a look at this test case and in the ORACLE documentation for corr:
WITH src AS (SELECT 'Sample1' sample, 1.4 tit1, 2.1 tit2, 1.1 tit3 FROM dual UNION ALL
SELECT 'Sample2' , 1.5 , 2.2 , 0.9 FROM dual UNION ALL
SELECT 'Sample3' , 1.3 , 2.0 , 1.2 FROM dual UNION ALL
SELECT 'Sample4' , 1.5 , 2.0 , 1.1 FROM dual UNION ALL
SELECT 'Sample5' , 1.7 , 2.3 , 1.0 FROM dual)
, pivot AS (SELECT sample
,CASE WHEN lvl = 1 THEN tit1
WHEN lvl = 2 THEN tit2
WHEN lvl = 3 THEN tit3
END val
,lvl measure
FROM src
,(SELECT level lvl FROM dual CONNECT BY LEVEL <=3))
SELECT p1.sample s1
,p2.sample s2
,corr(p1.val,p2.val) corr12
FROM pivot p1
,pivot p2
WHERE p1.sample < p2.sample
AND p1.measure = p2.measure
GROUP BY p1.sample,p2.sample
ORDER BY p1.sample,p2.sample;
S1 S2 CORR12

Sample1 Sample2 0,983378761463739
Sample1 Sample3 0,98354211735048
Sample1 Sample4 0,986761299721012
Sample1 Sample5 0,963411680012698
Sample2 Sample3 0,934389265134278
Sample2 Sample4 0,999806357913305
Sample2 Sample5 0,996062992125984
Sample3 Sample4 0,941218896546689
Sample3 Sample5 0,89912929286506
Sample4 Sample5 0,994125639970616
As @michel wrote, please post a working test case next time, you will get a better and quicker answer.



Re: matrix correlation [message #440626 is a reply to message #440544] 
Tue, 26 January 2010 05:33 

Kevin Meade
Messages: 2100 Registered: December 1999 Location: Connecticut USA

Senior Member 


Oracle 10gR2 sports an API for doing matrix math. Matrix math was never my strong point so maybe this is not what you are asking for but in any event, here is a link.
The package is called UTL_NA and it is supposed to offer basic matrix operations and matrix data types etc.
Quote:The UTL_NLA package exposes a subset of the BLAS and LAPACK (Version 3.0) operations on vectors and matrices represented as VARRAYs.
and then they say:
Quote:Users of this package are expected to have a sound grasp of linear algebra in general and of the BLAS and LAPACK libraries in particular.
so I guess you will have to do some additional reading. Still, if you plan on doing enough of this, or you want something somewhat standardized, or you have some brains and figure it is better not to RYO when the database offers a native feature to do what you want, then this would be the way to go.
Good luck, Kevin



Goto Forum:
Current Time: Fri Oct 28 05:50:10 CDT 2016
Total time taken to generate the page: 0.08444 seconds
