在Tableau中基于其他列条件计算单列时间差

嘿，我刚好处理过类似的问题！这种夹杂无关中间状态的日志表，确实不能直接算相邻行的时间差，得用窗口函数或者子查询来精准匹配对应的状态对才行。

先给你理清楚核心思路：我们要做的就是给每个on_id的目标结束状态，找到它对应的目标起始状态的时间戳，然后计算两者的分钟差。

假设你的表结构大概是这样（我模拟一个通用结构方便演示）：

CREATE TABLE status_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    on_id INT, -- 你的业务标识ID
    status VARCHAR(50), -- 状态值，比如START/COMPLETE/PAUSE等
    event_timestamp DATETIME -- 状态变更的时间戳
);

方法一：用窗口函数（推荐，适配MySQL 8+/PostgreSQL/Oracle/SQL Server等） #

这种方法先用CTE预处理数据，给每个记录标记出它所属的起始状态时间，最后过滤出结束状态计算差值：

WITH status_with_start AS (
    SELECT 
        on_id,
        status,
        event_timestamp,
        -- 针对每个on_id按时间排序，向前抓取最近的START状态时间
        LAST_VALUE(CASE WHEN status = 'START' THEN event_timestamp END) OVER (
            PARTITION BY on_id 
            ORDER BY event_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS corresponding_start_time
    FROM status_log
)
SELECT 
    on_id,
    -- 计算分钟差，这里用MySQL语法，其他数据库可按需替换
    TIMESTAMPDIFF(MINUTE, corresponding_start_time, event_timestamp) AS time_taken_minutes
FROM status_with_start
WHERE status = 'COMPLETE'; -- 只保留结束状态的计算结果

方法二：自连接+子查询（兼容旧版数据库，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用自连接结合子查询，找到每个结束状态对应的最近起始状态：

SELECT 
    sl.on_id,
    TIMESTAMPDIFF(MINUTE, sl_start.event_timestamp, sl.event_timestamp) AS time_taken_minutes
FROM status_log sl
INNER JOIN status_log sl_start 
    ON sl.on_id = sl_start.on_id
    AND sl_start.status = 'START'
    -- 子查询定位当前结束状态之前最近的起始状态时间
    AND sl_start.event_timestamp = (
        SELECT MAX(event_timestamp)
        FROM status_log
        WHERE on_id = sl.on_id
          AND status = 'START'
          AND event_timestamp < sl.event_timestamp
    )
WHERE sl.status = 'COMPLETE';

关键注意点 #

• 替换状态值：把示例里的START和COMPLETE换成你实际需要计算的状态对，比如PENDING到APPROVED等。
• 数据库适配：
  • PostgreSQL用EXTRACT(MINUTE FROM (event_timestamp - corresponding_start_time))计算分钟差
  • Oracle用EXTRACT(MINUTE FROM (event_timestamp - corresponding_start_time))
  • SQL Server用DATEDIFF(MINUTE, corresponding_start_time, event_timestamp)
• 多状态对扩展：如果要计算多种状态对的耗时，可以在CTE里添加多个LAST_VALUE字段，或者用CASE语句区分不同状态组合。

这样就能完美跳过中间无关的状态行，只计算你真正关心的两个状态之间的耗时啦！

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