Ekstrak perbezaan nilai berturut-turut dalam MySQL 5.7

Question

Nama	Tarikh	jam	Mengira
Kilang	2022-07-17	23	12
Kilang	2022-07-18	00	15
Kilang	2022-07-18	01	20
Kilang	2022-07-18	02	22
Kilang	2022-07-18	03	25
Kilang	2022-07-18	04	20
Kilang	2022-07-18	05	22
Kilang	2022-07-18	06	25
Mike	2022-07-18	00	15
Mike	2022-07-18	01	20
Mike	2022-07-18	02	22
Mike	2022-07-18	03	25
Mike	2022-07-18	04	20

Jadual input semasa saya menyimpan maklumat kiraan yang direkodkan secara berterusan setiap jam dalam sehari. Saya perlu mengekstrak perbezaan nilai kiraan berturut-turut, tetapi kerana saya terpaksa menggunakan MySQL 5.7, saya menghadapi masalah melakukan ini.

Pertanyaan yang saya tulis adalah seperti berikut:

SET @cnt := 0;
SELECT Name, Date, Hours, Count, (@cnt := @cnt - Count) AS DiffCount
FROM Hourly
ORDER BY Date;

Ini tidak memberikan hasil yang tepat.

Saya ingin mendapatkan output berikut:

Nama	Tarikh	jam	Mengira	Perbezaan
Kilang	2022-07-17	23	12	0
Kilang	2022-07-18	00	15	3
Kilang	2022-07-18	01	20	5
Kilang	2022-07-18	02	22	2
Kilang	2022-07-18	03	25	3
Kilang	2022-07-18	04	20	5
Kilang	2022-07-18	05	22	2
Kilang	2022-07-18	06	25	3
Mike	2022-07-18	00	15	0
Mike	2022-07-18	01	20	5
Mike	2022-07-18	02	22	2
Mike	2022-07-18	03	25	3
Mike	2022-07-18	04	20	5
Mike	2022-07-18	05	22	2
Mike	2022-07-18	06	25	3

Sila cadangkan apa yang saya kurang.

Answer 1

Cuba yang berikut:

SET @count=(select Count_ from Hourly order by Name,Date_,Hours LIMIT 1);
Set @Name=(select Name from Hourly order by Name,Date_,Hours LIMIT 1);

select  Name,Date_,Hours,Count_,
ABS(curr_count-lag_count) as DiffCount
From
(
  select Name,Date_,Hours,Count_, 
  Case When @Name = Name Then @count Else Count_ End as lag_count
  , @count:=Count_ curr_count, @Name:=Name
  From Hourly order by Name,Date_,Hours
) D
Order By Name,Date_,Hours;

Lihat demo daripada db-fiddle.

Answer 2

Dalam MySQL 5.7, anda boleh mengemas kini pembolehubah sebaris untuk mengandungi nilai "Count" yang dikemas kini. Memandangkan anda perlu menetapkan semula pembolehubah apabila nilai "Nama" berubah, anda boleh menggunakan pembolehubah lain yang mengandungi nilai sebelumnya "Nama". Kemudian gunakan fungsi IF untuk menyemak:

• Jika nama terdahulu anda sama dengan nama semasa anda
• Kemudian kira beza kiraan
• Jika tidak tetapkan 0

Ia akan berfungsi dengan fungsi ABS a>, menggunakan nilai mutlak kepada perbezaan.

SET @cnt  := NULL;
SET @name := NULL;

SELECT Date,
       Hours,
       ABS(IF(@name = Name, 
              @cnt := @cnt - Count,
              0)                    ) AS DiffCount,
       (@name := Name)                AS Name,
       (@cnt := Count)                AS Count
FROM tab
ORDER BY Name DESC,
         Date, 
         Hours;

Lihat demo di sini.

---

Dalam MySQL 8.0 anda boleh menggunakan sesuatu seperti LAG untuk mendapatkan output dengan lancar. Ia akan berfungsi dengan:

• ABS Gunakan nilai perbezaan mutlak,
• COALESCE digunakan untuk mengeluarkan nilai nol pertama.

SELECT *, 
       COALESCE(ABS(
           Count - LAG(Count) OVER(
                       PARTITION BY Name
                       ORDER     BY Date, Hours
                   )
       ), 0) AS Diff 
FROM tab
ORDER BY Name DESC, 
         Date, 
         Hours

Lihat demo di sini.