Name | Datum | Stunden | Z?hlen |
---|---|---|---|
Mühlen | 2022-07-17 | 23 | 12 |
Mühlen | 2022-07-18 | 00 | 15 |
Mühlen | 2022-07-18 | 01 | 20 |
Mühlen | 2022-07-18 | 02 | 22 |
Mühlen | 2022-07-18 | 03 | 25 |
Mühlen | 2022-07-18 | 04 | 20 |
Mühlen | 2022-07-18 | 05 | 22 |
Mühlen | 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 |
Meine aktuelle Eingabetabelle speichert kontinuierlich jede Stunde des Tages aufgezeichnete Z?hlinformationen. Ich muss die Differenz aufeinanderfolgender Z?hlwerte extrahieren, aber da ich gezwungen bin, MySQL 5.7 zu verwenden, habe ich Probleme damit.
Die Anfrage, die ich geschrieben habe, lautet wie folgt:
SET @cnt := 0; SELECT Name, Date, Hours, Count, (@cnt := @cnt - Count) AS DiffCount FROM Hourly ORDER BY Date;
Dies liefert keine genauen Ergebnisse.
Ich m?chte folgende Ausgabe erhalten:
Name | Datum | Stunden | Z?hlen | Unterschied |
---|---|---|---|---|
Mühlen | 2022-07-17 | 23 | 12 | 0 |
Mühlen | 2022-07-18 | 00 | 15 | 3 |
Mühlen | 2022-07-18 | 01 | 20 | 5 |
Mühlen | 2022-07-18 | 02 | 22 | 2 |
Mühlen | 2022-07-18 | 03 | 25 | 3 |
Mühlen | 2022-07-18 | 04 | 20 | 5 |
Mühlen | 2022-07-18 | 05 | 22 | 2 |
Mühlen | 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 |
Bitte schlagen Sie vor, was mir fehlt.
嘗試以下操作:
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;
查看來(lái)自 db-fiddle 的演示。
在MySQL 5.7中,您可以內(nèi)聯(lián)更新變量,以包含更新后的“Count”值。由于當(dāng)“Name”的值更改時(shí),您需要重置變量,因此您可以使用包含先前“Name”值的另一個(gè)變量。然后使用 IF
函數(shù)檢查:
它將與ABS
a> 函數(shù),對(duì)差值應(yīng)用絕對(duì)值。
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;
請(qǐng)?jiān)?a rel="nofollow noreferrer">此處查看演示。
在MySQL 8.0中,您可以使用像LAG
順利獲得輸出。它將與:
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
請(qǐng)?jiān)?a rel="nofollow noreferrer">此處查看演示。