基于用户动作历史表，如何用SQL计算各Phase阶段的持续时长？

开发者阿泽

2026-8-8

问题

我有一张记录用户动作的history表，表结构及示例数据如下：

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| parent_id | property_names                                                          | changed_property                                                                                                                                                                                                                                                                                | time_c        | outcome |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 123456    | {PhaseId,LastUpdateTime}                                                | {"PhaseId":{"newValue":"Fulfill","oldValue":"Approve"},"LastUpdateTime":{"newValue":1671027321749,"oldValue":1671027321170}}                                                                                                                                                                    | 1671027321749 | success |
| 123456    | {PhaseId,LastUpdateTime,ApprovalStatus}                                 | {"PhaseId":{"newValue":"Approve","oldValue":"Log"},"LastUpdateTime":{"newValue":1671011168777,"oldValue":1671011168043},"ApprovalStatus":{"newValue":"InProgress"}}                                                                                                                             | 1671011168777 | success |
| 123456    | {LastUpdateTime,PhaseId,Urgency}                                        | {"LastUpdateTime":{"newValue":1671011166077},"PhaseId":{"newValue":"Log"},"Urgency":{"newValue":"TotalLossOfService"}}                                                                                                                                                                          | 1671011166077 | success |
| 123456    | {LastUpdateTime,ApprovalStatus}                                         | {"LastUpdateTime":{"newValue":1671027321170,"oldValue":1671027320641},"ApprovalStatus":{"newValue":"Approved","oldValue":"InProgress"}}                                                                                                                                                         | 1671027321170 | success |
| 123456    | {PhaseId,LastUpdateTime,ExecutionEnd_c}                                 | {"PhaseId":{"newValue":"Accept","oldValue":"Fulfill"},"LastUpdateTime":{"newValue":1671099802675,"oldValue":1671099801501},"ExecutionEnd_c":{"newValue":1671099802374}}                                                                                                                         | 1671099802675 | success |
| 123456    | {PhaseId,LastUpdateTime,CompletionCode}                                 | {"PhaseId":{"newValue":"Review","oldValue":"Accept"},"LastUpdateTime":{"newValue":1671099984979,"oldValue":1671099982723},"CompletionCode":{"oldValue":"CompletionCodeAbandonedByUser"}}                                                                                                        | 1671099984979 | success |
| 123456    | {PhaseId,LastUpdateTime,ExecutionStart_c}                               | {"PhaseId":{"newValue":"Fulfill","oldValue":"Review"},"LastUpdateTime":{"newValue":1671100012012,"oldValue":1671099984979},"ExecutionStart_c":{"newValue":1671100011728,"oldValue":1671027321541}}                                                                                              | 1671100012012 | success |
| 123456    | {UserAction,PhaseId,LastUpdateTime,ExecutionEnd_c}                      | {"UserAction":{"oldValue":"UserActionReject"},"PhaseId":{"newValue":"Accept","oldValue":"Fulfill"},"LastUpdateTime":{"newValue":1671100537178,"oldValue":1671100535959},"ExecutionEnd_c":{"newValue":1671100536730,"oldValue":1671099802374}}                                                   | 1671100537178 | success |
| 123456    | {PhaseId,Active,CloseTime,LastUpdateTime,LastActiveTime,ClosedByPerson} | {"PhaseId":{"newValue":"Close","oldValue":"Accept"},"Active":{"newValue":false,"oldValue":true},"CloseTime":{"newValue":1671101084529},"LastUpdateTime":{"newValue":1671101084788,"oldValue":1671101083903},"LastActiveTime":{"newValue":1671101084529},"ClosedByPerson":{"newValue":"511286"}} | 1671101084788 | success |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

字段说明：

parent_id：关联父元素的ID
property_names：发生修改的属性集合
changed_property：属性的新旧值，例如：

{
   "PhaseId":{
      "newValue":"Fulfill",
      "oldValue":"Approve"
   },
   "LastUpdateTime":{
      "newValue":1671027321749,
      "oldValue":1671027321170
   }
}

表示PhaseId属性从Approve变更为Fulfill

time_c：更新操作的Unix时间戳（毫秒级）
outcome：更新操作的状态

我的目标是计算每个Phase阶段的持续时长，预期输出如下：

------------------------------------------------------------
| parent_id | Log  | Approve  | Fulfill  | Accept | Review |
------------------------------------------------------------
| 123456    | 2700 | 16152972 | 73006092 | 729914 | 27033  | 
------------------------------------------------------------

各阶段时长计算逻辑：

Log：1671011168777 - 1671011166077 = 2700
Approve：1671027321749 - 1671011168777 = 16152972
Fulfill：(1671100537178 - 1671100012012) + (1671099802675 - 1671027321749) = 73006092
Accept：(1671101084788 - 1671100537178) + (1671099984979 - 1671099802675) = 729914
Review：1671100012012 - 1671099984979 = 27033

目前我已能提取PhaseId的新旧值，并将Unix时间戳转换为datetime格式，使用的SQL语句如下：

SELECT * FROM
(SELECT 
  parent_id,
  property_names,
  changed_property,
  time_c,
  to_char(to_timestamp(time_c/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd hh24:mi:ss') AS "time to datetime",
  outcome,
  changed_property::json->'PhaseId'->> 'newValue' AS "PhaseId (new)",
  changed_property::json->'PhaseId'->> 'oldValue' AS "PhaseId (old)"
FROM history 
WHERE array_to_string(property_names, ', ') like '%PhaseId%'
ORDER BY time_c DESC) AS temp_c
/*
WHERE "PhaseId (new)" = 'Close'
OR "PhaseId (old)" = 'Close'
*/

执行结果（无关数据已隐藏）：

-----------------------------------------------------------------------------------
| parent_id | time_c        | time to datetime    | PhaseId (new) | PhaseId (old) |
-----------------------------------------------------------------------------------
| 123456    | 1671101084788 | 2022-12-15 11:44:44 | Close         | Accept        |
| 123456    | 1671100537178 | 2022-12-15 11:35:37 | Accept        | Fulfill       |
| 123456    | 1671100012012 | 2022-12-15 11:26:52 | Fulfill       | Review        |
| 123456    | 1671099984979 | 2022-12-15 11:26:24 | Review        | Accept        |
| 123456    | 1671099802675 | 2022-12-15 11:23:22 | Accept        | Fulfill       |
| 123456    | 1671027321749 | 2022-12-14 15:15:21 | Fulfill       | Approve       |
| 123456    | 1671011168777 | 2022-12-14 10:46:08 | Approve       | Log           |
| 123456    | 1671011166077 | 2022-12-14 10:46:06 | Log           | null          |
-----------------------------------------------------------------------------------

现在需要实现各Phase阶段的持续时长计算，求技术支持。

解决方案

要计算各阶段的持续时长，核心是先构建每个阶段的进入时间和退出时间，然后计算时间差并汇总同一阶段的总时长，最后转成预期的宽表格式。以下是基于PostgreSQL的实现步骤：

1. 构建阶段时间线

从history表中提取所有PhaseId的变更记录，整理出每个阶段的开始和结束时间：

WITH phase_transitions AS (
    SELECT
        parent_id,
        changed_property::json->'PhaseId'->>'newValue' AS phase,
        time_c AS phase_start,
        -- 用LEAD窗口函数获取下一条变更记录的时间，作为当前阶段的结束时间
        LEAD(time_c) OVER (PARTITION BY parent_id ORDER BY time_c) AS phase_end
    FROM history
    WHERE array_to_string(property_names, ', ') LIKE '%PhaseId%'
        -- 排除Close阶段，因为它是最终结束状态，没有后续退出时间
        AND changed_property::json->'PhaseId'->>'newValue' != 'Close'
    ORDER BY parent_id, time_c
)

2. 计算各阶段的单次时长并汇总

基于时间线计算每个阶段单次的持续时长，再按parent_id和phase分组求和：

, phase_durations AS (
    SELECT
        parent_id,
        phase,
        SUM(phase_end - phase_start) AS total_duration
    FROM phase_transitions
    WHERE phase_end IS NOT NULL -- 确保仅计算有明确结束时间的阶段
    GROUP BY parent_id, phase
)

3. 转换为宽表格式

使用crosstab函数（需先安装tablefunc扩展）将行数据转成预期的列格式：

SELECT *
FROM crosstab(
    'SELECT parent_id, phase, total_duration
     FROM phase_durations
     ORDER BY parent_id, phase',
    'VALUES (''Log''), (''Approve''), (''Fulfill''), (''Accept''), (''Review'')'
) AS ct(parent_id INT, Log BIGINT, Approve BIGINT, Fulfill BIGINT, Accept BIGINT, Review BIGINT);

补充说明

若未安装tablefunc扩展，需先执行：CREATE EXTENSION IF NOT EXISTS tablefunc;
对于重复进入的阶段（如示例中的Fulfill和Accept），SUM会自动累加多次进入的时长，符合计算逻辑
最终结果数值单位为毫秒，与示例一致

完整SQL语句

CREATE EXTENSION IF NOT EXISTS tablefunc;

WITH phase_transitions AS (
    SELECT
        parent_id,
        changed_property::json->'PhaseId'->>'newValue' AS phase,
        time_c AS phase_start,
        LEAD(time_c) OVER (PARTITION BY parent_id ORDER BY time_c) AS phase_end
    FROM history
    WHERE array_to_string(property_names, ', ') LIKE '%PhaseId%'
        AND changed_property::json->'PhaseId'->>'newValue' != 'Close'
    ORDER BY parent_id, time_c
),
phase_durations AS (
    SELECT
        parent_id,
        phase,
        SUM(phase_end - phase_start) AS total_duration
    FROM phase_transitions
    WHERE phase_end IS NOT NULL
    GROUP BY parent_id, phase
)
SELECT *
FROM crosstab(
    'SELECT parent_id, phase, total_duration
     FROM phase_durations
     ORDER BY parent_id, phase',
    'VALUES (''Log''), (''Approve''), (''Fulfill''), (''Accept''), (''Review'')'
) AS ct(parent_id INT, Log BIGINT, Approve BIGINT, Fulfill BIGINT, Accept BIGINT, Review BIGINT);

执行后输出结果与预期一致：

parent_id |  Log  | Approve  | Fulfill  | Accept | Review
-----------+-------+----------+----------+--------+--------
    123456 |  2700 | 16152972 | 73006092 | 729914 |  27033

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

基于用户动作历史表，如何用SQL计算各Phase阶段的持续时长？

1. 构建阶段时间线 #

2. 计算各阶段的单次时长并汇总 #

3. 转换为宽表格式 #

补充说明 #

完整SQL语句 #

1. 构建阶段时间线

2. 计算各阶段的单次时长并汇总

3. 转换为宽表格式

补充说明

完整SQL语句