From: Andrew Sullivan <ajs@crankycanuck.ca>
To: Henry Ortega <juandelacruz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Best way to do this query..
Date: Fri, 25 Aug 2006 16:21:45 -0400
Message-ID: <20060825202145.GH26439@phlogiston.dyndns.org> (raw)
In-Reply-To: <2bffcc330608251316g6ed00710u6e43b243800c6087@mail.gmail.com>
References: <2bffcc330608251316g6ed00710u6e43b243800c6087@mail.gmail.com>
On Fri, Aug 25, 2006 at 04:16:07PM -0400, Henry Ortega wrote:
> I have the following:
>
> name effective tstamp rate
> John 01-01-2006 2005-12-07 13:39:07.614945 115.00
> John 01-16-2006 2006-01-07 13:39:07.614945 125.00
> John 01-16-2006 2006-01-09 15:13:04.416935 1885.00
>
> I want the output to be:
> name effective end_date rate
> John 01-01-2006 01-15-2006 115.00
> John 01-16-2006 1885.00
>
> What is the best way to do this? This is on a huge table and what I
> have right now is quite slow. Any ideas?
This is not tested, and it's just a sketch. Seems like you need
another column, and probably a trigger that makes decisions on insert
time about whether the column is to be updated. That column would be
an end_date column, default infinity. When an insert comes along,
you DO ALSO update the old row's end_date with a new column. Then
you do DISTINCT ON max(effective) and use a CASE statement to
suppress the infinity on the current rate.
Does that help?
--
Andrew Sullivan | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca, juandelacruz@gmail.com
Subject: Re: Best way to do this query..
In-Reply-To: <20060825202145.GH26439@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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