Wow! What a trick! Harold, I am ashamed for not remembering that log/exp
technique myself.
Just out of curiosity, what is the difference between these two:
COALESCE(*expression*, 1)
IFNULL(*expression*, 1)
Right off the bat, I know that COALESCE accepts multiple arguments, while
IFNULL always takes two. If I only have two arguments, is there any
advantage in using one or the other from any optimization points of view?
---
Fyodor Golos
Stockworm, Inc.
-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Wednesday, May 26, 2004 8:42 AM
To: hf517@stripped
Cc: mysql@stripped; news
Subject: Re: need help with a complicated join
Harold, you win the "EUREKA" prize of the month!
I had forgotten all about that silly algebraic trick. This answers another
person's post from last week. (I will try to find it again) also looking
for a PRODUCT() function.
I agree about the unknown performance. If you only need to return adjusted
quotes for particular symbols and a date range, you *may* be better off
pre-calcuating the LOG()s in a temp table for what you need to quote. You
can combine that with the stock symbols and the date ranges (from your main
query) to minimize the number of items that the LEFT JOIN ends up
processing.
something like :
CREATE TEMPORARY TABLE tmpSplits
SELECT Symbol, date, log(split_from/split_to) as logadj
FROM splits
WHERE date > *earliest date* AND Symbol in (*list of symbols*)
SELECT q.symbol, q.date, q.quote,
q.quote * COALESCE(exp(sum(ts.logadj)), 1)
FROM quotes q
LEFT JOIN tmpSplits ts ON ts.symbol = q.symbol AND ts.date > q.date
WHERE q.symbol in (*list of symbols*) and q.date > *earliest date*
GROUP BY q.symbol, q.date, q.quote
ORDER BY q.symbol, q.date
only benchmarking will truly show which is faster.
Once again - A HUGE "way-to-go" for the math lesson!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Harald Fuchs
<hf517@protecting To:
mysql@stripped
.net> cc:
Sent by: news Fax to:
<news@stripped Subject: Re: need help with
a complicated join
rg>
05/26/2004 06:00
AM
Please respond to
hf517
In article <puy8nf8r1b.fsf@stripped>,
Harald Fuchs <hf517@stripped> writes:
> SELECT q.symbol, q.date,
> q.quote * product (s.split_from / s.split_to) AS adjusted_quote
> FROM quotes q
> LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date
> GROUP BY q.symbol, q.date, q.quote
> ORDER BY q.symbol, q.date
> The problem is how to define the 'product' aggregate (along the lines
> of 'sum').
[ I like talking to myself :-) ]
A workaround for the missing product aggregate would be
SELECT q.symbol, q.date, q.quote,
q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1))))
FROM quotes q
LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date
GROUP BY q.symbol, q.date, q.quote
ORDER BY q.symbol, q.date
but don't ask me how that performs...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
