Ted Byers wrote:
Please consider the following statement (it becomes
obvious if you remember the important thing about the
table is that it has columns for each of stock_id,
price_date, and price).
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY
price_date DESC LIMIT 1)
UNION
(SELECT * FROM (SELECT * FROM stockprices WHERE
stock_id = 1 ORDER BY price_date DESC LIMIT 22) AS T2
ORDER BY T2.price_date ASC LIMIT 1)
UNION
(SELECT * FROM (SELECT * FROM stockprices WHERE
stock_id = 1 ORDER BY price_date DESC LIMIT 66) AS T3
ORDER BY T3.price_date ASC LIMIT 1)
UNION
(SELECT * FROM (SELECT * FROM stockprices WHERE
stock_id = 1 ORDER BY price_date DESC LIMIT 132) AS T4
ORDER BY T4.price_date ASC LIMIT 1)
UNION
(SELECT * FROM (SELECT * FROM stockprices WHERE
stock_id = 1 ORDER BY price_date DESC LIMIT 264) AS T5
ORDER BY T5.price_date ASC LIMIT 1);
This statement works flawlessly, and is blindingly
fast relative to everything else I have tried. But I
am stuck.
I would have these subselects as -
UNION
(SELECT * FROM stockprices WHERE
stock_id = 1 ORDER BY price_date DESC LIMIT 1 OFFSET 264)
I would expect that to give the same result but make the query plan a
bit simpler and quicker using less memory.
First, while this statement gets me the correct data,
I need to obtain a single record with stock_id,
current price (that obtained from the first select
statement in the union, and each of the prices
returned by the subsequent select statements as a the
current price minus the price at the previous date,
and the result divided by the price at the previous
date, expressed as a percentage. I do not yet know
how to do this using SQL (it would be trivial if I
exported the data to Java or C++ - but it isn't clear
how to do it within SQL).
I haven't tested this but I would start with -
CREATE VIEW stock_price_combined AS
SELECT
stock_id
, (SELECT stock_price FROM stockprices WHERE stock_id = OT.stock_id
ORDER BY price_date DESC LIMIT 1) as orig_price
, (SELECT stock_price FROM stockprices WHERE stock_id = OT.stock_id
ORDER BY price_date DESC LIMIT 1 OFFSET 22) as price_two
, (SELECT stock_price FROM stockprices WHERE stock_id = OT.stock_id
ORDER BY price_date DESC LIMIT 1 OFFSET 66) as price_three
, (SELECT stock_price FROM stockprices WHERE stock_id = OT.stock_id
ORDER BY price_date DESC LIMIT 1 OFFSET 132) as price_four
, (SELECT stock_price FROM stockprices WHERE stock_id = OT.stock_id
ORDER BY price_date DESC LIMIT 1 OFFSET 264) as price_five
FROM stock_prices OT;
Then you can -
SELECT
orig_price
, (orig_price - price_two) as price_increase
, ((orig_price - price_two)/price_two) as percentile
...
...
FROM stock_price_combined
WHERE stock_id in (SELECT stock_id FROM someTable WHERE ...)
To make things more difficult, suppose I have another
select statement that returns a set of stock_ids. How
do I apply the SQL logic I require to only those
stocks in the set returned by a statement like SELECT
stock_id FROM someTable WHERE ... The result of this
extension would be that I have one record for each
stock in the selected set of stocks.
SELECT * from stockprices WHERE stock_id in (SELECT stock_id FROM
someTable WHERE ...)
If that isn't the answer you want I hope it points you in the right
direction...
--
Shane Ambler
pgSQL@xxxxxxxxxx
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
