We are trying to add some information to a query over data from a continuous process. Most of what we want can be done quite nicely using window functions, but I got stuck on this particular problem:
The data has records with a timestamp and a few properties that make subsequent rows belong to the same group. Say we have: create table process_data ( timestamp timestamp not null, property_A text not null, property_B text not null, value numeric(12, 3) ); And a query like: select datetime, property_A, property_B , first_value(datetime)::time over run as swap_time --, here I want to enumerate the runs themselves , value from process_data window run as (partition by property_A, property_B order by datetime) ; 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, which would result in something like this: datetime | property_A | property_B | swap_time | run_nr | value ================================================ 2020-09-03 15:06 | tea | earl grey | 15:06 | 1 | 0.23 2020-09-03 15:07 | tea | earl grey | 15:06 | 1 | 0.22 2020-09-03 15:08 | tea | ceylon | 15:08 | 2 | 0.34 2020-09-03 15:09 | coffee | cappucino | 15:09 | 3 | 0.45 2020-09-03 15:10 | coffee | cappucino | 15:09 | 3 | 0.43 etc. Is there a way to achieve this through window functions, or do we need to wrap the thing in a subquery to achieve this? Regards, Alban Hertroys -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
