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，而是舊版本，那麼這個答案可能沒用...