Re: Detecting change in event properties

On 10/25/13, Robert James <srobertjames(at)gmail(dot)com> wrote:
> I have a table of (timed) events, and I'm interested in marking events
> whose properties have changed from the previous event.
>
> I believe this can be done with window functions, but I'm not sure
> how. What window function can give me a field from the _previous_
> row?
>
> (To elaborate, I'm interested in:
> * Finding field x of the _previous_ row
> * Finding field x of the _next_ row
> * Finding field x of the _previous_ row that meets a certain criteria
> (which the current row may or may not meet)
> )

The first two are actually trivial - lag(field_x) over (order by [same
order as query]) and lead(...).

But the last one seems ellusive - How can I find the value of field x
on the previous row WHERE a criteria is met? Is it possible to do this
at all with a window function?