Re: How to enumerate partitions from a window function?

Alban Hertroys <haramrae(at)gmail(dot)com> writes:
> As stated above, I want to enumerate the runs, starting at 1 and
> incrementing by 1 every time a partition from the 'run' window closes,
> Is there a way to achieve this through window functions, or do we need to
> wrap the thing in a subquery to achieve this?

I think this'll work:

select datetime, property_A, property_B
, first_value(datetime)::time over run as swap_time
, dense_rank() over (order by property_A, property_B)
, value
from process_data
window run as (partition by property_A, property_B order by datetime)
;

You can't do it with a window function over the "run" window because
no window function ever looks outside the current partition. But
that's easy to fix by using a different window definition. The
planner is smart enough to see that these windows are compatible
and only need one sort to be performed.

regards, tom lane