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@crankycanuck.ca
