bladepit wrote:CORR accepts two arguments , such as two NUMBER columns. The result that it returns reflects the correlation over a group (or partition ) which typically includes a large number of rows.
i have a problem. I want to calculate the pearson correlation of two sets which have multiple values. The function CORR(x,y) accepts only two numbers.
How can i handle that?Post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
Is there an efficient way to do that because i have a lot of sets where i want to calculate the correlation.
I hope you can help me.
bladepit wrote:Don't be silly. Of course you can post CREATE TABLE and INSERT statmenents for some sample data. They don't need to have the same names as your real tables and columns, any more than they need to have the same size or the same data as your real tables.
I can't post the table definition
or something else but i hope i can explain my table. I thought this select will make it little bit clearer:What are the results you would want from this small sample data set?
select value, ref, bez from table where (bez = 'A' OR bez = 'B') and ref in (200,201,202....) order by ref;
The value is the important information here. The output could be something like that:
value ref bez
Over the amount of the two sets i want to calculate the pearson.
I think if i put them into an tmptable in my database and execute this sql statement:What is a bez? Why is the number of different bez singnificant in this problem?
select CORR(x,y) from tmptable;
i will get my information but i need to do that for more then 2 million different bez
and i think this will not be very efficient.Where is the data now?
Sorry, it is somehow RAW, behause i Worte this in an iPad, what i am Not Use to do.
With T1 as ( Select Value,ref,bez From table Where Bez ='A' ), T2 as ( Select Value, ref,bez From table Where bez ='B' ) Select Corr(T1.value,t2.value) Over(Order by T1.ref) cor From T1,T2 Wehre T1.ref=T2.ref
You may try to find the tablespace by starting with
select d1.bez , d2.bez d1.value, d2.value from data d1, data d2 where d1.bez <= d2.bez
There willbe one, read the docs on this or ask your administrator.
select * from dba_tablespaces
over40 is an collection with 8767 strings. It throws:
i:=0; over402 := over40; str := over40.first; while str is not null loop str2 := over402.first; while str2 is not null loop if str != str2 AND substidtosubstid.EXISTS(str || ';' || str2) = FALSE AND substidtosubstid.EXISTS(str2 || ';' || str) = FALSE then substidtosubstid(str || ';' || str2) := 'TEST'; --dbms_output.put_line(str || ' ' || str2); end if; str2 := over402.next(str2); end loop; str := over40.next(str); end loop;
Seems that the operating system under the database reachs end of memory?
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)" *Cause: Operating system process private memory has been exhausted *Action: