[SQL] Summierte Prozente, die einen Wert x nicht überschreiten dürfen

[Reisone]

Ok, folgendes Problem. Ich habe eine Reihe berechneter Prozent-Angaben. Zusammen ergeben sie logischerweise 100%. Sortiert nach Prozent (absteigend) sind sie. Nun hätte ich gern alle Werte, die zusammen die top 20% ausmachen.
Hat jemand ne Vorstellung wie das am Besten geht?

 

[Reisone]

Hat niemand eine idee?

Falls mehr informationen benötigt werden, einfach posten...

 

[DeletedUser]

In Verbindung mit Having geht das was du willst. Habs mal mit ner Tabelle von mir ausprobiert.

http://dev.mysql.com/doc/refman/5.1/de/select.html
http://www-user.tu-chemnitz.de/~dgr/mysql-einf/teil158.html

 

[Reisone]

nein, geht leider nicht. Zumindest gaben meine Lösungen immer zu viele werte aus. Das problem in diesem Fall ist, dass ich mehrer Werte (Prozente) habe, die >= 20 sind.

 

[DeletedUser]

Nunja, dann wirst du das wohl mit PHP oder was auch immer du benutzst machen.

 

[DeletedUser]

Das Problem ist (wenn ich Dich richtig verstanden habe) dass es mehr als eine Lösung geben kann, also musst Du irgendwie heuristisch vorgehen...

 

[DeletedUser105688]

Ausgangspunkt ist folgender (mal ein wenig reduziert, um es etwas zu vereinfachen): es gibt eine Tabelle, die als Felder "Artikelnummer", "Artikel-Kategorie" und "Umsatz" enthält.

Nun soll in der Abfrage der Umsatz je Kategorie kumuliert werden und der prozentuale Anteil der Kategorie am Gesamtumsatz ermittelt werden, mit absteigender Sortierung nach Umsatzanteil. Soweit kein Problem.

Abschließend soll das Ganze jedoch so erweitert werden, daß nicht alle Datensätze angezeigt werden sollen, sondern nur die Datensätze, deren Anteil am Gesamtumsatz nicht mehr als 20% beträgt. Also die 20% der umsatzstärksten Artikelgruppen.

PHP scheidet als Lösung aus. Es soll ausschließlich per SQL gelöst werden.

 

[Reisone]

Ausgangspunkt ist folgender (mal ein wenig reduziert, um es etwas zu vereinfachen): es gibt eine Tabelle, die als Felder "Artikelnummer", "Artikel-Kategorie" und "Umsatz" enthält.

Nun soll in der Abfrage der Umsatz je Kategorie kumuliert werden und der prozentuale Anteil der Kategorie am Gesamtumsatz ermittelt werden, mit absteigender Sortierung nach Umsatzanteil. Soweit kein Problem.

Abschließend soll das Ganze jedoch so erweitert werden, daß nicht alle Datensätze angezeigt werden sollen, sondern nur die Datensätze, deren Anteil am Gesamtumsatz nicht mehr als 20% beträgt. Also die 20% der umsatzstärksten Artikelgruppen.

PHP scheidet als Lösung aus. Es soll ausschließlich per SQL gelöst werden.

Soweit korrekt. Die Tabelle, um das vlt. noch zu ergänzen, muss erst über einen Join aus zwei Tabellen erstellt werden.

 

[DeletedUser101689]

Abschließend soll das Ganze jedoch so erweitert werden, daß nicht alle Datensätze angezeigt werden sollen, sondern nur die Datensätze, deren Anteil am Gesamtumsatz nicht mehr als 20% beträgt. Also die 20% der umsatzstärksten Artikelgruppen.

Ich versteh zwar nicht, warum das bedeutet dass das die Umsatzsärksten Artikelgrupppen sind, aber von der Logik her:

WHERE (t.umsatz / SUM(t.umsatz)) <= 0.2

Oder falsch verstanden?

 

[Reisone]

falsch verstanden.

Wir haben x Umsätze. Diese verteilen sich auf verschiedene Produktkategorien.

Ich möchte jetzt wissen, welches die Umsatzstärksten Produktkategorien sind. Um einzugrenzen, wieviele ich angezeigt haben möchte, ist festgelegt, dass diese Produktkategorie(n) zusammen 20% des Gesamtumsatzes ausmachen sollen. Die benötigten Informationen stehen in zwei Tabellen.

D.h. ich muss erst die Tabellen joinen (in meinen Fall ist es die ProduktID)(kein Problem), errechne dann den gesamtumsatz (kein Problem), die Umsätze je Produktkategorie (auch kein Problem), errechne die Anteile am Gesamtumsatz (kein Problem) und sortiere das ganze absteigend (ebenfalls leicht).

Jetzt habe ich eine Liste, den größten Wert oben, die mir alle Produktkategorien anzeigt und den Anteil am Gesamtumsatz.

Das Problem besteht nun darin, herauszufinden, welche Produktkategorien nun 20% des Gesamtumsaztes ausmachen, denn ich will, falls es mehre Kategorien gibt, die mehr als 20% des Gesamtumsatzes ausmachen, nur den Umsatzstärksten. Rein logisch betrachtet muss folgendes passieren:

Nimm dir den ersten Prozentwert, schaue, ob dieser größergleich 20 ist. Ja --> Ende (zeige nur den ersten an) Nein --> addiere zu dem Wert der ersten Zeile den Wert der zweiten und schaue, oder der Wert nun größergleich 20 ist. Ja --> Ende (zeige die ersten beiden Zeilen an) Nein --> USW.

Aber wie zur Hölle geht das mit SQL?

 

[DeletedUser101689]

Du hast dann also eine temporäre Tabelle (nach dem JOIN) die so aussieht:

id | kategorie | anteil_an_gesamtumsatz

Äh geordnet nach "anteil_an_gesamtumsatz" absteigend sollte ein LIMIT 1 genügen?

 

[Reisone]

naja, sie sieht nur so aus:

kategoriename | Prozent

Nein, Limit 1 würde bedeuten, ich kenne das Ergebnis. Aber das kenne ich ja strenggenommen nicht. Das muss schon dynamisch sein.

 

[DeletedUser101689]

naja, sie sieht nur so aus:

kategoriename | Prozent

Nein, Limit 1 würde bedeuten, ich kenne das Ergebnis. Aber das kenne ich ja strenggenommen nicht. Das muss schon dynamisch sein.

Ja, aber wenn du nach Prozent sortierst ist doch automatisch bei absteigender reihenfolge die erste row die umsatzstärkste? Die nächsten haben ja weniger anteil am Gesamtumsatz. Dementsprechend reicht ein eifnaches Limit?

 

[Reisone]

Nein. wenn die Umsatzstärkste Kategorie nur 5% vom Gesamtzumsatz ausmacht, dann muss ich entsprechend viele Kategorien addieren (und Anzeigen), um auf die geforderten 20% vom Gesamtumsatz zu kommen.

 

[DeletedUser101689]

Achso, verstanden.

Also gibt es jetzt folgende Fälle:

A) Schon der erste macht mehr als 20% aus -> es wird nur dieser Datensatz ausgegeben
B) Eine Kombination von den n ersten DS macht genau 20% aus -> gebe diese aus
C) Man kommt nicht auf genau 20% -> Gebe soviele DS aus, solange die Summer der Prozentsätze < 20% ist

 

[Reisone]

genau.

 

[DeletedUser101689]

Hat mich irgendwie noch beschäftigt und so hab ich nochmal nen bisschen gegooglet und schließlich noch Hilfe im IRC geholt:

http://sqlfiddle.com/#!2/9c885/10

Das entscheidende Statement:

SELECT prosum
     , kategoriename
  FROM (
    SELECT @xsum := @xsum + prozent AS prosum
         , prozent
         , kategoriename
      FROM reisonne
     CROSS JOIN (SELECT @xsum := 0) AS v1
     ORDER BY prozent DESC
            , id
       ) AS v2
 WHERE prosum < 0.2
;

PS: Ich wusste auch nicht, was so alles mit SQL möglich ist

 

[Reisone]

Vielen Dank ersteinmal für deine Mühe.

Aber entweder ich bin zu dumm, oder das funktioniert nicht.... :S

 

[Reisone]

Der Ausgangsquery ist der folgende:

USE [Data_Star_Awesome]
SELECT  p.Unterkategorie ,
       ErtragInProzent = SUM(f.Verkaufswert) /
                   (SELECT SUM(Verkaufswert) FROM dbo.Verkaufsfakten) * 100

FROM dbo.DimProdukt AS p INNER JOIN dbo.Verkaufsfakten AS f ON p.ProduktID = f.ProduktID
GROUP BY p.Unterkategorie
ORDER BY ErtragInProzent DESC

Darum kannste gern versuchen deine Lösung zu stricken, dann kann ich dir sagen, ob es stimmt.


Und für alle, die denken, sie machen damit meine Aufgaben. Es war eine Aufgabe, die ich lösen sollte, ich kenne niemanden, der es bis dato geschafft hat und ich würde gern einfach wissen, wie das geht. Oder ob unser Prof einfach zu faul ist, zu schauen, ob seine Aufgaben lösbar sind...

 

[DeletedUser101689]

Kannst du auch mal so einen SQLfiddle zum testen erstellen, dann ist es einfacher. Aber ich mach gleich mal was.

 

[DeletedUser101689]

SELECT prosum
     , kategoriename
	FROM (
    SELECT @xsum := @xsum + prozent AS prosum
         , prozent
         , kategoriename
		  FROM (SELECT  p.Unterkategorie AS kategoriename,
						ErtragInProzent = (SUM(f.Verkaufswert) /(SELECT SUM(Verkaufswert) FROM dbo.Verkaufsfakten)) AS prozent
					FROM dbo.DimProdukt AS p
					INNER JOIN dbo.Verkaufsfakten AS f ON p.ProduktID = f.ProduktID
					GROUP BY p.Unterkategorie
					ORDER BY ErtragInProzent DESC
				)
		 CROSS JOIN (SELECT @xsum := 0) AS v1
		 ORDER BY prozent DESC
				, id
    ) AS v2
 WHERE prosum < 0.2
;

So müsstes es theoretisch gehen, wenn dein Query geklappt hat.

Falls nicht erstell wie gesagt einen Fiddle, dann frag ich nochmal

edit// wahrscheinlioch kommt man um den letzten Subquery sogar herum, aber so zum testen sollte das erstmal so genügen.

 

[Reisone]

Ich schreibe dir mal die Fehlermeldungen hin:

Meldung 137, Ebene 15, Status 2, Zeile 5
Die '@xsum'-Skalarvariable muss deklariert werden.
Meldung 156, Ebene 15, Status 1, Zeile 9
Falsche Syntax in der Nähe des 'AS'-Schlüsselworts.
Meldung 102, Ebene 15, Status 1, Zeile 15
Falsche Syntax in der Nähe von ':'

 

[DeletedUser101689]

Was für ne MySQL Version läuft denn bei dir?

Kannst du den von mir gepostesten fiddle bei dir local ohne fehlermeldung ausführen?

 

[Reisone]

kein MYSQL.

Ich schrieb SQL

 

[DeletedUser]

Ich verstehe es so:
Mal als Beispiel: deine Tabelle heißt "tabelle" und hat eine integer Spalte "prozent"

SELECT * FROM tabelle t1 WHERE 20 >= all(
  SELECT SUM(t2.prozent) FROM tabelle t2 WHERE t2.prozent >= t1.prozent
)
ORDER BY t1.prozent DESC