如何在PostgreSQL中计算多行状态时间间隔并汇总表格

计算需求 #

• 首次cutting状态为1到首次m30状态为1的时间间隔
• m30状态到door状态的时间间隔
• door状态到clamp状态的时间间隔
• clamp状态到下一次cutting状态为1的时间间隔

原始状态数据 #

rid    mak_id       Machine prosessdate       m30 door clamp cutting
507191  552140800   GT2600L 12-7-2023 10:10:13  0   0   0   0
507192  552140800   GT2600L 12-7-2023 10:10:14  0   0   0   1
507193  552140800   GT2600L 12-7-2023 10:10:15  0   0   0   1
507194  552140800   GT2600L 12-7-2023 10:10:16  0   0   0   1
507196  552140800   GT2600L 12-7-2023 10:10:17  0   0   0   0
507197  552140800   GT2600L 12-7-2023 10:10:18  0   0   0   0
507199  552140800   GT2600L 12-7-2023 10:10:19  0   0   0   0
507201  552140800   GT2600L 12-7-2023 10:10:20  1   0   0   0
507202  552140800   GT2600L 12-7-2023 10:10:21  1   0   0   0
507204  552140800   GT2600L 12-7-2023 10:10:22  1   0   0   0
507205  552140800   GT2600L 12-7-2023 10:10:23  1   0   0   0
507207  552140800   GT2600L 12-7-2023 10:10:24  1   0   0   0
507209  552140800   GT2600L 12-7-2023 10:10:25  1   0   0   0
507210  552140800   GT2600L 12-7-2023 10:10:26  1   0   0   0
507212  552140800   GT2600L 12-7-2023 10:10:27  1   0   0   0
507214  552140800   GT2600L 12-7-2023 10:10:28  1   0   0   0
507215  552140800   GT2600L 12-7-2023 10:10:29  1   0   0   0
507217  552140800   GT2600L 12-7-2023 10:10:30  1   0   0   0
507219  552140800   GT2600L 12-7-2023 10:10:31  1   0   0   0
507221  552140800   GT2600L 12-7-2023 10:10:32  1   0   0   0
507223  552140800   GT2600L 12-7-2023 10:10:33  1   0   0   0
507225  552140800   GT2600L 12-7-2023 10:10:34  1   1   0   0
507226  552140800   GT2600L 12-7-2023 10:10:35  1   1   0   0
507228  552140800   GT2600L 12-7-2023 10:10:36  1   1   0   0
507230  552140800   GT2600L 12-7-2023 10:10:37  1   1   1   0
507231  552140800   GT2600L 12-7-2023 10:10:38  1   1   1   0
507232  552140800   GT2600L 12-7-2023 10:10:39  1   1   1   0
507234  552140800   GT2600L 12-7-2023 10:10:40  1   1   1   0
507236  552140800   GT2600L 12-7-2023 10:10:41  1   1   1   0
507237  552140800   GT2600L 12-7-2023 10:10:42  1   1   1   0
507238  552140800   GT2600L 12-7-2023 10:10:43  1   1   0   0
507239  552140800   GT2600L 12-7-2023 10:10:44  1   0   0   0
507240  552140800   GT2600L 12-7-2023 10:10:45  1   0   0   0
507241  552140800   GT2600L 12-7-2023 10:10:46  0   0   0   0
507243  552140800   GT2600L 12-7-2023 10:10:47  0   0   0   0
507245  552140800   GT2600L 12-7-2023 10:10:48  0   0   0   1
507247  552140800   GT2600L 12-7-2023 10:10:49  0   0   0   1
507249  552140800   GT2600L 12-7-2023 10:10:50  0   0   0   1

时间间隔计算结果 #

内容的提问来源于stack exchange，提问作者dimax