按UserID分组统计DropOffPoint为Completed的前后记录行数
2026-10-5
统计需求说明
针对每个用户ID（UserID），需要统计其所有记录中退出节点（DropOffPoint）值为「Completed」的条目出现前的尝试次数、出现后的尝试次数、总尝试次数以及Completed条目总数。
原始数据明细表
| 数字参考编号 | 用户ID | 录入时间 | 退出节点 | 高层结果 |
|---|---|---|---|---|
| 1001030397 | adam | 2021/07/30 13:06 | BACKSIDE | Undefined |
| 1001030403 | adam | 2021/07/30 13:08 | FRONTSIDE | Undefined |
| 1001030408 | adam | 2021/07/30 13:12 | FRONTSIDE | Undefined |
| 1001042505 | bernard | 2021/08/09 08:18 | Completed | Refer |
| 1001034228 | joe | 2021/08/02 21:31 | FRONTSIDE | Undefined |
| 1001033843 | maria | 2021/08/02 15:51 | Completed | Notsupported |
| 1001033848 | maria | 2021/08/02 15:59 | ADDRESSDOCUMENT | Undefined |
| 1001033850 | maria | 2021/08/02 16:02 | FRONTSIDE | Undefined |
| 1001033853 | maria | 2021/08/02 16:04 | FRONTSIDE | Undefined |
| 1001033854 | maria | 2021/08/02 16:04 | Completed | Notsupported |
| 1001033861 | maria | 2021/08/02 16:10 | Completed | Notsupported |
| 1001040207 | max | 2021/08/07 10:25 | FRONTSIDE | Undefined |
| 1001040208 | max | 2021/08/07 10:27 | FRONTSIDE | Undefined |
| 1001040258 | max | 2021/08/07 11:18 | BACKSIDE | Undefined |
| 1001040259 | max | 2021/08/07 11:22 | FRONTSIDE | Undefined |
| 1001040261 | max | 2021/08/07 11:23 | Completed | Passed |
预期输出统计结果表
| 用户ID | 总尝试次数 | Completed前尝试次数 | Completed后尝试次数 | Completed总数 |
|---|---|---|---|---|
| adam | 3 | 0 | 0 | 0 |
| bernard | 1 | 0 | 0 | 1 |
| joe | 1 | 0 | 0 | 0 |
| maria | 6 | 0 | 5 | 3 |
| max | 5 | 4 | 0 | 1 |
内容的提问来源于stack exchange，提问作者Rama
相关产品推荐
相关产品推荐
