Very Computer

by **John Hunt** » Sun, 02 Sep 2001 08:13:54

I have a database of minute by minute stock prices and I would like to
summarize daily information in a 'daily' table, which contains
information about the max, min, range of prices, total volume, etc...

This works so far:

CREATE TABLE daily (
ticker VARCHAR(8) not null,
date DATE not null,
minPrice FLOAT not null,
maxPrice FLOAT not null,
range FLOAT not null,
volume INTEGER not null,
PRIMARY KEY (date,ticker)
);

INSERT INTO daily
SELECT ticker, trade_date, MIN(price), MAX(price), MAX(price)-MIN(price), SUM(volume)
FROM short_quote
WHERE trade_date<'2001-06-25'
GROUP BY ticker, TO_DAYS(trade_date);

However, I would also like to get the open and closing prices, ie, the
first and last price for each day. Is there a way of getting the
price where trade_date=max(trade_date) within the given GROUP BY
ticker/day.

Thanks,
John Hunter

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

by **John Hunt** » Mon, 03 Sep 2001 03:25:42

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

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)

Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Very Computer

creating a new table from existing one

creating a new table from existing one

creating a new table from existing one