pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin table with itself?
3+ messages / 2 participants
[nested] [flat]
* join table with itself?
@ 2007-03-15 15:45 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: T E Schmitz @ 2007-03-15 15:45 UTC (permalink / raw)
To: pgsql-sql
I am not getting to grips with the following query:
set-up: Postgresql 8.1
The table HISTORY contains stockmarket data:
DAY HIGH LOW
2007/02/28 6286.1 6166.2
2007/02/27 6434.7 6270.5
2007/02/26 6446.8 6401.5
I'd like to produce the following result:
DAY HIGH LOW DAYS2FALL HIGHEST
where DAYS2FALL is the number of days it takes for LOW to fall below the
present row's LOW
where HIGHEST is the highest HIGH during that period.
I had a stab at DAYS2FALL:
SELECT present.day, present.low, (MIN(future.day)-present.day) as
days2fall FROM history AS present,history AS future
WHERE
present.day < future.day AND
future.low <= present.low
GROUP BY present.day,present.low
ORDER BY days2fall DESC
but didn't manage to express HIGHEST. Also, my attempt isn't exactly the
fastest.
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: join table with itself?
@ 2007-03-15 15:57 William Leite Araújo <william.bh@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: William Leite Araújo @ 2007-03-15 15:57 UTC (permalink / raw)
To: pgsql-sql
On 15/03/07, T E Schmitz <mailreg@numerixtechnology.de> wrote:(...)
Try join the tables.
SELECT present.day, present.low, (MIN(future.day)-present.day) as
>
days2fall FROM history AS present JOIN history AS future ON ( present.day <
> future.day AND
> future.low <= present.low )
> GROUP BY present.day,present.low
> ORDER BY days2fall DESC
--
William Leite Araújo
Analista de Banco de Dados - QualiConsult
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: join table with itself?
@ 2007-03-15 18:54 T E Schmitz <mailreg@numerixtechnology.de>
parent: William Leite Araújo <william.bh@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: T E Schmitz @ 2007-03-15 18:54 UTC (permalink / raw)
To: William Leite Araújo <william.bh@gmail.com>; +Cc: pgsql-sql
William Leite Araújo wrote:
> On 15/03/07, *T E Schmitz* <mailreg@numerixtechnology.de
> <mailto:mailreg@numerixtechnology.de>> wrote:
> (...)
>
> Try join the tables.
>
> SELECT present.day, present.low, (MIN(future.day)-present.day) as
>
> days2fall FROM history AS present JOIN history AS future ON (
> present.day < future.day AND
> future.low <= present.low )
> GROUP BY present.day,present.low
> ORDER BY days2fall DESC
That produces the same result as my previous example but maybe the join
is more efficient, Thank you for the suggestion.
However, I am still stuck as to how to retrieve HIGHEST. The result set
produced by the above query only contains those tuples whose LOW is
lower than present.LOW.
For HIGHEST, I need to look at the rows between present.day and DAYS2FALL:
something like
SELECT MAX (high) from history WHERE day >= present.day AND day <
(present.day + days2fall)
-
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-15 18:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-15 15:45 join table with itself? T E Schmitz <mailreg@numerixtechnology.de>
2007-03-15 15:57 ` William Leite Araújo <william.bh@gmail.com>
2007-03-15 18:54 ` T E Schmitz <mailreg@numerixtechnology.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox