PostgreSQL query for determining cohorts of data by timestamp and name

user_3596855 0
Viewed 44

I have data that has a timestamp and a name like:

ts name
2021-10-01 00:00:00 Place A (123)
2021-10-01 00:01:00 Place A (123)
2021-10-01 00:06:00 Place A (123)
2021-10-01 00:10:00 Place B (234)
2021-10-01 00:13:00 Place B (234)
2021-10-01 00:15:00 Place C (345)
2021-10-01 00:18:00 Place C (345)
2021-10-01 00:23:00 Place C (345)
2021-10-01 00:27:00 Place C (345)
2021-10-01 00:28:00 Place C (345)
2021-10-01 00:29:00 Place C (345)
2021-10-01 00:30:00 Place A (123)
2021-10-01 00:33:00 Place A (123)

I am wanting to build a query that will allow for finding "cohorts" of process cycle sessions where:

  • someone is going through steps of a cycle and will repeat the same process or start a new process
  • data_timestamp is ordered ASC
  • name is the step they are currently in
  • different cycles will have the same name, which can repeat multiple times over time, but may not be in the same order
  • returns the following columns:
    • MIN(data_timestamp) for a name before a different name
    • MAX(data_timestamp) for a name before a different name
    • name

The end result should be something like:

min_data_timestamp max_data_timestamp name
2021-10-01 00:00:00 2021-10-01 00:06:00 Place A (123)
2021-10-01 00:10:00 2021-10-01 00:13:00 Place B (123)
2021-10-01 00:15:00 2021-10-01 00:29:00 Place C (123)
2021-10-01 00:30:00 2021-10-01 00:33:00 Place A (123)

I am assuming some type of windowing query/CTE to do this. I have seen other examples that find start/end time overall for a name or something similar, but not where the name is repeated throughout.

EDIT had a few typos

common-table-expression postgresql sql
1 Answers

You can use row_number with group by:

with pr as (
   select row_number() over (order by id) r, id, name from processes
),
pr1 as (
   select p.*, (select sum(case when p1.r < p.r and p1.name != p.name then 1 end) from pr p1) gid from pr p
)
select min(p.id), max(p.id), max(p.name)
from pr1 p group by p.gid order by case when p.gid is null then 1 else p.gid end;

Output:

min_data_timestamp max_data_timestamp name
2021-10-01 00:00:00 2021-10-01 00:06:00 Place A (123)
2021-10-01 00:10:00 2021-10-01 00:13:00 Place B (234)
2021-10-01 00:15:00 2021-10-01 00:29:00 Place C (345)
2021-10-01 00:30:00 2021-10-01 00:33:00 Place A (123)
user_73267380
Related