pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedneed to join successive log entries into one
6+ messages / 5 participants
[nested] [flat]
* need to join successive log entries into one
@ 2001-03-14 17:24 George Young <gry@ll.mit.edu>
2001-03-14 21:07 ` Re: need to join successive log entries into one Oliver Elphick <olly@lfix.co.uk>
2001-03-14 21:17 ` Re: need to join successive log entries into one Richard H <dev@archonet.com>
0 siblings, 2 replies; 6+ messages in thread
From: George Young @ 2001-03-14 17:24 UTC (permalink / raw)
To: pgsql-sql
I have a table like:
run | seq | start | done
1415| 261| 2001-01-29 12:36:55| 2001-02-07 13:02:38
1415| 263| 2001-02-14 07:40:04| 2001-02-15 16:05:04
1415| 264| 2001-02-16 16:05:05| 2001-03-08 16:34:03
1415| 265| 2001-03-08 16:34:04|
1747| 257| 2001-02-15 09:14:39| 2001-03-01 08:58:03
1747| 258| 2001-03-01 08:58:04| 2001-03-01 09:01:27
1747| 260| 2001-03-01 09:01:39| 2001-03-01 09:02:39
1747| 262| 2001-03-05 13:13:58|
1954| 218| 2001-02-02 20:55:39| 2001-02-08 12:44:48
1954| 219| 2001-02-08 12:44:49| 2001-02-08 12:47:36
1954| 220| 2001-02-08 12:47:36| 2001-02-08 16:50:33
1954| 221| 2001-02-08 16:50:33| 2001-02-08 16:50:45
1954| 222| 2001-02-08 16:50:46| 2001-02-12 14:36:41
1954| 223| 2001-02-12 14:36:41| 2001-03-02 10:17:15
This is a log of some operations done on some runs. 'seq' is the step
within the run.
I need to produce a new table that coalesces immediately successive
operations on a run into one, e.g.:
run | start | done
1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
1415| 2001-02-14 07:40:04|
1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39
1747| 2001-03-05 13:13:58|
1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15
i.e. where a run has one or more steps with succesive seq values,
or equivalently, with abutting start/end values, then I want
the new table to have only one entry representing the full span of
time. Null 'done' just means it's not done yet.
Unfortunately, the start time of a 'succesive' op is sometimes 1 second
later that the 'done' time of the previous one, so maybe using
the seq field is simpler.
Can anyone think of a way I can do this in postgres?
--
George Young, Rm. L-204 gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts 02420-9108 (781) 981-2756
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need to join successive log entries into one
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
@ 2001-03-14 21:07 ` Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: Oliver Elphick @ 2001-03-14 21:07 UTC (permalink / raw)
To: George Young <gry@ll.mit.edu>; +Cc: pgsql-sql
George Young wrote:
...
>I need to produce a new table that coalesces immediately successive
>operations on a run into one, e.g.:
>
>run | start | done
> 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> 1415| 2001-02-14 07:40:04|
> 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39
> 1747| 2001-03-05 13:13:58|
> 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15
>
>i.e. where a run has one or more steps with succesive seq values,
>or equivalently, with abutting start/end values, then I want
>the new table to have only one entry representing the full span of
>time. Null 'done' just means it's not done yet.
...
>Can anyone think of a way I can do this in postgres?
I think you would be better off feeding the log out of the database into
a perl script. SQL is a set-oriented language that can't do this
sort of thing without some appalling contortions, if at all. (I can't
think of a way to do it.) So, store the data in PostgreSQL, but do
procedural processing in a more appropriate language.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let your light so shine before men, that they may see
your good works, and glorify your Father which is in
heaven." Matthew 5:16
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need to join successive log entries into one
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
@ 2001-03-14 21:17 ` Richard H <dev@archonet.com>
2001-03-14 22:30 ` Re: need to join successive log entries into one George Young <gry@ll.mit.edu>
2001-03-14 23:01 ` Re: need to join successive log entries into one Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 2 replies; 6+ messages in thread
From: Richard H @ 2001-03-14 21:17 UTC (permalink / raw)
To: George Young <gry@ll.mit.edu>; +Cc: pgsql-sql
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 3/14/01, 5:24:12 PM, George Young <gry@ll.mit.edu> wrote regarding [SQL]
need to join successive log entries into one:
> I have a table like:
> run | seq | start | done
> 1415| 261| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> 1415| 263| 2001-02-14 07:40:04| 2001-02-15 16:05:04
> 1415| 264| 2001-02-16 16:05:05| 2001-03-08 16:34:03
> 1415| 265| 2001-03-08 16:34:04|
> This is a log of some operations done on some runs. 'seq' is the step
> within the run.
> I need to produce a new table that coalesces immediately successive
> operations on a run into one, e.g.:
> run | start | done
> 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> 1415| 2001-02-14 07:40:04|
> 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39
> 1747| 2001-03-05 13:13:58|
> 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15
Try:
select run,min(start),max(done) from mytable group by run;
- Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need to join successive log entries into one
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
2001-03-14 21:17 ` Re: need to join successive log entries into one Richard H <dev@archonet.com>
@ 2001-03-14 22:30 ` George Young <gry@ll.mit.edu>
2001-03-15 07:39 ` Re: need to join successive log entries into one Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 6+ messages in thread
From: George Young @ 2001-03-14 22:30 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
On Wed, 14 Mar 2001, you wrote:
> On 3/14/01, 5:24:12 PM, George Young <gry@ll.mit.edu> wrote regarding [SQL]
> I need to join successive log entries into one:
> > I have a table like:
>
> > run | seq | start | done
> > 1415| 261| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > 1415| 263| 2001-02-14 07:40:04| 2001-02-15 16:05:04
> > 1415| 264| 2001-02-16 16:05:05| 2001-03-08 16:34:03
> > 1415| 265| 2001-03-08 16:34:04|
>
> > This is a log of some operations done on some runs. 'seq' is the step
> > within the run.
>
> > I need to produce a new table that coalesces immediately successive
> > operations on a run into one, e.g.:
>
> > run | start | done
> > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > 1415| 2001-02-14 07:40:04|
> > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39
> > 1747| 2001-03-05 13:13:58|
> > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15
>
> Try:
>
> select run,min(start),max(done) from mytable group by run;
Alas, this combines *all* entries for a given run, not just those that
are imediately adjacent (in time, or by 'seq' number)...
--
George Young, Rm. L-204 gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts 02420-9108 (781) 981-2756
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need to join successive log entries into one
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
2001-03-14 21:17 ` Re: need to join successive log entries into one Richard H <dev@archonet.com>
2001-03-14 22:30 ` Re: need to join successive log entries into one George Young <gry@ll.mit.edu>
@ 2001-03-15 07:39 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2001-03-15 07:39 UTC (permalink / raw)
To: George Young <gry@ll.mit.edu>; +Cc: pgsql-sql
George Young wrote:
>
> On Wed, 14 Mar 2001, you wrote:
> > On 3/14/01, 5:24:12 PM, George Young <gry@ll.mit.edu> wrote regarding [SQL]
> > I need to join successive log entries into one:
> > > I have a table like:
> >
> > > run | seq | start | done
> > > 1415| 261| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > > 1415| 263| 2001-02-14 07:40:04| 2001-02-15 16:05:04
> > > 1415| 264| 2001-02-16 16:05:05| 2001-03-08 16:34:03
> > > 1415| 265| 2001-03-08 16:34:04|
> >
> > Try:
> >
> > select run,min(start),max(done) from mytable group by run;
>
> Alas, this combines *all* entries for a given run, not just those that
> are imediately adjacent (in time, or by 'seq' number)...
I thought it was complicated, then I thought it was easy. Looks like I
was right first time.
I was thinking that some huge self-join might do it, but I can't see how
to go beyond a run of two adjacent entries.
The only thing I can think of is to add a "batch" column and build a
trigger to set it as data is inserted. I'm assuming the entries are put
in one at a time and in order. That way you just need to look at the
last entry to determine if the new one is in the same batch.
Any use?
- Richard Huxton
> --
> George Young, Rm. L-204 gry@ll.mit.edu
> MIT Lincoln Laboratory
> 244 Wood St.
> Lexington, Massachusetts 02420-9108 (781) 981-2756
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need to join successive log entries into one
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
2001-03-14 21:17 ` Re: need to join successive log entries into one Richard H <dev@archonet.com>
@ 2001-03-14 23:01 ` Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 0 replies; 6+ messages in thread
From: Ross J. Reedstrom @ 2001-03-14 23:01 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 14, 2001 at 09:17:33PM +0000, Richard H wrote:
>
> > I need to produce a new table that coalesces immediately successive
> > operations on a run into one, e.g.:
>
> > run | start | done
> > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38
> > 1415| 2001-02-14 07:40:04|
> > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39
> > 1747| 2001-03-05 13:13:58|
> > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15
>
> Try:
>
> select run,min(start),max(done) from mytable group by run;
select run,min(start),max(done) from mytable group by run
UNION ALL
select run,start,NULL from mytable where done is null;
Assuming that any given run has only one open step.
Ross
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-03-15 07:39 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-14 17:24 need to join successive log entries into one George Young <gry@ll.mit.edu>
2001-03-14 21:07 ` Oliver Elphick <olly@lfix.co.uk>
2001-03-14 21:17 ` Richard H <dev@archonet.com>
2001-03-14 22:30 ` George Young <gry@ll.mit.edu>
2001-03-15 07:39 ` Richard Huxton <dev@archonet.com>
2001-03-14 23:01 ` Ross J. Reedstrom <reedstrm@rice.edu>
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