pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to do this query..
5+ messages / 3 participants
[nested] [flat]
* Best way to do this query..
@ 2006-08-25 20:16 Henry Ortega <juandelacruz@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Henry Ortega @ 2006-08-25 20:16 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to do this query..
@ 2006-08-25 20:21 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2006-08-25 20:21 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to do this query..
@ 2006-08-25 20:40 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Broersma Jr @ 2006-08-25 20:40 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; pgsql-sql
> 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?
I would assume that your data does not change after it is logged. If this is the case, maybe this
part of your data model would actually be a good canidate for an OLAP data model.
If this is not the direction your are enterested in moving, you could also reduce your query
processing time by limiting the data ranges for your existing query to something for reasonable.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to do this query..
@ 2006-08-25 20:43 Henry Ortega <juandelacruz@gmail.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Henry Ortega @ 2006-08-25 20:43 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
Yes the data does not change once it is logged.
I am quite new to this whole thing, do you mind elaborating more
about the OLAP data model you mentioned about?
On 8/25/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> I would assume that your data does not change after it is logged. If this
> is the case, maybe this
> part of your data model would actually be a good canidate for an OLAP data
> model.
>
> If this is not the direction your are enterested in moving, you could
> also reduce your query
> processing time by limiting the data ranges for your existing query to
> something for reasonable.
>
> Regards,
>
> Richard Broersma Jr.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best way to do this query..
@ 2006-08-25 22:59 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Henry Ortega <juandelacruz@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Broersma Jr @ 2006-08-25 22:59 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
> Yes the data does not change once it is logged.
> I am quite new to this whole thing, do you mind elaborating more
> about the OLAP data model you mentioned about?
Well just to give a generalization using an OLAP as your data model, the data is stored in a
denormalized yet defined model. Data integrety is maintained because by definition all records
inserted will remain static.
Here are some additional links that can be used for getting started:
http://en.wikipedia.org/wiki/OLAP
http://www.amazon.com/gp/product/0123695120/sr=8-4/qid=1156546075/ref=pd_bbs_4/002-4041472-4877644?i...
Another alternative is to create a materialized view that will update itself only when records are
inserted or updated.
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-25 22:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-25 20:16 Best way to do this query.. Henry Ortega <juandelacruz@gmail.com>
2006-08-25 20:21 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-08-25 20:40 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-08-25 20:43 ` Henry Ortega <juandelacruz@gmail.com>
2006-08-25 22:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
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