Developer Forum

Please consider the following statement.

(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 statemet works flawlessly, and is blindingly fast relative to
everything else I have tried. But I am stuck.

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 stament 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).

To make things more difficult, suppose I have another select satement
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 ...

I do NOT want to have to recompute the set of stocks for each of the
select statements in the above union (since that would be a waste
because the result would always be the same for the given criteria).
Nor do I want to apply the SQL logic I need for the prices for all the
stocks in the database. There could be thousands, or even tens of
thousands, of stocks represented and I'd need the gain/loss logic only
for a few dozen at any given time!

How do I make the two extensions I require?

I expect the SQL I get to be eventually placed in a stored procedure,
which may then be used to construct a view, but that is the easy part.

Maybe I have been staring at this for too long to see the obvious
solution, but I am exhausted and am not seeing the next step. If
there IS an obvious next step, please at least give me a hint.

Thanks

Ted

n Nov 16, 11:01 pm, Ted < XXXX@XXXXX.COM > wrote:

Progress!

I have the result I wanted (a single record with profit/loss
calculations) for a single stock. I did so by transforming the union
into a join on the stock_id. E.g.:

SELECT A1.stock_id,
A1.price_date,
A1.adjusted,
A2.price_date,
100.0 * (A1.adjusted - A2.adjusted)/A2.adjusted AS gl22pc,
A3.price_date,
100.0 * (A1.adjusted - A3.adjusted)/A3.adjusted AS gl66pc,
A4.price_date,
100.0 * (A1.adjusted - A4.adjusted)/A4.adjusted AS gl132pc,
A5.price_date,
100.0 * (A1.adjusted - A5.adjusted)/A5.adjusted AS gl264pc
FROM
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1) AS A1
LEFT JOIN
(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) AS
A2
ON A1.stock_id = A2.stock_id
LEFT JOIN
(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) AS
A3
ON A1.stock_id = A3.stock_id
LEFT JOIN
(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)
AS A4
ON A1.stock_id = A4.stock_id
LEFT JOIN
(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)
AS A5
ON A1.stock_id = A5.stock_id;

However, I am at a loss as to how to apply it to a variety of stocks.
Obviously, the logic of the statement requires that it be applied to
only one stock at a time. How, then, do I get a query where I have a
record for each stock that satisfies the condition in a WHERE clause,
with the profit/loss calculate applied to each individually?

Thanks

Ted

ed wrote:

Change all your all subselects to JOINs/LEFT JOINs themselves and it'll
start to become clearer

n Nov 17, 5:03 pm, "Paul Lautman" < XXXX@XXXXX.COM >
wrote:

Thanks Paul, I leanred soemthing today, and almost doubled the speed
of my query. The result is now:

SELECT A1.stock_id,
A1.price_date,
A1.adjusted,
A2.price_date AS pd22,
100.0 * (A1.adjusted - A2.adjusted)/A2.adjusted AS gl22pc,
A3.price_date AS pd66,
100.0 * (A1.adjusted - A3.adjusted)/A3.adjusted AS gl66pc,
A4.price_date AS pd132,
100.0 * (A1.adjusted - A4.adjusted)/A4.adjusted AS gl132pc,
A5.price_date AS pd264,
100.0 * (A1.adjusted - A5.adjusted)/A5.adjusted AS gl264pc
FROM
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1) AS A1
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1 OFFSET 22) AS A2
ON A1.stock_id = A2.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1 OFFSET 66) AS A3
ON A1.stock_id = A3.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1 OFFSET 132) AS A4
ON A1.stock_id = A4.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY price_date DESC
LIMIT 1 OFFSET 264) AS A5
ON A1.stock_id = A5.stock_id;

So I no longer have subselects within the select statements used to
produce derived tables, and each derived table has only one record, so
the left joins are faster.

But I am still not seeing how to apply the logic to each stock
individually from a small selection of stock ids from a very large
pool of stocks that could be chosen. Obviously, if I remove or alter
the WHERE clauses, the logic of the statement is immediately broken.
Where I am making a selection of stock IDs, the table used has simple
records, only two clumns: etf_id and stock_id. An etf is an
investment vehicle where some fund manager has selected what he
considers an optimal suite of stocks for the purpose of the fund).
The primary key uses both columns, and there is an extra index on
stock ID. If I make a mistake, and I get a part of teh planner's plan
resulting in tens of thousands of stocks involved in a left join of a
table with many millions of price records, the cost will be huge and
performance so degraded even my grand nephew will have died of old age
by the time it would be done (or at least he'll have more gray hair
than do I).

I suppose I could construct yet another derived table, giving the
whole statement an alias (by enclosing the who thing in parentheses
and adding "AS OPT"), and then using a join with another select
statement that returns only the required suite of stock IDs, But I
worry about the potential that has for making something that is now
very fast into something that is slow, and I am not sure how to ensure
that the above logic will be applied to each stock individually, with
the output placed in a resultset that has a record for each stock.

I am thinking maybe making my statement above a correlated subquery
(which ought to preserve my logic above, if I properly understand how
subqueries work), but I am at a loss as to how to put the data
returned by such a subquery into the recordset returned by the
composite statement. It would seem my understanding of correlated
subqueries is incomplete. :-(

Can you give me another hint or two? Perhaps with a word of
explanation of correlated subqueries where my understandin

"Ted" < XXXX@XXXXX.COM > schreef in bericht


Ted, please read following, (and above)
http://www.yqcomputer.com/

and change the subselects into JOINs/LEFT JOINS....