strange behavior of CORR analytic function

From: Rainer Herbst <rainer.herbst_at_sesa.de>
Date: 5 Feb 2002 15:48:09 +0100
Message-ID: <3c5ff0a9@netnews.web.de>

Hi, *!

I was just playing around with Oracle's analytic functions when I came about the CORR-function which calculates the correlation coefficient. Have a look on the following statement:

> select accountid

>	, balance
>	, corr(balance, balance) over (order by accountid rows between 1 


>            preceding and 1 following)

>	as self_correlation

> from accounts
> where accountid < 1200

> ;

I expected to see all self_correlation = 1, but the result looks like:

ACCOUNTID BALANCE SELF_CORRELATION

1	10103	
2	10103	
3	10103	
4	10103	
5	10103	
6	10103	
7	10103	
8	10103	
9	10103	1
10	100029	1
11	10000	1
1000	1100	1
1001	2000	1
1002	2000	
1003	2000	1
1004	3100	1
1005	2000	1
1006	4000	1
1007	2000	1
1008	5000	1
1009	2000	1
1010	6100	1
1011	2000	1
1012	7000	1
1013	2000	1
1014	8001	1
1015	2000	1
1016	9000	1
1017	2000	1
1018	9999	1
1019	2000	1
1020	10000	1
1021	2000	1
1022	1100	1
1023	2000	1
1024	2000	
1025	2000	1

...

I do understand that there might be NULL values at the beginning of the result set ( but why 8 and not only 1? I have set preceding to 1! ) But why I see a NULL value in every 22 row beginning from 14?

What is the problem - my understanding about the CORR-function or the behavior of the function itself?

Regards!
Rainer Herbst

P.S. I used Oracle8i Enterprise Edition Release 8.1.7.0.1 on SuSE 7.0

-- 
__________________________________________________________
News suchen, lesen, schreiben mit http://newsgroups.web.de

Received on Tue Feb 05 2002 - 08:48:09 CST