SQL计算文档各阶段停留时长的均值、中位数和最大值
2026-7-6
文档阶段停留时长统计需求与处理
背景说明
- 文档制作流程包含8个阶段（Stage 1至Stage 8），通过工单追踪每份文档的进度
- 工单每次修改都会记录
modification time，但仅需关注阶段名称（stage name）变更的时间点 - 存在文档被退回至前期阶段、在阶段间来回流转的情况
原始记录表格
| 文档名称 | 修改时间 | 阶段名称 |
|---|---|---|
| Doc 1 | 01/01/2023 06:00:00 | Stage 1 |
| Doc 1 | 01/01/2023 07:00:00 | Stage 1 |
| Doc 1 | 02/01/2023 06:00:00 | Stage 2 |
| Doc 2 | 02/01/2023 07:00:00 | Stage 1 |
| Doc 2 | 04/01/2023 06:00:00 | Stage 2 |
| Doc 1 | 05/01/2023 06:00:00 | Stage 3 |
| Doc 1 | 05/01/2023 14:00:00 | Stage 3 |
| Doc 1 | 05/01/2023 15:00:00 | Stage 3 |
| Doc 2 | 05/01/2023 15:30:00 | Stage 3 |
| Doc 1 | 05/01/2023 16:00:00 | Stage 3 |
| Doc 1 | 05/01/2023 17:00:00 | Stage 2 |
处理规则
- 过滤未变更阶段的记录（仅修改工单其他内容的记录无需关注）
- 计算每份文档在各阶段停留时长的均值、中位数、最大值
具体处理过程与结果
1. 过滤无效记录
按文档分组，仅保留阶段发生变更的时间点：
Doc 1 有效记录
| 修改时间 | 阶段名称 |
|---|---|
| 01/01/2023 06:00:00 | Stage 1 |
| 02/01/2023 06:00:00 | Stage 2 |
| 05/01/2023 06:00:00 | Stage 3 |
| 05/01/2023 17:00:00 | Stage 2 |
Doc 2 有效记录
| 修改时间 | 阶段名称 |
|---|---|
| 02/01/2023 07:00:00 | Stage 1 |
| 04/01/2023 06:00:00 | Stage 2 |
| 05/01/2023 15:30:00 | Stage 3 |
2. 计算各阶段停留时长
停留时长 = 下一个阶段变更时间 - 当前阶段进入时间（最后一个阶段无后续变更，暂不统计）
Doc 1 各阶段停留时长
- Stage 1：24小时（02/01 06:00 - 01/01 06:00）
- Stage 2：72小时（05/01 06:00 - 02/01 06:00）
- Stage 3：11小时（05/01 17:00 - 05/01 06:00）
- 再次进入Stage 2：无后续变更时间，暂不统计
Doc 2 各阶段停留时长
- Stage 1：47小时（04/01 06:00 - 02/01 07:00）
- Stage 2：67.5小时（05/01 15:30 - 04/01 06:00）
- Stage 3：无后续变更时间，暂不统计
3. 统计指标计算
按阶段汇总（含多次进入的情况）
| 阶段名称 | 停留时长列表 | 均值 | 中位数 | 最大值 |
|---|---|---|---|---|
| Stage 1 | 24h、47h | 35.5h | 35.5h | 47h |
| Stage 2 | 72h | 72h | 72h | 72h |
| Stage 3 | 11h | 11h | 11h | 11h |
按文档汇总
- Doc 1
- 停留时长列表：24h、72h、11h
- 均值：≈35.67小时
- 中位数：24小时
- 最大值：72小时
- Doc 2
- 停留时长列表：47h、67.5h
- 均值：57.25小时
- 中位数：57.25小时
- 最大值：67.5小时
内容的提问来源于stack exchange，提问作者Deboparna
相关产品推荐
相关产品推荐
