如何计算间隔重复出现的ActivityName的最小/最大ActionDatetime及最后USERID
2026-7-23
需求：按ActivityName连续周期分组计算时间极值及最后操作人
数据表A结构及数据
数据表A包含字段：LOGID、PROCESSINSTANCEID、USERID、ActionDatetime、ActivityName，具体数据如下：
| LOGID | PROCESSINSTANCEID | USERID | ActionDatetime | ActivityName |
|---|---|---|---|---|
| 20,450 | COB-0000002024-process | 23 | 7/14/2021 11:17:37 AM | Branch_Maker |
| 20,451 | COB-0000002024-process | 23 | 7/14/2021 11:17:37 AM | Branch_Maker |
| 20,456 | COB-0000002024-process | 23 | 7/14/2021 12:17:47 PM | Branch_Maker |
| 20,464 | COB-0000002024-process | 6 | 7/14/2021 12:18:42 PM | Compliance_Review |
| 20,467 | COB-0000002024-process | 6 | 7/14/2021 12:19:12 PM | Compliance_Review |
| 20,475 | COB-0000002024-process | 6 | 7/14/2021 12:19:19 PM | Branch_Maker_Correction |
| 20,476 | COB-0000002024-process | 6 | 7/14/2021 12:19:19 PM | Branch_Maker_Correction |
| 20,481 | COB-0000002024-process | 23 | 7/14/2021 12:20:41 PM | Branch_Maker_Correction |
| 20,491 | COB-0000002024-process | 24 | 7/14/2021 12:25:35 PM | Branch_Checker |
| 20,492 | COB-0000002024-process | 24 | 7/14/2021 12:25:35 PM | Branch_Checker |
| 20,493 | COB-0000002024-process | 24 | 7/14/2021 12:26:42 PM | Branch_Checker |
| 20,494 | COB-0000002024-process | 24 | 7/14/2021 12:26:42 PM | Branch_Checker |
| 20,500 | COB-0000002024-process | 6 | 7/14/2021 12:27:54 PM | AMC_Maker |
| 20,579 | COB-0000002024-process | 6 | 7/15/2021 1:17:05 PM | AMC_Maker |
| 20,580 | COB-0000002024-process | 24 | 7/14/2021 1:18:05 PM | Branch_Checker |
| 20,581 | COB-0000002024-process | 24 | 7/14/2021 1:27:05 PM | Branch_Checker |
| 20,586 | COB-0000002024-process | 6 | 7/15/2021 1:37:22 PM | AMC_Checker |
| 20,587 | COB-0000002024-process | 6 | 7/15/2021 1:47:22 PM | AMC_Checker |
需求说明
计算ActionDatetime的最小值（Min）和最大值（Max）：
- 当
ActivityName间隔出现时，按其每次连续出现的周期分组 - 每组需计算该周期内的Min ActionDatetime和Max ActionDatetime
- 同时显示每个周期对应
ActivityName的最后一个USERID
期望输出结果
| PROCESSINSTANCEID | USERID | min ActionDatetime | max ActionDatetime | ActivityName |
|---|---|---|---|---|
| COB-0000002024-process | 24 | 7/14/2021 11:17:37 AM | 7/14/2021 12:17:47 PM | Branch_Maker |
| COB-0000002024-process | 6 | 7/14/2021 12:18:42 PM | 7/14/2021 12:19:12 PM | Compliance_Review |
| COB-0000002024-process | 24 | 7/14/2021 12:19:19 PM | 7/14/2021 12:20:41 PM | Branch_Maker_Correction |
| COB-0000002024-process | 32 | 7/14/2021 12:25:35 PM | 7/14/2021 12:26:42 PM | Branch_Checker |
| COB-0000002024-process | 6 | 7/14/2021 12:27:54 PM | 7/15/2021 1:17:05 PM | AMC_Maker |
| COB-0000002024-process | 48 | 7/14/2021 1:18:05 PM | 7/14/2021 1:27:05 PM | Branch_Checker |
| COB-0000002024-process | 52 | 7/15/2021 1:37:22 PM | 7/15/2021 1:47:22 PM | AMC_Checker |
内容的提问来源于stack exchange，提问作者MD KAMRAN AZAM
相关产品推荐
相关产品推荐
