Summe über die letzten 7 Tage, gruppiert nach Datum

[franz_hoff]

Hallo,

ich mühe mich gerade damit ab, ein SQL-Statement zu bekommen, mit welchem ich Daten wie folgt abfragen kann.

In einer Tabelle werden seit zwei Jahren Aufträge gespeichert. Alle Aufträge haben ein Erstellungsdatum. Nun möchte ich für alle Tage der aktuellen Woche die Gesamtanzahl aller - bis zum entsprechenden Tag - angelegten Aufträge bekommen.

Mit einem

SELECT 
    DATE_FORMAT(Datum,'%d.%m.%Y'), COUNT(*) AS anzahl 
FROM 
    Auftraege
WHERE 
    Datum >= SUBDATE(NOW(), weekday(NOW())) GROUP BY DATE_FORMAT(Datum,'%d.%m.%Y') ORDER BY Datum

erhalte ich zwar für alle Tage der aktuellen Woche eine Anzahl von Aufträgen, aber nur die, die am entsprechenden Tag angelegt wurden. In der Anzahl, die ich aber gern sehen möchte, sollen alle, bis zum entsprechenden Tag angelegten, Aufträge beinhaltet sein.

Bleibt mir da jetzt nur die Möglichkeit, die einzelnen Tage einer Woche in einer Schleife zu bestimmen und diese dann einzeln mit einen SELECT COUNT(*) FROM Auftraege WHERE DATUM = $datumAusDerSchleife abzufragen?

Oder geht das irgendwie noch 'eleganter'? :)

Danke

[MudGuard]

Hi,

In einer Tabelle werden seit zwei Jahren Aufträge gespeichert. Alle Aufträge haben ein Erstellungsdatum. Nun möchte ich für alle Tage der aktuellen Woche die Gesamtanzahl aller - bis zum entsprechenden Tag - angelegten Aufträge bekommen.

Betreff und Nachricht passen nicht zusammen - letzte 7 Tage vs. Tage der aktuellen Woche …

SELECT 
    DATE_FORMAT(Datum,'%d.%m.%Y'), COUNT(*) AS anzahl 
FROM 
    Auftraege
WHERE 
    Datum >= SUBDATE(NOW(), weekday(NOW())) GROUP BY DATE_FORMAT(Datum,'%d.%m.%Y') ORDER BY Datum

Du willst die Anzahl für alle betroffenen Tage, warum gruppierst Du dann nach Datum?

cu,
Andreas a/k/a MudGuard

[franz_hoff]

Betreff und Nachricht passen nicht zusammen - letzte 7 Tage vs. Tage der aktuellen Woche …

Hm, sorry. Für mich war eine Woche = 7 Tage. Aber ja, die Abfrage kann man ja auch an einem Mittwoch laufen lassen und nicht nur am Sonntag 😀 Also letztlich brauche ich die Tage der aktuellen Woche.

Du willst die Anzahl für alle betroffenen Tage, warum gruppierst Du dann nach Datum?

Hm, zum einem braucht ein Count(*) doch eine Gruppierung, oder?! Zum anderen ist das Feld "Datum" ein Datetime-Feld und ich brauche nur die Infos des Datums, die den Tag betreffen.

Habe ich einen massiven Denkfehler?!

[dedlfix]

Tach!

In der Anzahl, die ich aber gern sehen möchte, sollen alle, bis zum entsprechenden Tag angelegten, Aufträge beinhaltet sein.

Gruppieren bei SQL bedeutet, die Datensätze eindeutig in eine Gruppe zu sortieren. Ein Datensatz kann nicht Mitglied mehrerer Gruppen sein. Gruppieren ist also nicht zielführend.

Du möchtest stattdessen zu einem Datensatz (hier dem Datum) eine Menge anderer Datensätze zuordnen. Das ist ein kartesisches Produkt und das bekommt man, wenn man zwei Datenmengen joint. Zum zählen aber musst du die Daten des kartesichen Produkts dann über das Datum gruppieren.

dedlfix.

[franz_hoff]

Ok, dann schaue ich mir das mal unter diesen Gesichtspunkt an.

Vielen Dank!

[Rolf B]

Hallo franz_hoff,

deine Query ist eigentlich schon nahe dran. Kartesische Produkte sehe ich nicht als notwendig, da muss Dedlfix dein Anliegen falsch verstanden haben.

Änderungsvorschläge:

1. Du musst NOW() durch CURDATE() ersetzen, weil NOW() eine Uhrzeit enthält. Die NOW-Query verschlampt die Aufträge vom Montag (weil im Date-Feld die Uhrzeit 00:00:00 ist).

2. Wenn deine Datum-Spalte ein DATE Typ ist, kannst Du direkt danach gruppieren und brauchst kein DATE_FORMAT.

3. Der DATE_FORMAT Spalte solltest Du noch einen Aliasnamen geben.

SELECT 
    DATE_FORMAT(Datum,'%d.%m.%Y') AS ErstellDatum, COUNT(*) AS anzahl 
FROM 
    Auftraege
WHERE 
    Datum >= SUBDATE(CURDATE(), WEEKDAY(CURDATE()))
GROUP BY 
    Datum
ORDER BY
    Datum

Falls es vordatierte Aufträge gibt oder Du die Abfrage für eine frühere Woche machen willst, muss die Where-Bedingung noch erweitert werden:

WHERE
    Datum BETWEEN SUBDATE(CURDATE(),WEEKDAY(CURDATE()))
              AND ADDDATE(CURDATE(),6-WEEKDAY(CURDATE()))

Rolf

[dedlfix]

Tach!

deine Query ist eigentlich schon nahe dran. Kartesische Produkte sehe ich nicht als notwendig, da muss Dedlfix dein Anliegen falsch verstanden haben.

In dem Satz

Nun möchte ich für alle Tage der aktuellen Woche die Gesamtanzahl aller - bis zum entsprechenden Tag - angelegten Aufträge bekommen.

und dass historische Daten eine Rolle spielen, lese ich, dass da sowas rauskommen soll, wie: Bis Montag hatten wir 10 Auftrage, am Dienstag kamen drei neue, macht 13, die 2 vom Mittwoch dazu sind 15, und so weiter.

Die Query hat mit nicht viel geholfen, das Problem zu verstehen. Besser wäre gewesen, das Datenmodell (den relevanten Teil daraus) zu zeigen und ein paar Beispieldaten und wie das gewünschte Ergebnis aussehen soll.

dedlfix.

[Rolf B]

Hallo dedlfix,

okeeeh - da habe ich nicht genau gelesen. Ein Beispiel wäre sicherlich sehr nützlich gewesen.

Du denkst also, dass sowas benötigt wird?

Datum        Auftrag
==========   ===========================
29.04.2019   7 <div> mit Klassen und CSS
29.04.2019   2 <span>, Sonderpreis
01.05.2019   1 <form>, vergoldet
01.05.2019   2 <button>, JS-enabled
03.05.2019   1 <fieldset>, extrastark

Ergebnis:

Datum       Anzahl
29.04.2019   2
30.04.2019   2       ?
01.05.2019   4
02.05.2019   5       ?
03.05.2019   5

Frage wäre dann, ob die Zeilen mit dem Fragezeichen angezeigt werden sollen. Davon hängt ab, ob das SQL wüst oder extrawüst wird.

Rolf

[franz_hoff]

Wieder ein Sorry, dass ich mich da nicht entsprechend ausgedrückt habe.

Das Datenmodell ist sehr flach.

Eine einzige Tabelle mit den Feldern Id, Datum, Betreff, ... weitere Statusinformationen

Und es ist in der Tat so, wie dedlfix. geschrieben hat:

Bis Montag hatten wir 10 Auftrage, am Dienstag kamen drei neue, macht 13, die 2 vom Mittwoch dazu sind 15, und so weiter.

[Rolf B]

Hallo franz_hoff,

Bis Montag hatten wir 10 Auftrage

technische Feinspezifikation ist eine Sache für Korinthenkacker, sorry.

"Bis" Montag? Nicht "am" Montag? D.h. von wann bis Montag? Oder meintest Du "am" Montag? Ich hatte Dich so verstanden dass deine Auswertung von Montag bis Tagesdatum gehen soll. Und wenn die Auswertung am Montag stattfindet, dann wird nur der Montag ausgegeben.

Und es bleibt die Frage: Was ist mit dem Fall, dass an einem Tag keine neuen Aufträge gekommen sind. Muss die Query solche Tage ausweisen oder nicht (siehe mein Beispiel von gestern)?

Rolf

[franz_hoff]

Es soll immer die Anzahl der Aufträge ausgegeben werden, die an einem bestimmten Tag im System sind. Diese Zahl wird nie kleiner 0 sein.

Nehmen wir an, bis zum Sonntag, 28.4. waren es 430 Aufträge.

Am Montag kamen 3 dazu, 0 abgearbeitet.
Am Dienstag 5 neu, 1 abgearbeitet.
Am Mittwoch 1 neu, 0 abgearbeitet.
Am Donnerstag 0 neu, 0 abgearbeitet.

Dann sollte die Ausgabe am Montagabend so sein:
29.4. : 433
Dann sollte die Ausgabe am Dienstagabend so sein:
29.4. : 433
30.4. : 437

Dann sollte die Ausgabe am Mittwochabend so sein:
29.4. : 433
30.4. : 437
01.5. : 438

Dann sollte die Ausgabe am Donnerstagabend so sein:
29.4. : 433
30.4. : 437
01.5. : 438
02.5. : 438

Edit Rolf B: Zeilenumbrüche

[Rolf B]

Hallo franz_hoff,

gut. Dann brauchst Du eine Art "Listengenerator", wenn Du das mit SQL machen willst. Das geht bspw. über ein UNION Statement, oder klassisch über eine Temp-Table mit einer INT-Spalte, die die Werte von 0-6 enthält.

Dein Beispiel wirft übrigens eine neue Frage auf. Du hast am Dienstag 5 Aufträge bekommen, einen Auftrag abgearbeitet und die Anzahl steigt um 4. Was heißt das denn für die Zahl "430" vom Sonntag? Sind das alles offene Aufträge, die noch abzuarbeiten sind?

Daher nun die Frage nach den technischen Rahmenbedingungen

• welche Version von MYSQL verwendest Du?
• bist Du darauf angewiesen, diese Abfrage in reinem SQL zu machen? Wovon wird das Ergebnis der Abfrage weiterverarbeitet? Ein Programm, ein Reporting-Tool, oder keine weitere Verarbeitung?
• kannst Du eine Routine erstellen (Stored Procedure)?
• kannst Du Variablen nutzen?

Der Generator für eine Datumsliste sähe mit einer Variablen und einem UNION so aus:

SET @anfang = SUBDATE(CURDATE(), WEEKDAY(CURDAT()));

SELECT @anfang as Datum
UNION SELECT ADDDATE(@anfang, 1)
UNION SELECT ADDDATE(@anfang, 2)
UNION SELECT ADDDATE(@anfang, 3)
UNION SELECT ADDDATE(@anfang, 4)
UNION SELECT ADDDATE(@anfang, 5)
UNION SELECT ADDDATE(@anfang, 6)

Sowas kann man dann als Treiber für einen Subselect-Query verwenden:

SET @anfang = SUBDATE(CURDATE(), WEEKDAY(CURDAT()));

SELECT Datum, (SELECT COUNT(*) from auftraege a WHERE a.Datum <= t.Datum) as Anzahl
FROM (SELECT @anfang as Datum
      UNION SELECT ADDDATE(@anfang, 1)
      UNION SELECT ADDDATE(@anfang, 2)
      UNION SELECT ADDDATE(@anfang, 3)
      UNION SELECT ADDDATE(@anfang, 4)
      UNION SELECT ADDDATE(@anfang, 5)
      UNION SELECT ADDDATE(@anfang, 6)) t
WHERE t.Datum <= CURDATE()

Ohne die @anfang Variable müsstest Du überall da, wo @anfang steht, den SUBDATE-Ausdruck hinkopieren. Darum die Frage ob Du Variablen oder Routinen verwenden kannst.

Eine Query dieser Art hat aber immense Nachteile. Sie läuft pro Tag über den größten Teil der Auftragstabelle und summiert immer wieder neu auf. Deine Datenbank wird darüber nicht happy sein. Man kann diesen Rubbeln über die gesamte Tabelle optimieren, indem man alle Aufträge vor dem Startdatum einmal zählt und dann nur noch die Woche betrachtet:

SET @anfang = SUBDATE(CURDATE(), WEEKDAY(CURDAT()));
set @basis = (select count(*) from auftraege where datum < @anfang);

SELECT Datum, @basis+(SELECT COUNT(*) from auftraege a 
               WHERE a.Datum BETWEEN @anfang AND t.Datum) as Anzahl
FROM (SELECT @anfang as Datum
      UNION SELECT ADDDATE(@anfang, 1)
      UNION SELECT ADDDATE(@anfang, 2)
      UNION SELECT ADDDATE(@anfang, 3)
      UNION SELECT ADDDATE(@anfang, 4)
      UNION SELECT ADDDATE(@anfang, 5)
      UNION SELECT ADDDATE(@anfang, 6)) t
WHERE t.Datum <= CURDATE()

Ob so etwas geht, hängt von der Umgebung ab, in der Du das ausführst.

Rolf