如何计算同一应用不同状态间的updt_time时间差？

要解决这个问题，核心思路是利用窗口函数追踪每个应用的上一次状态更新时间，然后计算两次更新之间的时间差。下面我会给出通用的SQL实现方案，适配主流数据库（比如PostgreSQL、MySQL）。

步骤说明 #

1. 分组排序并获取上一次状态时间：通过PARTITION BY app_no按应用分组，ORDER BY updt_time保证状态按时间顺序排列，用LAG()窗口函数提取上一次的更新时间。
2. 计算时间差：根据数据库类型选择合适的函数，将两次更新的时间差转换为小时数（hh格式）。
3. 过滤无效行：每个应用的第一条状态记录没有上一次时间，需要过滤掉。
4. 格式化输出：将状态名称转为小写，匹配你需要的输出格式。

具体代码实现 #

方案1：适用于PostgreSQL

WITH status_transitions AS (
    SELECT
        app_no,
        status,
        updt_time,
        -- 获取当前应用的上一次状态更新时间
        LAG(updt_time) OVER (PARTITION BY app_no ORDER BY updt_time) AS previous_update_time
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    LOWER(status) AS status,
    -- 计算时间差并转为总小时数（保留2位小数）
    ROUND(EXTRACT(EPOCH FROM (updt_time - previous_update_time)) / 3600, 2) AS time_taken
FROM status_transitions
WHERE previous_update_time IS NOT NULL; -- 过滤掉每个应用的第一条状态记录

方案2：适用于MySQL

WITH status_transitions AS (
    SELECT
        app_no,
        status,
        updt_time,
        LAG(updt_time) OVER (PARTITION BY app_no ORDER BY updt_time) AS previous_update_time
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    LOWER(status) AS status,
    -- 直接计算两个时间的小时差
    TIMESTAMPDIFF(HOUR, previous_update_time, updt_time) AS time_taken
FROM status_transitions
WHERE previous_update_time IS NOT NULL;

结果说明 #

运行上述代码后，你会得到符合需求的输出格式，比如针对示例数据，会返回每个状态切换对应的耗时（以小时为单位）。

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