Обсуждение: How should I do this?
Hello,
I'm trying to do a couple of things:
I have a lot of data in a database which is structured as follows:
date: long
value: numeric
delta: numeric
What happens is that sometimes the value and delta fields can be identical
for a long period. This data is used to produce graphs so having a number of
rows with the same value and delta but with different times just wastes space.
How would I make sure that insertions occur only if delta and/or value are
different than the last entry in the table?
Secondly, now I've got a lot of this useless data in my table. What's the best
way to clean it out e.g. if my data is:
1 10 1
2 10 1
3 10 1
4 11 2
5 9 0
6 9 0
7 9 0
8 7 -2
I only want
1 10 1
4 11 2
5 9 0
8 7 -2
left in the table. Note that I'm using the Pgtcl extension and I can script it
in Tcl. My original idea was to do a select to get all the data in the tables
and then go through them one by one and to remove a row if the delta/value is
different from the previous one. But maybe there's a better way?
Thanks for any ideas,
L
--
Laurent Duperval <mailto:lduperval@videotron.ca>
"I realized that the purpose of writing is to inflate ideas, obscure poor
reasoning, and inhibit clarity."
-Calvin
-
Have a look at DISTINCT ON. It works well for this sort of thing. On Wed, Jun 26, 2002 at 06:53:59PM -0400, Laurent Duperval wrote: > Hello, > > I'm trying to do a couple of things: > > I have a lot of data in a database which is structured as follows: > > date: long > value: numeric > delta: numeric > > What happens is that sometimes the value and delta fields can be identical > for a long period. This data is used to produce graphs so having a number of > rows with the same value and delta but with different times just wastes space. > How would I make sure that insertions occur only if delta and/or value are > different than the last entry in the table? > > Secondly, now I've got a lot of this useless data in my table. What's the best > way to clean it out e.g. if my data is: > > 1 10 1 > 2 10 1 > 3 10 1 > 4 11 2 > 5 9 0 > 6 9 0 > 7 9 0 > 8 7 -2 > > I only want > > 1 10 1 > 4 11 2 > 5 9 0 > 8 7 -2 > > left in the table. Note that I'm using the Pgtcl extension and I can script it > in Tcl. My original idea was to do a select to get all the data in the tables > and then go through them one by one and to remove a row if the delta/value is > different from the previous one. But maybe there's a better way? > > Thanks for any ideas, > > L > > -- > Laurent Duperval <mailto:lduperval@videotron.ca> > > "I realized that the purpose of writing is to inflate ideas, obscure poor > reasoning, and inhibit clarity." > -Calvin > - > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Thu, 27 Jun 2002 12:53:21 -0400, Martijn van Oosterhout wrote:
> Have a look at DISTINCT ON. It works well for this sort of thing.
>
Yes, but if I have
1 2 3
2 2 3
3 3 4
4 2 3
5 2 3
I want
1 2 3
3 3 4
4 2 3
left in the database. I think using distint on would yield
1 2 3
3 3 4
only (provided my key isn't part of unique on). But then, I need to read
up a bit more to make sure.
Thanks!
L
--
Laurent Duperval <mailto:lduperval@videotron.ca>
"The proof that IBM didn't invent the car is that it has a steering wheel and
an accelerator instead of spurs and ropes, to be compatible with a horse."
-
On Thu, Jun 27, 2002 at 08:55:23PM -0400, Laurent Duperval wrote: > On Thu, 27 Jun 2002 12:53:21 -0400, Martijn van Oosterhout wrote: > > > Have a look at DISTINCT ON. It works well for this sort of thing. > > > > Yes, but if I have > > 1 2 3 > 2 2 3 > 3 3 4 > 4 2 3 > 5 2 3 > > I want > > 1 2 3 > 3 3 4 > 4 2 3 > > left in the database. I think using distint on would yield > > 1 2 3 > 3 3 4 Ah right. The problem is that DISTINCT ON requires that the fields be distincted on be the same and the first few fields being sorted by. In the hypothetical case you could do: select distinct on (col2) select * from a order by col1; But that isn't allowed. Maybe you will be a stored procedure or something. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
In <20020628121755.A15733@svana.org>, Martijn van Oosterhout wrote: > Ah right. The problem is that DISTINCT ON requires that the fields be > distincted on be the same and the first few fields being sorted by. In the > hypothetical case you could do: > > select distinct on (col2) select * from a order by col1; > > But that isn't allowed. Maybe you will be a stored procedure or something. I ended up doing this on the client side because there isn't a way for the DB to know what I'm trying to do. I also wrote the cleanup stuff on the client side and everything is working up to specs. Thanks! L -- Laurent Duperval <mailto:laurent.duperval@masq.ca> Why are there flotation devices under plane seats instead of parachutes?