how to create pairs (postgresql)-CodePudding

table:

issue_id  timestamp  status delta_time

1 2022-03-29 06:54:02.000000 Open 0
1 2022-03-29 12:00:02.000000 In Progress 18360
1 2022-03-29 12:09:02.000000 On Hold 540
2 2022-03-29 07:00:02.000000 Open 0
2 2022-03-29 12:42:02.000000 On Hold 20520
3 2022-03-29 07:30:02.000000 Open 0
3 2022-03-29 07:39:03.000000 Done 541
4 2022-03-29 07:39:03.000000 Done 0
4 2022-03-29 12:15:02.000000 Reopened 16559
4 2022-03-29 12:27:02.000000 Done 720
4 2022-03-29 14:21:01.000000 Reopened 6839
4 2022-03-29 15:12:02.000000 Done 3061
5 2022-03-29 07:42:02.000000 Open 0
5 2022-03-29 08:45:03.000000 Done 3781
6 2022-03-29 07:45:02.000000 Open 0
6 2022-03-29 09:48:02.000000 Done 7380
7 2022-03-29 07:45:02.000000 Open 0
7 2022-03-29 07:48:02.000000 Done 180
7 2022-03-29 07:51:02.000000 Reopened 180
7 2022-03-29 07:57:02.000000 Done 360
8 2022-03-29 07:54:02.000000 Open 0
8 2022-03-29 08:45:03.000000 Done 3061

it is necessary to make pairs of all consecutive (in time) options - by status - column in the context of one issue_id.

(the timestamp column is not important when building pairs, but is important for understanding the order in which the operations in the status column occur. In order to understand how much time we have from one operation to another, there is a delta_time column)

should be brought to the following form:

issue_id  pair_status       delta_time 
 1      Open - In Progress     18360  
 1      In Progress - On Hold  540  
 2      Open - On Hold         20520  
 3      Open - Done            541  
 4      Done - Reopened        16559  
 4      Reopened - Done        720  
 4      Done - Reopened        6839
 4      Reopened - Done        3061
 5      Open - Done            3781  
 6      Open - Done            7380  
 7      Open - Done            180  
 7      Done - Reopened        180  
 7      Reopened - Done        360
 8      Open - Done            3061

Which way to look? I think "CUBE" and "GROUPING SETS" is also not suitable for this.

Unfortunately no code.

CodePudding user response：

the LEAD window function looks like a great fit for your use case. Something like:

select subquery.issue_id, 
subquery.old_status || ' - ' || subquery.new_status as pair_status, 
subquery.delta_time from(
    select issue_id, status as old_status, 
    lead(status) over (partition by issue_id order by timestamp) as new_status, 
    lead(delta_time) over (partition by issue_id order by timestamp) as delta_time
    from table) subquery
where subquery.new_status is not null