Re: need help with a complicated join

[SGreen]

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