mysql - 时间段 SQL 查询

Question

我有一个这样的表:

---------------------------------------------
|Id | Step                  | StartedAt     |
---------------------------------------------
| 1 | Download Data         | 10:20:00      |
| 2 | Data Quality Control  | 10:45:00      |
| 3 | Run Prediction        | 10:47:00      |
---------------------------------------------

什么是 SQL 查询，它告诉我每个步骤的时间，如下所示:“下载数据”用了 25 分钟，“数据质量控制”用了 2 分钟等。

干杯。

P.S 我的 RDBMS 是 MySQL。 有没有办法在 MySQL 中做到这一点？

Answer 1

你应该使用 datetime or timestamp而不是 time 来使这项工作跨越日期边界。查看评论。

大多数 RDBMS 的标准 SQL

为此使用窗口函数。目前在大多数著名的 RDBMS(MySQL 除外)中实现:

SELECT *
     ,lead("StartedAt") OVER (ORDER BY "StartedAt") - "StartedAt" AS duration
FROM  tbl;

lead() 根据 ORDER BY 子句中的顺序检索下 行的值。对于最后一行，如果没有“下一个”行，则会得到 NULL。

我引用 manual of PostgreSQL on window functions ，因为您没有命名您的 RDBMS。

MySQL

在没有窗口函数的情况下，一种方法是使用相关子查询:

SELECT t1.*
     ,(SELECT t2."StartedAt"
       FROM tbl t2
       WHERE t2.id > t1.id
       ORDER BY t2."StartedAt"
       LIMIT 1) - "StartedAt" AS duration
FROM  tbl t1;

或者这可能更快:

SELECT t1."Id", t1."Step", t1."StartedAt"
      ,TIMESTAMPDIFF(MINUTE, t1."StartedAt", min(t2."StartedAt")) AS minutes
FROM   tbl t1
LEFT   JOIN tbl t2 ON t2."Id" > t1."Id"
GROUP  BY t1."Id", t1."Step", t1."StartedAt";

->sqlfiddle with both queries.

手册关于TIMESTAMPDIFF()或 TIMEPDIFF()

如果您的 Id 列将无间隙地升序，这会更简单。但在现实生活中很少出现这种情况。