Re: need help with a complicated join
At 14:07 -0500 on 05/25/2004, <[EMAIL PROTECTED]> wrote about need
help with a complicated join:
I am trying to come up with a query that takes two tables, one with
non-split-adjusted historical stock prices, and one with information on
splits, for instance:
CREATE TABLE quotes (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
quote FLOAT NOT NULL,
PRIMARY KEY (symbol, date),
INDEX (date),
);
INSERT quotes VALUES ("A", "2004-01-01", 3);
INSERT quotes VALUES ("A", "2004-01-02", 3);
INSERT quotes VALUES ("A", "2004-01-03", 3);
INSERT quotes VALUES ("A", "2004-01-04", 3);
INSERT quotes VALUES ("A", "2004-01-05", 2);
INSERT quotes VALUES ("A", "2004-01-06", 2);
INSERT quotes VALUES ("A", "2004-01-07", 2);
INSERT quotes VALUES ("A", "2004-01-08", 1);
INSERT quotes VALUES ("A", "2004-01-09", 1);
CREATE TABLE splits (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
split_from INT UNSIGNEDNOT NULL,
split_toINT UNSIGNEDNOT NULL,
PRIMARY KEY (symbol, date),
);
INSERT splits VALUES ("A", "2004-01-05", 2, 3);
INSERT splits VALUES ("A", "2004-01-08", 1, 2);
I need to be able to pull out split-adjusted quotes, like this:
SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER
BY date;
+++---++
| symbol | date | quote | adjusted_quote |
+++---++
| A | 2004-01-01 | 3 | 1 |
| A | 2004-01-02 | 3 | 1 |
| A | 2004-01-03 | 3 | 1 |
| A | 2004-01-04 | 3 | 1 |
| A | 2004-01-05 | 2 | 1 |
| A | 2004-01-06 | 2 | 1 |
| A | 2004-01-07 | 2 | 1 |
| A | 2004-01-08 | 1 | 1 |
| A | 2004-01-09 | 1 | 1 |
+++---++
Split-adjusting means that on a split date all previous prices are
multiplied by "split_from/split_to" ratio. In my example two splits took
place, one on 2004-01-05, which multiplied all previous prices by 2/3 and
another one on 2004-01-08, which multiplied all previous prices (including
those already affected by first split) by 1/2.
Any help would be appreciated.
Question: Are you doing this direct in MySQL or is it being done as a
Web Inquiry that is doing the MySQL Select "Under the Covers" and
then displaying the result? If the latter, then you can do it by
first building a Temp Table of all records where symbol="A" (fill in
the requested symbol from the user query) AND date=as-of-date (again
supplied by user) creating an adjusted field equal to the quote. The
temp table now has only the requested table rows and ends at the
as-of-date. You then read the splits table for all records dated
before or on the as-of-date and do the updates to the adjusted field
for each adjusted row (you can have the loop update the factor as
needed so you only need to run the table once). Then just read and
display the temp table.
One additional question. From your definition when you say
"non-split-adjusted historical stock prices" I assume that you mean
that on the day a split occurs, that day's quote HAS BEEN adjusted
for the split and all subsequent quotes are based on the status of
the latest split. What you are trying to do with the adjusted column
is adjust for the splits and have the quotes reflect the quote in
terms of a block of stock quoted on day one in the table. IOW: If the
first quote in the table was for 1 share and due to splits that block
is now 5 shares, you want to multiple the todays (1-share) quote by 5
to get a constant based price and do the same on the other day by
using the then current block size as the adjustment factor.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with a complicated join
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 8:42 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; 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 <[EMAIL PROTECTED]To: [EMAIL PROTECTED] .net>cc: Sent by: newsFax to: <[EMAIL PROTECTED] Subject: Re: need help with a complicated join rg> 05/26/2004 06:00 AM Please respond to hf517 In article <[EMAIL PROTECTED]>, Harald Fuchs <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]To: [EMAIL PROTECTED] .net>cc: Sent by: newsFax to: <[EMAIL PROTECTED]Subject: Re: need help with a complicated join rg> 05/26/2004 06:00 AM Please respond to hf517 In article <[EMAIL PROTECTED]>, Harald Fuchs <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with a complicated join
man i didnt even know you can do this AND s.date > q.date i assumed that goes in a where clause ? > -Original Message- > From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs > Sent: Wednesday, May 26, 2004 8:01 PM > To: [EMAIL PROTECTED] > Subject: Re: need help with a complicated join > > > In article <[EMAIL PROTECTED]>, > Harald Fuchs <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a complicated join
You are beyond the realm of SQL. What you would need for something like
this is a dynamically-generated case statement that would apply different
multipliers based on the date of the quote you are trying to adjust. For
those quote values that exist BEFORE multiple splits you must adjust by the
PRODUCT of those splits to achieve your adjusted split.
There is currently no PRODUCT() keyword that is analogous to the SUM()
keyword or you would be able to get your historical multipliers through
sub-selects.
It may be possible to write a UDF that takes the stock symbol and a target
date and computes the adjustment ratio for that date (but I haven't built
one yet so I don't know for sure)
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<[EMAIL PROTECTED]
com> To: <[EMAIL PROTECTED]>
Sent by: "Fyodor cc:
Golos" Fax to:
<[EMAIL PROTECTED]Subject: need help with a complicated
join
com>
05/25/2004 03:07
PM
I am trying to come up with a query that takes two tables, one with
non-split-adjusted historical stock prices, and one with information on
splits, for instance:
CREATE TABLE quotes (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
quote FLOAT NOT NULL,
PRIMARY KEY (symbol, date),
INDEX (date),
);
INSERT quotes VALUES ("A", "2004-01-01", 3);
INSERT quotes VALUES ("A", "2004-01-02", 3);
INSERT quotes VALUES ("A", "2004-01-03", 3);
INSERT quotes VALUES ("A", "2004-01-04", 3);
INSERT quotes VALUES ("A", "2004-01-05", 2);
INSERT quotes VALUES ("A", "2004-01-06", 2);
INSERT quotes VALUES ("A", "2004-01-07", 2);
INSERT quotes VALUES ("A", "2004-01-08", 1);
INSERT quotes VALUES ("A", "2004-01-09", 1);
CREATE TABLE splits (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
split_from INT UNSIGNEDNOT NULL,
split_toINT UNSIGNEDNOT NULL,
PRIMARY KEY (symbol, date),
);
INSERT splits VALUES ("A", "2004-01-05", 2, 3);
INSERT splits VALUES ("A", "2004-01-08", 1, 2);
I need to be able to pull out split-adjusted quotes, like this:
SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER
BY date;
+++---++
| symbol | date | quote | adjusted_quote |
+++---++
| A | 2004-01-01 | 3 | 1 |
| A | 2004-01-02 | 3 | 1 |
| A | 2004-01-03 | 3 | 1 |
| A | 2004-01-04 | 3 | 1 |
| A | 2004-01-05 | 2 | 1 |
| A | 2004-01-06 | 2 | 1 |
| A | 2004-01-07 | 2 | 1 |
| A | 2004-01-08 | 1 | 1 |
| A | 2004-01-09 | 1 | 1 |
+++---++
Split-adjusting means that on a split date all previous prices are
multiplied by "split_from/split_to" ratio. In my example two splits took
place, one on 2004-01-05, which multiplied all previous prices by 2/3 and
another one on 2004-01-08, which multiplied all previous prices (including
those already affected by first split)
need help with a complicated join
I am trying to come up with a query that takes two tables, one with
non-split-adjusted historical stock prices, and one with information on
splits, for instance:
CREATE TABLE quotes (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
quote FLOAT NOT NULL,
PRIMARY KEY (symbol, date),
INDEX (date),
);
INSERT quotes VALUES ("A", "2004-01-01", 3);
INSERT quotes VALUES ("A", "2004-01-02", 3);
INSERT quotes VALUES ("A", "2004-01-03", 3);
INSERT quotes VALUES ("A", "2004-01-04", 3);
INSERT quotes VALUES ("A", "2004-01-05", 2);
INSERT quotes VALUES ("A", "2004-01-06", 2);
INSERT quotes VALUES ("A", "2004-01-07", 2);
INSERT quotes VALUES ("A", "2004-01-08", 1);
INSERT quotes VALUES ("A", "2004-01-09", 1);
CREATE TABLE splits (
symbol VARCHAR(127)NOT NULL,
dateDATENOT NULL,
split_from INT UNSIGNEDNOT NULL,
split_toINT UNSIGNEDNOT NULL,
PRIMARY KEY (symbol, date),
);
INSERT splits VALUES ("A", "2004-01-05", 2, 3);
INSERT splits VALUES ("A", "2004-01-08", 1, 2);
I need to be able to pull out split-adjusted quotes, like this:
SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER
BY date;
+++---++
| symbol | date | quote | adjusted_quote |
+++---++
| A | 2004-01-01 | 3 | 1 |
| A | 2004-01-02 | 3 | 1 |
| A | 2004-01-03 | 3 | 1 |
| A | 2004-01-04 | 3 | 1 |
| A | 2004-01-05 | 2 | 1 |
| A | 2004-01-06 | 2 | 1 |
| A | 2004-01-07 | 2 | 1 |
| A | 2004-01-08 | 1 | 1 |
| A | 2004-01-09 | 1 | 1 |
+++---++
Split-adjusting means that on a split date all previous prices are
multiplied by "split_from/split_to" ratio. In my example two splits took
place, one on 2004-01-05, which multiplied all previous prices by 2/3 and
another one on 2004-01-08, which multiplied all previous prices (including
those already affected by first split) by 1/2.
Any help would be appreciated.
---
Fyodor Golos
Stockworm, Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
