桌子：
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
有必要在statusone 的背景關系中按列對所有連續（及時）選項進行配對issue_id。
（timestamp列在構建對時并不重要，但對于了解狀態列中的操作發生的順序很重要。為了了解我們從一個操作到另一個操作有多少時間，有一個delta_time列）
應采用以下形式：
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
怎么看？我認為“CUBE”和“GROUPING SETS”也不適合這個。
不幸的是沒有代碼。
uj5u.com熱心網友回復：
LEAD視窗函式看起來非常適合您的用例。就像是：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454917.html
標籤：PostgreSQL