确定跨时间区间起止时间:聚合多销售订单的状态时段数据
基础信息
- 我有一张来自销售订单状态追踪应用的原始表,包含字段:
OpportunityID、SalesOrderID、CurrentStatus、PriorStatus、RevisionDateTime - 基于这张表,我通过
LEAD窗口函数构建了视图,视图包含字段:OpportunityID、SalesOrderID、PriorStatus、CurrentStatus、startTime、endTime、duration(按工作分钟统计) - 关联规则说明:
- Opportunity与SalesOrderID为一对多关系
- 每个关联到同一Opportunity的销售订单,独立推进自身的工作流
- 当Opportunity被取消时,其下所有关联销售订单会同步取消
- 销售订单可随时回退到工作流中的任意历史状态
需求目标
统计关联销售订单处于同一状态的合并时段范围。
示例数据
soID | opportunityID | lastStatus | statusName | startTime | endTime -----+---------------+------------+------------+------------------+------------------ 1 | Q1234 | Open | Approved | 11/17/20 10:00AM | 11/17/20 02:00PM 2 | Q1234 | Open | Approved | 11/17/20 10:00AM | 11/17/20 02:00PM 3 | Q1234 | Open | Approved | 11/17/20 10:00AM | 11/18/20 10:00AM 1 | Q1234 | Hold | Approved | 11/20/20 12:00PM | 11/21/20 08:00AM 2 | Q1234 | Confirmed | Approved | 11/21/20 12:00PM | 11/22/20 02:00PM
根据上述示例,需要输出2行结果,对应的合并时段如下:
1: 11/17/2020 10:00 AM / 11/18/2020 10:00 AM 2: 11/20/2020 12:00 PM / 11/22/2020 12:00 PM
我最初判断需要通过存储过程生成上述结果,但目前实现遇到阻碍,寻求可行的解决思路。
规则补充说明
- 状态判定仅以
statusName字段为准,忽略lastStatus列 - 最终输出需要包含
OpportunityID、合并起始时间、合并结束时间 - 合并逻辑说明:
- 同一组状态记录中,所有销售订单的
startTime一致但endTime存在差异时,取组内最小startTime作为合并起始时间,取组内最大endTime作为合并结束时间 - 所有分组均遵循相同逻辑
- 同一组状态记录中,所有销售订单的
- 排查过程中可使用db<>fiddle工具简化问题定位流程
内容的提问来源于stack exchange,提问作者Morgan Boyd
相关产品推荐
相关产品推荐

