Home » SQL & PL/SQL » SQL & PL/SQL » matrix correlation
matrix correlation Mon, 25 January 2010 08:55
 joshua82 Messages: 31Registered: 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 #440545 is a reply to message #440544] Mon, 25 January 2010 09:03
 Michel Cadot Messages: 64942Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Post a working Test case: create table and insert statements along with the result you want with these data and we will work with your table and data.

Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: matrix correlation [message #440602 is a reply to message #440544] Tue, 26 January 2010 01:14
 _jum Messages: 511Registered: 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: 2101Registered: 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
 Previous Topic: Intersection of a large, unknown number of sets Next Topic: How can I restrict a table For only One Transaction
Goto Forum:

Current Time: Thu Jun 22 09:24:09 CDT 2017

Total time taken to generate the page: 0.06494 seconds