Home » SQL & PL/SQL » SQL & PL/SQL » matrix correlation
matrix correlation [message #440544] Mon, 25 January 2010 08:55 Go to next message
Messages: 31
Registered: December 2009
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:

Re: matrix correlation [message #440545 is a reply to message #440544] Mon, 25 January 2010 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64724
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
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.

Re: matrix correlation [message #440602 is a reply to message #440544] Tue, 26 January 2010 01:14 Go to previous messageGo to next message
Messages: 510
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 Go to previous message
Kevin Meade
Messages: 2101
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.

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:

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: Wed Apr 26 05:14:31 CDT 2017

Total time taken to generate the page: 0.18081 seconds