Re: Summing activity intervals without any obvious column to group by

On Mon, Aug 13, 2012 at 5:53 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>wrote:

> On Mon, Aug 13, 2012 at 05:28:24PM -0700, Carey Tilden wrote:
>
> > how to solve it. I have a list of program start/stop times, and I want
> to
> > know how long each run takes to complete. The thing that's really
> tripping
> > me up is there are gaps in the sequence. I've figured out how to
> collapse
> > the results down to a single row per attempt, but I can't quite figure
> out
> > how to further collapse down each full run to its own row. It'd be easy
> if
> > I had a session_id or something to group on, but I don't. All I have are
> > the start/stop times.
>
> Looking at your data and the sample output you provided, it would
> appear that the "real" start time of a program is always the one that
> is started _after_ a completion (or else it's the very first start).
> In other words, it's never the case that a "start" in the program_runs data
> is the start of a new run when an existing, previously-started run
> hasn't completed.
>
> Is that right? If so, then you ought to be able to use windowing
> functions. For each completion, pick the earliest start before it that is
> _after_ a completion or, if there is no such completion, is the very
> first record. See the discussion of windowing functions in the manual
> for how to do this.
>

That's right, but I'm not simply trying to see how long it's been between
the "real" start and the "real" end. I want to exclude the gaps between
runs. I'm looking for how many minutes it spent actually processing.

Carey