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

--- Shane Ambler <pgsql@Sheeky.Biz> wrote:

> 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.
>
It gave apparently correct values, but for some
reason, it insisted on returning thousands upon
thousands of identical record.  There is something
awry there, but I can't place what.  Yes, I know I
could use SELECT DISTINCT, but I worry that it may be
doing a full table scan, as opposed to the relatively
direct lookup I came up with after looking at your
statement.  I don't yet know how long it would take
because it is the slowest option I tied, and I gave up
after it had returned over 10,000 rows and still
showed no signs of finishing.  I don't understand this
as explain returned apparently much better results for
yours than it did for mine.

My latest is as follows:

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;

This still gives me the correct answer, but is faster
still than anything I came up with before.

Now that I have the correct result for one stock, I
need to adapt it to apply to each stock individually,
in some small selection from a large number of stocks.

Thanks again

Ted