SELECT-Anweisdung mit CEILING und gewichteter Gesamtmenge

Question

Guten Abend,

ich möchte gern folgendes realisieren, habe aber irgendwie noch nicht ganz die passende Idee gefunden:

Es gibt eine Tabelle mit folgenden Datensätzen:

Kunde          Anteil       Gesamtmenge
---------------------------------------------
Kunde A        20%          21
Kunde B        25%          21
Kunde C        20%          21
Kunde D        20%          21
Kunde E        15%          21

Jetzt möchte ich gern mit einem SELECT die entsprechenden ganzzahligen Anteile an der Gesamtmenge je Kunde herausbekommen, einzige Bedingung ist, dass jeder Kunde mit mindestens 1% muss auch mindestens einen ganzzahligen Wert bekommen.

Meine Idee war nun folgende:

Kunde          Anteil   Ergeb.(ROUND)     Ergeb.(erwartet)
--------------------------------------------------------
Kunde A        20%      4                        4
Kunde B        25%      5                        6  
Kunde C        20%      4                        4
Kunde D        20%      4                        4
Kunde E        15%      3                        3

In diesem Beispiel ist die Gesamtmenge = 21, nach der ROUND(VALUE,0)-Methode wird jedoch so ungünstig gerundet, sodass am Ende die Gesamtsumme nur noch 20 beträgt. Es soll jedoch so gerundet werden, sodass die GEsamtsumme wieder stimmt.

Wäre jetzt noch ein Datensatz mit z.B. 2% (=0,42) dabei, würde ROUND abrunden auf 0, es soll jedoch in diesem Fall (da ein Prozentwert verteilt wurde) zwingend auf 1 gerundet werden (mittels CEILING etc.).

Kann mir hier jemand einen Tipp geben wie ich die "Gesamtsummen"- und die "mindestens 1 sobald ein Prozentwert existiert"-Problematik realisieren kann?

Viele Grüße,

Andy

Answer 1

Hallo Andy,

dass Du keine Lösung dafür gefunden verwundert nicht, denn es gibt keine, solange du rundest. Das gilt zumindest aus mathematischer Sicht und wird deutlich, wenn du mal ein paar extreme Zahlenbeispiele durchrechnest:

- 2 Kunden mit je 50% und einer ungeraden Gesamtmenge
- ganz viele Kunden mit 2%; Gesamtmenge beliebig

Dass zB der erste Fall mathematisch gar nicht entstehen kann (wenn Kunden nur ganze Stückzahlen kaufen können), ist hier kein Argument, denn das können Deine Werte auch nicht. Wie kommst du darauf, bei dem 25%Kunden "6" statt "5" zu erwarten? Hast du irgendwo vielleicht doch die (Original-)Zahlen, die dann irgendwann zu den Prozentwerten umgerechnet wurden? Warum kannst Du dann nicht mit diesen Werten arbeiten? Und wieso wurde aus "6 von 21" 25% errechnet (=28,5% ... also 30, wenn ich auf 5er runde)?

Ein Ansatz, den ich bei zu erwartenden Rundungsproblemen verfolge, ist, zuerst die kleinen Werte zu verarbeiten und bei den großen dann die resultierenden Fehler in Kauf zu nehmen. D. h. der letzte Wert ergibt sich nicht durch Ausrechnen aus Gesamtmenge und Prozentanteil, sondern schlicht aus dem Rest, der noch nicht verteilt wurde (Das würde auch die 25% erklären).

Aber sowas wirst du sicher nicht mit einem einfachen (oder auch komplexen) SQL-Befehl hinbekommen, sondern es in eine Stored Procedure verpacken müssen.

Gruß
Jürgen

Answer 2

Hallo Jürgen,

genau so meinte ich es:

Zuerst muss geschaut werden, ob ein Kunde mindestens 1% hat und somit mindestens 1 bekommt, anschließend werden die restlichen Prozentwerte bedient und zum Schluss der größte mit dem Rest (bei Kunde 25% statt 5 nun 6).

Wahrscheinlich hast du Recht und ich sollte das in einer SP abfangen, Schritt für Schritt. Nur eine Frage noch:

Warum stimmt die Gesamtsumme beim ROUND nicht bzw. bei deinem "2 Kunden zu je 50% und ungerade Gesamtmenge" Beispiel müsste dann das ROUND doch auch einen auf 10 und einen auf 11 aufteilen? Oder achtet ROUND nicht auf die Gesamtmenge?

Viele Grüße,

Andy

Answer 3

Hallo Andy,

ROUND rundet eine Zahl. Woher die kommt (sprich: aus welcher Gesamtmenge) interessiert ROUND nicht (wenn du dir die Parameterliste anschaust: woher sollte die Funktion das auch wissen). Es wird also bei gleichem Eingabewert (50% von 21 = 10,5) immer das gleiche Ergebnis ausgeworfen (immer 11 und nicht einmal 10 und einmal 10).

Zu meinem Beispiel: Du bekommst bei beiden 50% Kunden dieselbe Teilmenge. Wenn du diesen (auf eine ganze Zahl gerundeten) Wert mit 2 multiplizierst (die Anzahl der Kunden) ergibt das immer eine gerade Zahl ... also nicht deine Ausgangsgesamtmenge (in deinem Beispiel 22 statt 21).

Entsprechendes gilt für die vielen Kunden mit sehr kleinem Wert. Wenn du rundest (Teilmenge = 0) haben alle diese kleinen Kunden zusammen "Nichts" gekauft; wenn Du zwangsweise auf Teilmenge = 1 erhöhst, haben diese Kunden ggf. mehr gekauft, als die Gesamtmenge hergibt.

Gruß
Jürgen

Answer 4

Hallo Zusammen!

Der Ursprung des Problems dürften ja die gerundeten Prozentwerte sein. Bei 6 von 21 komme ich immerhin auf 28,571428 (Periode) Prozent. Die per Round ermittelte Zahl passt eher zu 23,809523 (Periode) Prozent.

Wenn man zweimal rundet, wird das Ergebnis eben nicht besser. Ich denke auch, dass hier nur mit iterativen Verfahren Werte berechnet werden können, die halbwegs plausibel, aber nie korrekt sind.

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP - Blog

Answer 5

Hallo,

ich werde in der SP so vorgehen, dass ich zu allererst schaue ob ein Anteil >0 aber <1 existiert, dann bekommt diese Kombination den Anteilswert "1".

Anschließend nehme ich die verbleibende Gesamtmenge und die bisherigen Prozentwerte und errechne anteilsmäßig neue Prozentwerte je verbleibendem Kunde. Danach errechne ich die neuen Anteilswerte.

Beim Runden müsste ich mir da jedesmal die Rundungsdifferenz (Ergebnis 10,5 => Runden auf 11 -> Rundungsdifferenz: +0,5) merken und dann erneut die verbleibende Gesamtmenge ermitteln. Gibt es hier vielleicht irgendwo ein Beispiel wie man sich diese Rundungsdifferenzen merkt und überträgt für die nächste Rundung? Mir fehlt hier irgendwie der Ansatz :-(

Viele Grüße,

Andy

Answer 6

Hallo,

ich beschreibe mal verbal einen Ansatz, der wohl am ehesten in die numerische Mathe passt. Ich hoffe, es ist nachvollziehbar:

1. Du sortierst deine Kunden aufsteigend nach Prozentwerte (Anteil)
    Zu Beginn ist der Rest-Prozentwert = 100% und der Rest-Gesamtwert = 21 (aus deinem Beispiel).
2. Aus dem Kundenanteil und dem Rest-Gesamtwert berechnest Du (mit dem Mindestwert 1) den Kundenanteil
3. Den Kunden-Prozentwert ziehst du vom Rest-Prozentwert ab und erhältst den neuen Rest-Prozentwert (zB 100% - 2% = 98%); den errechneten Kundenanteil ziehst du vom Rest-Gesamtwert ab und erhältst den neuen Rest-Gesamtwert (hier: 21 - 1 = 20)
4. Der Kunden-Prozentwert ergibt sich aus: originaler Prozentwert / Rest-Prozentwert (zB aus 5% wird 5% / 98% => 5,1%). Hiermit steigst Du bei 2. wieder ein.

Auf diese Weise verteilst du alle deine 'Dinge' ohne Rest, musst dir keine Überträge merken, ... Nur verstehen musst du das da oben. Ich füge mal einen Screenshot einer Excel-Liste bei:

Als Verbesserung könntest Du alle Kunden mit identischen (Original-)Anteilen zusammen behandeln, damit nicht - wie im Beispiel - die beiden 10%er (von der zufälligen Reihenfolge abhängig) unterschiedlich behandelt werdne.

Ich hoffe, das hilft.

Jürgen

Answer 7

Hallo Jürgen,

vielen Dank für den Ansatz, genau so habe ich es umgesetzt, die Verteilung läuft nun wie gewünscht als SP und die Gesamtsummen passen ebenfalls.

Viele Grüße,

Andy