MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2013

Re: Where are the CORRELATION, VARIANCE, and COVARIANCE functions in IBM i DB2



fixed

No problem.

I do not think the cross joins should be a problem, as there is only one
record produced from each of the CTE's as long as you are aggregating
across the entire table. If there is any need for a group by clause, then
it might be cause for concern and should be coded in a manner to not cause
performance problems. :-)

Tom
On Dec 3, 2013 6:32 PM, "Vernon Hamberg" <vhamberg@xxxxxxxxxxxxxxx> wrote:

My apologies, Tom - your reference to LUW was confusing.

These should work - I had thought that what the OP wanted should be
capable of being expressed in terms of simpler, existing functions.

Unless you really want the cross join that this will produce - I think!!
- you might want to use true JOIN syntax instead of the table list with
no WHERE clause to define the JOINs.

Nicely done - thanks!

Vern

On 12/3/2013 5:55 PM, Tom Stieger wrote:
Can I ask why my code won't work? It is just using standard DB2 for i
aggregate functions to emulate what those standard statistical function
do.
I don't know statistics very well, but a brief look online should me that
my calculations seem to match the standard definitions. DISCLAIMER: EVEN
IF
MY CODE DOES WORK I DON'T GUARANTEE IT IS CORRECT.

On your point about looking at DB2 LUW documentation, I agree. Usually
only two things can come of it: confusion as to why something doesn't
work
or anger as to why that isn't in DB2 for i yet!

-Tom


On Tue, Dec 3, 2013 at 2:58 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx
wrote:

This will not work on IBM i, which is the flavor of DB2 the OP is asking
about.

Documentation for DB2 LUW (Linux-Unix-Windows) is generally not
something I recommend to anyone running on IBM i. It will probably end
up just confusing a person.

JMHO
Vern

On 12/3/2013 3:08 PM, Tom Stieger wrote:
Based on the links for DB2 LUW, the following should get you what you
need.
WITH
avgcte as (SELECT AVG(exp1) as avgexp1, AVG(exp2) as avgexp2 FROM
table1),
covarcte as (SELECT AVG((exp1 - avgexp1)*(exp2-avgexp2)) as covarvalue
FROM
table1, avgcte),
corrcte as (SELECT covarvalue/(STDDEV(exp1)*STDDEV(exp2)) as corrvalue
FROM
table1, covarcte)

SELECT * from avgcte, covarcte, corrcte

I can't test this out right now, but it looks like it might work to me.

-Tom Stieger
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact