Aug 21, 2023

1248

MySQL - Anzahl Aufträge rollierend aufzählen

Hallo zusammen,

ich stehe aufm Schlauch und komme nicht weiter

. Vieleicht kann mir einer helfen.

Ziel: für die Marketingabteilung sollte ich das Kaufverhalten der Kunden analysieren bzw. Status festlegen. z.B. kauft der Kunde in letzten 365 Tagen 10x ein = Kunde, 50x = Stammkunde usw.. = ist eigentlich einfach!

Problem: Ich habe eine Tabelle wo alle Aufträge gelistet sind und bekomme es nicht hin, für jeden Tag einen Status für den Kunden rückblickend festzuschreiben (Kunde kann ja letztes Jahr 50x gekauft haben und dieses Jahr nur 10x == Statusänderung).

Tabelle auftrag:
KdNr;Auftragsnr;Datum;Kaufverhalten (NEU)
1; 55; 20.1.2022; 1
2; 56; 20.1.2022; 1
1:57; 20.1.2022; 2 (Auftrag 57 + 55)

im Grunde soll es zum jeden Kaufdatum die Anzahl der Aufträge des Kunden aus letzten 365 Tagen in die Spalte Kaufverhalten schreiben/ausgeben...

Ich würde diese Daten gerne in einer MSAccess abfragen wollen, deswegen fest in die DB schreiben (Entwicklung des Kaufverhaltens).

Oder habt ihr andere Ideen wie ich das "darstellen" soll....?

Please also mark the comments that contributed to the solution of the article

Content-Key: 41515818168

Url: https://administrator.de/contentid/41515818168

Printed on: April 27, 2024 at 08:04 o'clock

10 Comments

Latest comment

Moin,

was du brauchst, ist ein COUNT(), DATEPART() sowie ein GROUP BY

SELECT 
  DATEPART('year', Datum) as Jahr  
  , KdNr
  , COUNT(Auftragsnr) as anzahl
  , CASE WHEN COUNT(Auftragsnr) >= 50 THEN 'Stammkunde' ELSE 'Kunde' END as Kundentyp  
FROM auftrag

GROUP BY
  KdNr
  , DATEPART('year', Datum)  

bzw. bei "... kauft der Kunde in letzten 365 Tagen .. ein ..."

SELECT 
  KdNr
  , COUNT(Auftragsnr) as anzahl
  , CASE WHEN COUNT(Auftragsnr) >= 50 THEN 'Stammkunde' ELSE 'Kunde' END as Kundentyp  
FROM auftrag

WHERE
  Datum >= DATE_ADD(CURDATE(), INTERVAL -365 DAY)

GROUP BY
  KdNr

Edit: kleinere Fehler korrigiert

Hallo,

ich würde einfach die Aufträge pro Kunde zählen. und dann per Case auf diesen Wert die Ausgabe ob Kunde, Stammkunde machen und das dann gruppiert nach Jahr.

Gruß,
Thomas

Und bitte nicht in die DB rein schreiben, so einen Pippikram kann man live berechnen. Wenn das so leistungsintensiv wird das es nicht mehr geht braucht man auch das know how ein Auswertungssystem zu bauen, da kann man dann auch Berechnungen zwischenspeichern aber nicht einfach irgendwo an eine Kundentabelle dran flanschen.

Vielen Dank em-pie! hat mir sehr geholfen

@ukulele-7: ist mir bewusst, ich habe über 800k Datensätze und nur begrenzte "Möglichkeiten". Werde es in BD festschreiben, derzeit nicht anders umsetzbar.

Zitat von @bicheck:

Vielen Dank em-pie! hat mir sehr geholfen

@ukulele-7: ist mir bewusst, ich habe über 800k Datensätze und nur begrenzte "Möglichkeiten". Werde es in BD festschreiben, derzeit nicht anders umsetzbar.

Hallo,
du könntest dir aber auch einfach einen VIEW bauen, und den dann abfragen

grüße

Zitat von @bicheck:
Vielen Dank em-pie! hat mir sehr geholfen

Kein Ding

@ukulele-7: ist mir bewusst, ich habe über 800k Datensätze und nur begrenzte "Möglichkeiten". Werde es in BD festschreiben, derzeit nicht anders umsetzbar.

Das heisst, du willst für jeden neuen Kalendertag zu jedem Kunden die verdichteten Daten der über 800.000 Datensätze wegschreiben. Das ist ja wahnsinn.

Also wenn es ca. 10.000 Kunden sind, macht das somit pro Tag ca. 10.000 Datensätze, im Jahr also 3.650.000 Datensätze, die du immer mitschleppst. Denn du weisst ja nicht, wann das Marketing die Daten aufrufen will.

Mache das als View (oder lasse s als View vom DB-Administrator) anlegen. Dann ist es egal, wann die Makketing-Fuzzis die Ansicht aufrufen, die bekommen immer die aktuellsten Daten der letzte 365 Tage

Das muss wirklich jeder selber wissen, ich denke mal es geht nicht darum alle Zustände der letzten X Jahre nach zu halten sondern tagesaktuell die letzten 365 Tage zu berechnen. Kann man machen mit dem gleichen Code als Berechnungsgrundlage. Dann aber bitte nicht mit Triggern aktuell halten sondern wirklich nur täglich in einer eigenen Tabelle für jeden Kunden aktuallisieren.

Ansonsten ist vielleicht auch eine materialized view für dich interessant.

Das ist ja die Geschichte, für die letzte 365 Tage habe ich bereits alles aufbereitet, die kennen den Status.

Was die wollen: die wollen sehen bzw. auswerten wie oft ein Kunde den Status ändert UND wollen das ganze als Verlauf angezeigt bekommen. z.B. letzte Woche war der Kunde = Kunde, danach war der Stammkunde, dann wurde er wieder Kunde usw ...

Ich muss quasi zum jeden Einkauf vom Kunden einen Status hinterlegen, wie stand er zu diesem Zeitpunkt. Das ist das was ich erreichen muss. Jeder Einkauf = neuer Status...

Ich muss quasi zum jeden Einkauf vom Kunden einen Status hinterlegen, wie stand er zu diesem Zeitpunkt. Das ist das was ich erreichen muss. Jeder Einkauf = neuer Status...

Dann mache es mit nem Cross Apply (Link). Teste das aber, könnte "teuer" (= performancehungrig) werden:

SELECT 
  a.Kunde as KUNDE, a.Auftrag as AUFTRAG, a.DATUM as Eingangsdatum
  , astat.anzahl, astat.Kundentyp

FROM auftrag as a

CROSS APPLY (SELECT 
      t2.KdNr
      , COUNT(t2.Auftragsnr) as anzahl
      , CASE WHEN COUNT(Auftragsnr) >= 50 THEN 'Stammkunde' ELSE 'Kunde' END as Kundentyp    
    FROM auftrag t2
    WHERE t2.Datum >= DATE_ADD(a.Datum, INTERVAL -365 DAY) and t2.kdNr = a.Kdnr
    GROUP BY t2.KdNr) as astat

CROSS APPLY ist schon gut, eventuell geht auch lag() oder lead(). Da braucht man wirklich einen großen Datensatz um auch mal die Geschwindigkeit beurteilen zu können.

German solved Question Development Databases