扩展SQL查询：计算分组内最新记录与progress=1/10记录的时间差

我有如下结构的日志表，目前已经写了SQL查询，按FK和colB分组，获取每个FK下每种水果的最新记录（查询语句如下）。现在想扩展这个查询，计算当前返回的最新记录与对应分组内progress=1/10的最新记录之间的时间差。如果需要的话，我可以把progress列拆成两列，但不确定这个需求能不能实现，求指点。

日志表结构 #

FK|colA|colB|progress|timestamp
2|y|apple|2/10|2023-03-03 09:43:20
1|c|orange|3/10|2023-03-03 09:42:00
1|b|orange|2/10|2023-03-03 09:41:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|z|apple|1/10|2023-03-03 09:40:35
1|a|orange|1/10|2023-03-03 09:40:00
1|c|orange|3/10|2023-02-03 11:02:00
1|b|orange|2/10|2023-02-03 10:41:00
1|a|orange|1/10|2023-02-03 10:30:00

当前查询语句 #

SELECT l.*
FROM log l,
(SELECT FK, ColB, MAX(TIMESTAMP) AS Timestamp
FROM log
GROUP BY FK, ColB DESC
ORDER BY TIMESTAMP DESC) l1
where l.FK=l1.FK
             AND l.Timestamp=l1.Timestamp
             AND l.colB=l1.colB
ORDER BY l.FK, l.Timestamp DESC;

当前查询输出 #

FK|colA|colB|progress|timestamp
1|c|orange|3/10|2023-03-03 09:42:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|y|apple|2/10|2023-03-03 09:43:20

期望输出 #

FK|colA|colB|progress|timestamp|Timetaken(HH:MM:SS)
1|c|orange|3/10|2023-03-03 09:42:00|00:02:00 (2023-03-03 09:42:00 - 2023-03-03 09:40:00)
2|x|pineapple|1/10|2023-03-03 09:40:40|00:00:00 (2023-03-03 09:40:40 - 2023-03-03 09:40:40)
2|y|apple|2/10|2023-03-03 09:43:20|00:02:45 (2023-03-03 09:43:20 - 2023-03-03 09:40:35)

解决方案 #

核心思路 #

无需拆分progress列即可实现需求，核心是同时获取两个关键时间：每个FK+colB组的最新记录时间，以及同组内progress='1/10'的最新记录时间，再计算两者的时间差并格式化输出。

实现SQL（以MySQL为例） #

SELECT 
    l.*,
    -- 格式化时间差为HH:MM:SS格式，并拼接时间区间说明
    CONCAT(
        SEC_TO_TIME(TIMESTAMPDIFF(SECOND, l2.latest_1_10_ts, l.timestamp)),
        ' (', l.timestamp, ' - ', l2.latest_1_10_ts, ')'
    ) AS `Timetaken(HH:MM:SS)`
FROM log l
-- 关联子查询定位每个FK+colB组的最新记录
JOIN (
    SELECT FK, colB, MAX(timestamp) AS latest_ts
    FROM log
    GROUP BY FK, colB
) l1 ON l.FK = l1.FK AND l.colB = l1.colB AND l.timestamp = l1.latest_ts
-- 关联子查询获取同组内progress=1/10的最新时间戳
JOIN (
    SELECT FK, colB, MAX(timestamp) AS latest_1_10_ts
    FROM log
    WHERE progress = '1/10'
    GROUP BY FK, colB
) l2 ON l.FK = l2.FK AND l.colB = l2.colB
ORDER BY l.FK, l.timestamp DESC;

补充说明 #

1. 如果存在某些FK+colB组没有progress='1/10'记录的情况，可将第二个JOIN改为LEFT JOIN，并对NULL值做处理（比如显示N/A）；
2. 若拆分progress列为current_progress和total_progress两列，可将查询条件改为current_progress=1 AND total_progress=10，语义更清晰，也能避免字符串匹配的潜在问题，SQL只需调整第二个子查询的WHERE条件即可。

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