SQL查询获取每个状态的时间

Question

给定一个如下所示的表格

|date      |From       | To                 |
  |01/01/21  |Open       |In progress         |
  |01/03/21  |In progress|In review           | 
  |01/10/21  |In Review  |Done                |
  |01/15/21  |Done       |Resolved            |

有没有办法根据状态更改的日期来跟踪每个状态所花费的时间？ 我正在使用的表中只有一个日期列。

期望的结果看起来像这样

|Time spent in phase|Status|
|2                  |Open
|7                  |In review
|5                  |Done

我只有一张表可以使用，那么是否有任何查询可以很好地解决这个问题？此工作流程已设定规则，因此只有 open 才能进入进行中。

如果是这样，是否可以使用这些工作流程状态更改来获取潜在的查询？

Answer 1

DBFIDDLE

尽管我最后对你的“问题”发表了评论，但我还是创建了一个小提琴。它有两个选项：

• 选项1

SELECT 
    DATEDIFF(COALESCE(LEAD(`Date`) OVER (ORDER BY `Date`),`date`),`Date`) as 'Time spent in phase',
    `From`
FROM ThatLooksLikethis;

输出：

阶段花费的时间	来自
2	打开
7	进行中
5	审核中
0	完成

• 选项2：

SELECT 
    DATEDIFF(`Date`,COALESCE(LAG(`Date`) OVER (ORDER BY `Date`),`date`)) as 'Time spent in phase',
    `From`
FROM ThatLooksLikethis;

输出：

阶段花费的时间	来自
0	打开
2	进行中
7	审核中
5	完成

附注这个答案使用MySQL 8.0函数 LEAD 和 LAG。如果您使用的不是 MySQL 8.0，而是旧版本，那么这个答案可能没用......