Durchschnittlicher höhe des Belegs beim Verkauf von Backwaren

S

Storm

Benutzer
Beiträge
22
Hallo alle zusammen,

ich habe ein kleines Problem für das ich allein leider keine wirkliche Lösung finde.

Das Umfeld sieht so aus das eine Datenbank alle Verkäufe speichert. Allerdings wohl nicht als Kassenbons zusammen gefasst (zumindest finde ich keine entsprechende Tabelle) sondern nur als einzelne Verkäufe.
Es werden diverse Daten bei jedem Verkauf erfasst, Uhrzeit, Artikel Nummer, Artikel Kategorie, Preis, KeyCard etc.. Man kann davon ausgehen das jeder Kunde nur 1 mal während des Zeitraums einkauft.

Die Aufgabe ist es nun den Durchschnittlichen Wert des Gesamten Einkaufs zu erfassen, sofern ein Artikel der Categorie 6, 7 oder 10 (Backwaren, Getränke, heiße Theke) gekauft werden. Das ganze noch für eine bestimmte Kasse und einen bestimmten Zeitraum.

Als kleines Beispiel:
A kauft eine Semmel und 5 Hanutas, zahlt 5€ (ist relevant)
B kauft 4 Snickers und zahlt 3,50€ (ist irrelevant)
C kauft ein Wasser für 1€ (ist relevant)

alsowäre mein Ergebnis 3€ ( (1+5)/2 = 3 )

Meine erste Idee wäre jetzt ein "Group By" der KeyCard um quasi einen Beleg zu erzeugen. Dabei müssten die einzelnen Preise addiert werden. Hier komm ich nicht wirklich drauf wie ich den nun das "SUM" der Preise und am besten auch gleich die Durchnschnitts ermitllung mit reinbringe.
Die Eingrenzung des Datums sollte kein Problem sein. Bei der Eingrenzung der Artikel Kategorien wirds schon schwieriger, da sobald einer der gekauften Artikel aus einer der 3 genannten Kategorien stammt soll ja der gesamte Einkauf berücksichtigt werden.
Wie könnte den hier der Code Beispielhaft aussehen?

Ich hoffe ich habs verständlich erklärt, ansonsten fragt bitte nach.
Entschuldigt falls es total simpel ist und ich nur den Wald vor lauter Bäumen nicht seh.

MFG
Georg
 
Werbung:
Du hast also:

Code: 
test=*# select * from einkauf ;
 wer | kat | preis
-----+-----+-------
 a  |  6 |  1
 a  |  9 |  4
 b  |  1 |  3
 c  |  7 |  1
(4 Zeilen)

Ich hab mal für Hanuta und Snickers andere Produktkategorien erfunden.

Code: 
test=*# select avg(preis) from (select wer,array_agg(kat),sum(preis) as preis from einkauf group by wer having array_agg(kat) && array[6,7,10]) foo;
  avg   
--------------------
 3.0000000000000000
(1 Zeile)

Ich nutze für solche Sachen immer wieder gerne Array-Funktionen, hier prüfe ich, ob das Array der aggregierten Produkte eines Kunden Elemente aus dem Array der zu betrachtenden Produkte enthält. Ich fürchte aber, M$SQL kann das nicht. Oder?
 
falls das mit dem Array nicht geht ...

Code: 
test=*# select avg(sum) from (select wer, sum(preis) from einkauf where wer in (select wer from einkauf where kat in (6,7,10)) group by wer) foo;
  avg   
--------------------
 3.0000000000000000
(1 Zeile)
 
Nein MSSQL hat kein "direktes" Equivalent zu array_agg() von PG oder group_concat() von MySQL. Das ist zumindest mein letzter Stand und wird z.B. hier gut beschreiben:
String Aggregation in PostgreSQL, SQL Server, and MySQL - Postgres OnLine Journal

Ersatzweise ginge das hier:
Code: 
WITH t(id,datumzeit,keycard,anzahl,produkt,kategorie,preis) AS (
   SELECT   1,getdate(),'A',1,'Semmel',6,2.50     UNION ALL
   SELECT   2,getdate(),'A',5,'Hanuta',1,0.50     UNION ALL
   SELECT   3,getdate()+1,'B',4,'Snickers',1,0.875   UNION ALL
   SELECT   4,getdate()+2,'C',1,'Wasser',7,1
   )
SELECT   sum(t3.summe) AS summe,
     avg(t3.summe) AS durchschnitt
FROM   (

SELECT   t1.datumzeit,
     t1.keycard,
     sum(t1.anzahl * t1.preis) AS summe,
     stuff((   SELECT   '#' + cast(t2.kategorie AS VARCHAR(10))
         FROM   t t2
         WHERE   t2.datumzeit = t1.datumzeit
         AND     t2.keycard = t1.keycard
         FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,0,'') AS kategorie
FROM   t t1
GROUP BY t1.datumzeit,t1.keycard

     ) t3
WHERE   t3.kategorie LIKE '%#6%'
OR     t3.kategorie LIKE '%#7%'
OR     t3.kategorie LIKE '%#10%'

Oder eben die letzte Variante:
Code: 
WITH t(id,datumzeit,keycard,anzahl,produkt,kategorie,preis) AS (
   SELECT   1,getdate(),'A',1,'Semmel',6,2.50     UNION ALL
   SELECT   2,getdate(),'A',5,'Hanuta',1,0.50     UNION ALL
   SELECT   3,getdate()+1,'B',4,'Snickers',1,0.875   UNION ALL
   SELECT   4,getdate()+2,'C',1,'Wasser',7,1
   )
SELECT   sum(t1.anzahl * t1.preis) AS summe,
     sum(t1.anzahl * t1.preis) / count(DISTINCT datumzeit) AS durchschnitt
FROM   t t1
WHERE EXISTS (   SELECT   1
         FROM   t t2
         WHERE   t2.kategorie IN ( 6,7,10 )
         AND     t2.datumzeit = t1.datumzeit
         AND     t2.keycard = t1.keycard )
Im WITH-Teil stehen hier immer nur deine Beispieldaten, den brauchst du nicht sondern musst t durch deine Tabelle ersetzen. Problematisch kann noch der Durchschnitt sein, je nachdem wie deine Tabelle aussieht (Preis pro Stück oder pro Position oder irgendwie anders...), eventuell sollte man auch hier in zwei Schritten rechnen.
 
Hi,

vielen Dank erstmal euch beiden.
Ich bin beruhigt dass, das Problem wirklich etwas zu kompliziert für mich gewesen wäre.

Nur nochmal kurz als Beispiel wie die Daten vorliegen:
Date - KeyCard - CategoryID - Price - ArticleID
2016-08-01 07:55:53 - 1234 - 5 - 0.75 - 543
2016-08-01 07:55:53 - 1234 - 5 - 0.75 - 543
2016-08-01 07:55:53 - 1234 - 7 - 1.5 - 789

Das wäre jetzt ein Kunde gewesen der 3 Artikel gekauft hat, 2 Hanuta eine Semmel.
Theoretisch: diesen Einkauf (und jeden anderen der mindestens einen Artikel der 3 Warengruppen gekauft hat) mit gesamt Preis(3€) in ein imaginäres Array schreiben und dann den Durchschnittlichen im Array stehenden Einkaufspreis berechnen.
Oder anders formuliert: Wenn jemand etwas von der Heißen Theke (oder ein Getränk oder eine Backware) kauft, wieviel gibt er für seinen Einkauf aus?

Ich bin mir nicht sicher ob das so schon rübergekommen ist.

Bei deinem Code ukulele muss ich nur das t durch den Tabellennamen ersetzen oder? t1/2/3 erzeugt der Code und die Namen sind irrelevant?

Es tut mir leid wenn ich irgendwas ganz falsch verstehe, ist nich wirklich mein Fachgebiet.
 
Ist eigentlich nicht schwer:
Code: 
WITH deine_tabelle(id,[Date],KeyCard,ArticleID,CategoryID,Price) AS (
   SELECT   1,getdate(),'A','Semmel',7,1.50     UNION ALL
   SELECT   2,getdate(),'A','Hanuta',5,0.75     UNION ALL
   SELECT   3,getdate(),'A','Hanuta',5,0.75     UNION ALL
   SELECT   4,getdate()+1,'B','Snickers',5,100   UNION ALL
   SELECT   5,getdate()+2,'C','Wasser',7,1
   )
Code: 
SELECT   sum(t1.Price) AS summe_aller_einkaeufe,
     sum(t1.Price) / count(DISTINCT [Date]) AS durchschnitt_pro_einkauf
FROM   deine_tabelle t1
WHERE EXISTS (   SELECT   1
         FROM   deine_tabelle t2
         WHERE   t2.CategoryID IN ( 6,7,10 )
         AND     t2.[Date] = t1.[Date]
         AND     t2.KeyCard = t1.KeyCard )
Du musst nur den zweiten Teil ausführen und deine_tabelle mit deinem Tabellennamen ersetzen.

Schwer wirds erst wenn jetzt unterschiedliche Kassen zum exakt selben Zeitpunkt auf unterschiedliche KeyCards Einkäufe erfassen, die würden dann nur bei der Berechnung im Durchschnitt wie ein Einkauf behandelt. sum(DISTINCT spalte) kommt hier nur mit einer Spalte zurecht, du hast aber zwei Spalten die zusammen eindeutig sind. Um das zu vermeiden könnte man z.B.
Code: 
SELECT   sum(t1.Price) AS summe_aller_einkaeufe,
     sum(t1.Price) / count(DISTINCT checksum([Date],KeyCard)) AS durchschnitt_pro_einkauf
FROM   deine_tabelle t1
WHERE EXISTS (   SELECT   1
         FROM   deine_tabelle t2
         WHERE   t2.CategoryID IN ( 6,7,10 )
         AND     t2.[Date] = t1.[Date]
         AND     t2.KeyCard = t1.KeyCard )
verwenden.
 
Werbung:
Super vielen Dank für die Antwort, hatte bis jetzt Urlaub aber ich bau jetzt gerade den Code ein und es scheint zu klappen. Vielen Dank. :)
 
Zurück
Oben