Re: Problem with built-in CORR function

Krzysiek299 schrieb:
> I didn't write that in table I have one more column:
> x | id | no
> --------------
> 2 | 1 | 1
> 5 | 2 | 1
> 2 | 1 | 2
> 3 | 2 | 2
> 3 | 1 | 3
> 4 | 2 | 3
> My mistake, sorry.
> So I have pairs but still dont know how to calculate correlation
> without creating another table.
>

If the NO column determine the ordering it both subsets, then this probably does what you expect:

SELECT corr(x1,x2)
FROM (
SELECT t1.x x1,t2.x x2
FROM mytable t1,mytable t2
WHERE t1.NO = t2.NO AND t1.ID=1 AND t2.ID=2 ORDER BY t1.NO );

The corelation of this set is 0 as i posted earlier.

> Second question is about statement:
> SELECT * FROM (SELECT x AS x1 FROM mytable WHERE id=1),
> (SELECT x AS x2 FROM mytable WHERE id=2);
> result is:
> x1 | x2
> ----------
> 2 | 5
> 2 | 5
> 3 | 5
>
> but x2 should be 5 3 4?? What do You think about it?
>

This returns not the result you have posted, but a cartesian join of both subselects ( i.e. 36 rows with the data provided above).

Best regards

Maxim Received on Sun Apr 16 2006 - 10:41:05 CDT