COMMENT ON AGGREGATE last_value_when (anyelement, bool) IS 'Aggregate
function for tracking the last value when a condition was true';
Here is an example of its use.
, last_value_when(timestamp, measurement_type = 'foo') over w as
, last_value_when(value, measurement_type = 'foo') over w as last_foo_value
WINDOW w AS (
ORDER BY timestamp,
CASE WHEN measurement_type = 'foo' THEN 0 ELSE 1 END
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This gives me for every row the last time/value for foo, no matter how
many rows back it might have been.
For working with time series I've found this incredibly powerful. In
fact I think that it is worth including in Postgres!
But here is my problem. I would like to build equivalents of lead/lag
with the same idea. That is where I currently do lag(some_column, 2)
I'd like to instead be able to say lag_when(some_column, 2,
some_condition). So I could pick out not just the last measurement of
foo, but the last 3 measurements of foo.