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

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.

Best,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca