pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
need 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