Question

0

205

Views

PostgreSQL query for determining cohorts of data by timestamp and name

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

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-05-12T16:54:02.695Z

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)

0

205

PostgreSQL query for determining cohorts of data by timestamp and name

1 answers

0

Find remote jobs

Andres GPT