Re: Need help with complicated SQL statement - Mailing list pgsql-general

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@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz