Pertanyaan SQL untuk mendapatkan masa setiap status

Question

Diberi jadual seperti di bawah

|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            |

Adakah terdapat cara untuk menjejaki masa yang diluangkan dalam setiap status berdasarkan tarikh status berubah? Jadual yang saya bekerjasama hanya mempunyai lajur tarikh.

Hasil yang diinginkan kelihatan seperti ini

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

Saya hanya mempunyai satu jadual untuk bekerja, jadi adakah terdapat sebarang pertanyaan yang akan menyelesaikan masalah ini dengan baik? Aliran kerja ini mempunyai peraturan yang ditetapkan supaya hanya terbuka boleh diteruskan.

Jika ya, adakah mungkin untuk menggunakan perubahan keadaan aliran kerja ini untuk mendapatkan pertanyaan yang berpotensi?

Answer 1

DBFIDDLE

Walaupun komen terakhir saya pada "soalan" anda, saya mencipta biola. Ia mempunyai dua pilihan:

• Pilihan 1

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

Keluaran:

Masa dihabiskan di pentas	dari
2	Terbuka
7	Sedang berjalan
5	Dalam semakan
0	Lengkap

• Pilihan 2:

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

Keluaran:

Masa dihabiskan di pentas	dari
0	Terbuka
2	Sedang berjalan
7	Dalam semakan
5	Lengkap

P.S. Jawapan ini menggunakan fungsi MySQL 8.0 LEAD< /a> dan LAG. Jika anda tidak menggunakan MySQL 8.0, tetapi versi yang lebih lama, jawapan ini mungkin tidak berguna...