提取 MySQL 5.7 連續值的差異

Question

姓名	日期	小時	計數
米爾斯	2022-07-17	23	12
米爾斯	2022-07-18	00	15
米爾斯	2022-07-18	01	20
米爾斯	2022-07-18	02	22
米爾斯	2022-07-18	03	25
米爾斯	2022-07-18	04	20
米爾斯	2022-07-18	05	22
米爾斯	2022-07-18	06	25
麥克	2022-07-18	00	15
麥克	2022-07-18	01	20
麥克	2022-07-18	02	22
麥克	2022-07-18	03	25
麥克	2022-07-18	04	20

我目前的輸入表儲存每天每小時連續記錄的計數資訊。我需要提取連續計數值的差異，但由於我被迫使用 MySQL 5.7，所以我很難做到這一點。

我寫的查詢如下：

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

這並沒有給出準確的結果。

我希望得到以下輸出：

姓名	日期	小時	計數	差異
米爾斯	2022-07-17	23	12	0
米爾斯	2022-07-18	00	15	3
米爾斯	2022-07-18	01	20	5
米爾斯	2022-07-18	02	22	2
米爾斯	2022-07-18	03	25	3
米爾斯	2022-07-18	04	20	5
米爾斯	2022-07-18	05	22	2
米爾斯	2022-07-18	06	25	3
麥克	2022-07-18	00	15	0
麥克	2022-07-18	01	20	5
麥克	2022-07-18	02	22	2
麥克	2022-07-18	03	25	3
麥克	2022-07-18	04	20	5
麥克	2022-07-18	05	22	2
麥克	2022-07-18	06	25	3

請提出我所缺少的內容。

Answer 1

嘗試以下操作：

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;

查看來自 db-fiddle 的示範。

Answer 2

在MySQL 5.7中，您可以內嵌更新變量，以包含更新後的「Count」值。由於當「Name」的值變更時，您需要重置變數，因此您可以使用包含先前「Name」值的另一個變數。然後使用 IF 函數檢查：

• 如果您以前的名字與現在的名字相同
• 然後計算計數差
• 否則分配 0

它將與ABS 函數，對差值套用絕對值。

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;

請在此處查看示範。

---

在MySQL 8.0中，您可以使用像LAG 順利獲得輸出。它將與：

• ABS 套用絕對值差異值，
• COALESCE 用來刪除第一個空值。

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

請在此處查看示範。