Detaillierte Erläuterung der Idee, den Unterschied zwischen stündlichen Daten und den letzten stündlichen Daten in MySQL abzufragen

1. Einleitung

Die Anforderung besteht darin, die Differenz und das Verhältnis zwischen den Stundendaten und den vorherigen Stundendaten innerhalb eines bestimmten Zeitraums zu ermitteln. Ursprünglich dachte ich, es wäre ein ganz einfaches sql , aber nachdem ich zwei Minuten darüber nachgedacht hatte, stellte ich fest, dass es nicht einfach war. Ich konnte im Internet keine Referenzlösungen finden, sodass ich es nur langsam selbst analysieren konnte.

Zuerst hatte ich keine Ahnung, also fragte ich DBA Kommilitonen. DBA sagte, er wüsste nicht, wie das geht, und bat mich, ein php Skript für die Berechnung zu schreiben. Das war ein bisschen zu viel. Ich wollte nur vorübergehend einige Daten überprüfen und konnte nicht glauben, dass ich sie nicht direkt mit sql finden konnte. Okay, probieren wir es mal aus.

Der Blogger hier verwendet eine dumme Methode, um dies zu erreichen. Wenn Sie eine einfachere Methode kennen, geben Sie mir bitte Ihren Rat. Der Kommentarbereich wartet auf Sie!

MySQL-Version:

mysql> Version auswählen();
+---------------------+
| version() |
+---------------------+
| 10.0.22-MariaDB-Protokoll |
+---------------------+
1 Zeile im Satz (0,00 Sek.)

2. Abfrage der Differenz zwischen jeder Stunde und der vorherigen Stunde

1. Anforderungen aufteilen

Lassen Sie uns hier separat abfragen, um zu sehen, wie viele Daten vorhanden sind, um die nachfolgende Kombination zu erleichtern.

(1) Bezug stündlichen Datenvolumens

Zur Vereinfachung der Anzeige werden sie hier direkt zusammengeführt und es werden nur die Daten von 01-12 Uhr angezeigt. Dies ist kein bug . .

Wählen Sie Anzahl(*) als Nums, Datumsformat(log_time, „%Y-%m-%d %h“) als Tage aus dem Test, wobei 1 und log_time >= „2020-04-19 00:00:00“ und log_time <= „2020-04-20 00:00:00“, gruppieren Sie nach Tagen;
+----------+--------------+
| Zahl | Tage |
+----------+--------------+
| 15442 | 19.04.2020 01 |
| 15230 | 19.04.2020 02 |
| 14654 | 19.04.2020 03 |
| 14933 | 19.04.2020 04 |
| 14768 | 19.04.2020 05 |
| 15390 | 19.04.2020 06 |
| 15611 | 19.04.2020 07 |
| 15659 | 19.04.2020 08 |
| 15398 | 19.04.2020 09 |
| 15207 | 19.04.2020 10 |
| 14860 | 19.04.2020 11 |
| 15114 | 19.04.2020 12 |
+----------+--------------+

(2) Ermitteln Sie die Datenmenge der letzten Stunde

Wählen Sie Anzahl(*) als Nums1, Datumsformat (Datumssub (Datumsformat (log_time), „%Y-%m-%d %h“), Intervall -1 Stunde, „%Y-%m-%d %h“) als Tage ab Test, wobei 1 und log_time >= „2020-04-19 00:00:00“ und log_time <= „2020-04-20 00:00:00“, nach Tagen gruppieren;
+----------+--------------+
| Zahl1 | Tage |
+----------+--------------+
| 15114 | 19.04.2020 01 |
| 15442 | 19.04.2020 02 |
| 15230 | 19.04.2020 03 |
| 14654 | 19.04.2020 04 |
| 14933 | 19.04.2020 05 |
| 14768 | 19.04.2020 06 |
| 15390 | 19.04.2020 07 |
| 15611 | 19.04.2020 08 |
| 15659 | 19.04.2020 09 |
| 15398 | 19.04.2020 10 |
| 15207 | 19.04.2020 11 |
| 14860 | 19.04.2020 12 |
+----------+--------------+

Beachten:

1) Um die Daten der letzten Stunde abzurufen, verwenden Sie die Funktion date_sub(). date_sub(date, interval -1 hour) bedeutet, die letzte Stunde des Datumsparameters abzurufen. Weitere Einzelheiten finden Sie im Handbuch: https://www.w3school.com.cn/sql/func_date_sub.asp
2) Das äußerste verschachtelte Datumsformat dient hier dazu, das Format mit dem obigen konsistent zu halten. Wenn dieses Datumsformat nicht hinzugefügt wird, lautet das abgefragte Datumsformat: 2020-04-19 04:00:00, was für den Vergleich nicht praktisch ist.

2. Kombinieren Sie diese beiden Daten und sehen Sie

wähle Nums, Nums1, Tage, Tage1 
aus 
(Wählen Sie count(*) als Nums, date_format(log_time,'%Y-%m-%d %h') als Tage ab Test, wobei 1 und log_time >='2020-04-19 00:00:00' und log_time <= '2020-04-20 00:00:00' nach Tagen gruppieren) als m,
(Wählen Sie Anzahl(*) als Nums1, Datumsformat (Datumssub (Datumsformat (log_time), '%Y-%m-%d %h'), Intervall -1 Stunde), '%Y-%m-%d %h') als Tage1 aus Test, wobei 1 und Log_Time >= '2020-04-19 00:00:00' und Log_Time <= '2020-04-20 00:00:00', gruppieren Sie nach Tagen1) als n;

+-------+-------+---------------+---------------+
| Nums | Nums1 | Tage | Tage1 |
+-------+-------+---------------+---------------+
| 15442 | 15114 | 19.04.2020 01 | 19.04.2020 01 |
| 15442 | 15442 | 19.04.2020 01 | 19.04.2020 02 |
| 15442 | 15230 | 19.04.2020 01 | 19.04.2020 03 |
| 15442 | 14654 | 19.04.2020 01 | 19.04.2020 04 |
| 15442 | 14933 | 19.04.2020 01 | 19.04.2020 05 |
| 15442 | 14768 | 19.04.2020 01 | 19.04.2020 06 |
| 15442 | 15390 | 19.04.2020 01 | 19.04.2020 07 |
| 15442 | 15611 | 19.04.2020 01 | 19.04.2020 08 |
| 15442 | 15659 | 19.04.2020 01 | 19.04.2020 09 |
| 15442 | 15398 | 19.04.2020 01 | 19.04.2020 10 |
| 15442 | 15207 | 19.04.2020 01 | 19.04.2020 11 |
| 15442 | 14860 | 19.04.2020 01 | 19.04.2020 12 |
| 15230 | 15114 | 19.04.2020 02 | 19.04.2020 01 |
| 15230 | 15442 | 19.04.2020 02 | 19.04.2020 02 |
| 15230 | 15230 | 19.04.2020 02 | 19.04.2020 03 |

Es ist ersichtlich, dass diese Kombination dem verschachtelten Schleifeneffekt im Programm ähnelt, der gleichbedeutend damit ist, dass nums die äußere Schleife und nums1 die Speicherschleife ist. Verwenden Sie beim Schleifen zuerst den Wert von nums , um alle Werte von nums1 abzugleichen. Ähnlich wie das php -Programm:

foreach($arr als $k=>$v){
 foreach($arr1 als $k1=>$v1){

 }
}

Können wir in diesem Fall in zwei Schleifen-Arrays dieselben Werte finden und dann die Differenz berechnen, wie wir es normalerweise beim Schreiben von Programmen tun? Es ist offensichtlich, dass die Daten hier exakt gleich sind und als Vergleichsbedingungen verwendet werden können.

3. Anwendungsfall …wann soll die Differenz berechnet werden?

select (case when days = days1 then (nums - nums1) else 0 end) als diff
aus 
(Wählen Sie count(*) als Nums, date_format(log_time,'%Y-%m-%d %h') als Tage ab Test, wobei 1 und log_time >='2020-04-19 00:00:00' und log_time <= '2020-04-20 00:00:00' nach Tagen gruppieren) als m,
(Wählen Sie Anzahl(*) als Nums1, Datumsformat (Datumssub (Datumsformat (log_time), '%Y-%m-%d %h'), Intervall -1 Stunde), '%Y-%m-%d %h') als Tage1 aus Test, wobei 1 und Log_Time >= '2020-04-19 00:00:00' und Log_Time <= '2020-04-20 00:00:00', gruppieren Sie nach Tagen1) als n;

Wirkung:
+------+
| Unterschied |
+------+
| 328 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
|-212 |
| 0 |
| 0

Sie können diesen case..when sehen, wenn hier verwendet wird, um die Differenz zu berechnen, wenn zwei Daten gleich sind, was php Programm ähnelt:

	foreach($arr als $k=>$v){
 foreach($arr1 als $k1=>$v1){
 wenn($k == $k1){
  //Finde den Unterschied}
 }
}

Als Ergebnis können wir sehen, dass es viele 0 und einige berechnete Ergebnisse gibt. Wenn wir diese Nullen jedoch ausschließen, scheint es Hoffnung zu geben.

4. Filtern Sie den Teil mit dem Ergebnis 0 heraus und vergleichen Sie die endgültigen Daten

Hier wird having verwendet, um die Abfrageergebnisse zu filtern. having -Klausel ermöglicht es uns, die Datengruppen zu filtern. Obwohl unser sql am Ende keine group by -Klausel hat, haben beide Unterabfragen group by -Klauseln. Theoretisch ist die Verwendung von having zum Filtern von Daten am besten geeignet. Versuchen wir es.

select (case when days = days1 then (nums1 - nums) else 0 end) als diff
aus 
(Wählen Sie count(*) als Nums, date_format(log_time,'%Y-%m-%d %h') als Tage ab Test, wobei 1 und log_time >='2020-04-19 00:00:00' und log_time <= '2020-04-20 00:00:00' nach Tagen gruppieren) als m,
(Wählen Sie Anzahl(*) als Nums1, Datumsformat (Datumssub (Datumsformat (log_time), '%Y-%m-%d %h'), Intervall -1 Stunde), '%Y-%m-%d %h') als Tage1 aus Test, wobei 1 und Logzeit >= '2020-04-19 00:00:00' und Logzeit <= '2020-04-20 00:00:00', gruppieren Sie nach Tagen1) als n mit Unterschied <>0;

Ergebnis:
+------+
| Unterschied |
+------+
|-328 |
| 212 |
| 576 |
|-279 |
| 165 |
|-622 |
|-221 |
|-48 |
| 261 |
| 191 |
| 347 |
|-254 |
+------+

Hier sehen wir die berechneten Ergebnisse, also vergleichen wir sie. Hier sind einige manuell aufgelistete Daten:

Die Differenz zwischen der aktuellen Stunde und der letzten Stunde: Aktuelle Stunde - letzte Stunde

Diese Stunde Letzte Stunde Differenz
15442 15114 -328
15230 15442 212
14654 15230 576
14933 14654 -279
14768 14933 165

Man erkennt, dass die Differenz tatsächlich erfolgreich erzielt werden konnte. Wenn Sie das Verhältnis der Differenz erhalten möchten, case when days = days1 then (nums1 - nums)/nums1 else 0 end .

5. Holen Sie sich den Datenrückgang für diese und die letzte Stunde und zeigen Sie die Nummer jedes Rückgangsbereichs an

Wir können die ursprüngliche case..when -Anweisung erweitern, indem wir weitere Bedingungen zum Aufteilen des Bereichs hinzufügen und schließlich group by wir die Werte entsprechend dem abnehmenden Bereich summieren. Dieses sql ist etwas kompliziert. Sie können es bei Bedarf ändern. Es kann in tatsächlichen Tests verwendet werden.

Fall auswählen 
wenn Tage = Tage1 und (Anzahl1 - Anzahl)/Anzahl1 < 0,1 dann 0,1
wenn Tage = Tage1 und (Anzahl1 - Anzahl)/Anzahl1 > 0,1 und (Anzahl1 - Anzahl)/Anzahl1 < 0,2 dann 0,2
wenn Tage = Tage1 und (Anzahl1 - Anzahl)/Anzahl1 > 0,2 und (Anzahl1 - Anzahl)/Anzahl1 < 0,3 dann 0,3
wenn Tage = Tage1 und (Anzahl1 - Anzahl)/Anzahl1 > 0,3 und (Anzahl1 - Anzahl)/Anzahl1 < 0,4 dann 0,4
wenn Tage = Tage1 und (Anzahl1 - Anzahl)/Anzahl1 > 0,4 ​​und (Anzahl1 - Anzahl)/Anzahl1 < 0,5 dann 0,5
wenn Tage = Tage1 und (Anz.1 - Anz.)/Anz.1 > 0,5 dann 0,6
 sonst 0, Ende als diff, count(*) als diff_nums
aus 
(Wählen Sie count(*) als Nums, date_format(log_time,'%Y-%m-%d %h') als Tage ab Test, wobei 1 und log_time >='2020-03-20 00:00:00' und log_time <= '2020-04-20 00:00:00' nach Tagen gruppieren) als m,
(Wählen Sie Anzahl(*) als Nums1, Datumsformat (Datumssub (Datumsformat (log_time), „%Y-%m-%d %h“), Intervall -1 Stunde, „%Y-%m-%d %h“) als Tage1 aus Test, wobei 1 und Logzeit >= „2020-03-20 00:00:00“ und Logzeit <= „2020-04-20 00:00:00“ nach Tagen1 gruppieren) als n nach Differenz gruppieren, wobei die Differenz >0 ist;

Ergebnis:

+------+----------+
| Unterschied | Unterschiedszahlen |
+------+----------+
| 0,1 | 360 |
| 0,2 | 10 |
| 0,3 | 1 |
| 0,4 | 1 |
+------+----------+

Abschluss

1. sql ähnelt eigentlich einem Programmcode. Durch Aufteilen und schrittweises Kombinieren der Anforderungen können die meisten Anforderungen erreicht werden. Wer am Anfang schüchtern ist, kommt natürlich nicht mit dem Schreiben klar.
2. Es wird jedoch im Allgemeinen nicht empfohlen, sql zum Schreiben komplexer Berechnungen zu verwenden. Es ist schneller, sie mithilfe von Programmen zu schreiben. Je komplexer sql , desto geringer die Effizienz.
3. DBA Klassenkameraden sind manchmal unzuverlässig, also müssen Sie sich immer noch auf sich selbst verlassen

Ergänzende Einführung: Der Unterschied zwischen der MySQL-Datenbankzeit und der tatsächlichen Zeit beträgt 8 Stunden

url=jdbc:mysql://127.0.0.1:3306/somedatabase?characterEncoding=utf-8&serverTimezone=GMT%2B8

Fügen Sie &serverTimezone=GMT%2B8 nach der Datenbankkonfiguration hinzu

Dies ist das Ende dieses Artikels über MySQL-Abfragen der Differenz zwischen stündlichen Daten und vorherigen stündlichen Daten. Weitere relevante Inhalte zu MySQL-Stundendatendifferenzen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!