I have a working solution to the problem in the post above but am
wondering if there is a better more efficient way. I was able to get
the first quote simply by virtue of the fact that the table is ordered
by trade_date, and created a temporary table sorted in descending
order by trade_date to get the closing quote. I have a fairly large
data base (almost 400MB) and am wondering if there is a better way
than reverse sorting the whole table into a temporary table.
My approach is below. Comments are welcome since I am trying to
learn/improve my SQL (pun intended).
Thanks,
John Hunter
Here is the master table (minute by minute)
mysql> describe short_quote;
+---------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------------------+-------+
| ticker | varchar(8) | | PRI | | |
| trade_date | datetime | | PRI | 0000-00-00 00:00:00 | |
| price | float | | | 0 | |
| volume | int(11) | | | 0 | |
| creation_time | timestamp(14) | YES | | NULL | |
+---------------+---------------+------+-----+---------------------+-------+
and the table I want to create (minute by minute):
mysql> describe daily;
+------------+------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+------------+-------+
| ticker | varchar(8) | | PRI | | |
| date | date | | PRI | 0000-00-00 | |
| minPrice | float | | | 0 | |
| maxPrice | float | | | 0 | |
| rangePrice | float | | | 0 | |
| stdPrice | float | | | 0 | |
| openPrice | float | | | 0 | |
| closePrice | float | | | 0 | |
| volume | int(11) | | | 0 | |
+------------+------------+------+-----+------------+-------+
9 rows in set (0.00 sec)
And here is the solution I am using.
#reverse the main table to get the daily close into tmp_daily_close
CREATE TABLE tmp_reverse (
ticker VARCHAR(8) not null,
trade_date DATETIME not null,
price FLOAT not null,
PRIMARY KEY (trade_date,ticker)
);
INSERT INTO tmp_reverse (ticker, trade_date, price)
SELECT ticker, trade_date, price
FROM short_quote
ORDER BY trade_date DESC;
#the summary of short quote using SQL summary funcs w
CREATE TABLE tmp_daily_summary (
ticker VARCHAR(8) not null,
date DATE not null,
minPrice FLOAT not null,
maxPrice FLOAT not null,
rangePrice FLOAT not null,
stdPrice FLOAT not null,
volume INTEGER not null,
PRIMARY KEY (date,ticker)
);
#the daily opening by stock and day
CREATE TABLE tmp_daily_open (
ticker VARCHAR(8) not null,
date DATE not null,
openPrice FLOAT not null,
PRIMARY KEY (date,ticker)
);
#the daily close by stock and day
CREATE TABLE tmp_daily_close (
ticker VARCHAR(8) not null,
date DATE not null,
closePrice FLOAT not null,
PRIMARY KEY (date,ticker)
);
#get the summary info from short_quote
INSERT INTO tmp_daily_summary
(ticker, date, minPrice, maxPrice, rangePrice, stdPrice, volume)
SELECT ticker, trade_date,
MIN(price), MAX(price), MAX(price)-MIN(price), STD(price),
SUM(volume)
FROM short_quote
GROUP BY ticker, TO_DAYS(trade_date);
#get the opening price from short_quote
#this works because the quotes are ordered by trade_date
INSERT INTO tmp_daily_open (ticker, date, openPrice)
SELECT ticker, trade_date, price
FROM short_quote
GROUP BY ticker, TO_DAYS(trade_date);
#get the closing price from tmp_reverse
#this works because the quotes are descending (DESC) ordered by trade_date
INSERT INTO tmp_daily_close (ticker, date, closePrice)
SELECT ticker, trade_date, price
FROM tmp_reverse
GROUP BY ticker, TO_DAYS(trade_date);
#join the tables
INSERT INTO daily
SELECT t1.ticker, t1.date, t1.minPrice, t1.maxPrice, t1.rangePrice, t1.stdPrice,
t2.openPrice, t3.closePrice, t1.volume
FROM tmp_daily_summary AS t1, tmp_daily_open AS t2, tmp_daily_close AS t3
WHERE t1.ticker=t2.ticker AND t1.ticker=t3.ticker AND t2.ticker=t3.ticker
AND t1.date=t2.date AND t1.date=t3.date AND t2.date=t3.date;
DROP TABLE tmp_daily_close, tmp_daily_open, tmp_daily_summary, tmp_reverse;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
