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.