Gewinnermittlung mit group by


Ahiru

Grünschnabel
Ich habe folgende Tabelle:

OrderID
ProduktID
RegionsID
Restmenge
Preis
Tagesverkauf


Was ich gerne hätte wäre:
Eine Tabelle gruppiert nach ProduktID und RegionsID mit dem jeweiligen (zu erwartenden) Gewinn für dieses Produkt und diese Region. Dabei soll folgendes zugrunde gelegt werden:

Der Preis ist der Minimalpreis für das Produkt in dieser Region, wobei nur Orders mit einer Restmenge > 5 gezählt werden sollen

SQL:
select 
ProduktID, 
RegionsID 
min(Preis) as MINP 
from Verkaufsdatenbank 
where Restmenge>5 
group by ProduktID, RegionsID 
order by min(Preis) desc

Die Verkaufsmenge soll dem unteren Quartil entsprechen, also praktisch der Wert TOP 25% der Tagesverkäufe sein:

SQL:
set @b=1;
select @b:=ceil(count(orderID)/4) 
from Verkaufsdatenbank 
where `ProduktID` = 1236 and RegionsID=30000142;
PREPARE STMT FROM 'select `Tagesverkauf`as erwarteter_Tagesverkauf 
from Verkaufsdatenbank where `ProduktID` = 1236
and RegionsID=30000142 
order by Tagesverkauf desc 
LIMIT ?,1';
EXECUTE STMT USING @b;

Wie man sieht, muss ich allerdings bei meiner Lösung noch per Hand die ProduktID und RegionsID eintragen :-(

Wie mache ich es, dass er mir jetzt eine Tabelle zusammenbaut, die dann so aussieht:

ProduktID
RegionsID
erwarteter_Tagesverkauf
Gewinn


Gruppiert nach ProduktID und RegionsID sortiert nach Gewinn desc

Ich verzweifele schon - sitze jetzt gut 2 Tage daran.
 

Yaslaw

n/a
Moderator
Um was für eine Datenbank handelt es sich? Oracle? MS SQL? MySQL? MS Access? Pervasive? Postgre SQL? SQLite? oder sonstwas?
Je nachdem kann die Lösung ganz anderst aussehen.

Zuerst dachte ich es sei MySQL, doch MySQL kennt kein CEIL()
 

Ahiru

Grünschnabel
Hi,

doch, ceil() funktioniert bei mir - hab`s gerade nochmal ausprobiert - und ja, es ist MySql.

Es ging mir darum, dass sich für die Tagesverkäufe das Bild ergibt, dass es sehr viele niedrige Tagesverkäufe gibt (relativ inaktive Händler) und einige hohe. Leider darunter auch oft absolute Ausreißer. Daher wäre für eine Gewinnprognose max() deutlich zu hoch, avg() jedoch viel zu niedrig.
Daher hätte ich gerne sowas wie den Median der oberen Hälfte der Daten gehabt. Scheinbar ist es aber sehr schwierig mit MySQL den Median zu bilden (habe durch viel googeln lediglich Lösungen gefunden die es über Perl, Phyton oder ähnliche Sprachen lösen.)

Nunja - ich habe jetzt erstmal für den erwarteten Tagesverkauf avg(Tagesverkauf)+std(Tagesverkauf) gewählt - das bringt schon ganz brauchbare Ergebnisse.

Ist schade, dass MySQL weder den Median unterstützt, noch Limit count(Tagesverkauf)/4,1 zulässt :-(
 

Yaslaw

n/a
Moderator
Zwar nicht mit Median, jedoch mit deiner 1/4 Fprmel
SQL:
SELECT
    ProduktID,
    RegionsID,
    erwarteter_Tagesverkauf
FROM    
    (
        SELECT
            @sortId := IF(@pid = v.ProduktID AND @nid = v.RegionsID, @sortId + 1, 1) AS sortId,
            @pid := v.ProduktID AS ProduktID,
            @nid := v.RegionsID AS RegionsID,
            v.Tagesverkauf AS erwarteter_Tagesverkauf,
            q.qlimit
        FROM 
            (SELECT @sortId := 0, @pid := 0, @rid := 0) AS vars,
            (
                SELECT *
                FROM            
                    Verkaufsdatenbank
                ORDER BY 
                    ProduktID, 
                    RegionsID,
                    Tagesverkauf DESC
            ) AS v,
            (
                SELECT
                    ProduktID,
                    RegionsID,
                    CEILING(COUNT(orderID)/4) AS qlimit 
                FROM 
                    Verkaufsdatenbank
                GROUP BY
                    ProduktID,
                    RegionsID        
            ) AS q
        WHERE 
            v.ProduktID = q.ProduktID
            AND v.RegionsID=q.RegionsID
    ) AS myData
WHERE sortId <= qlimit
 
Zuletzt bearbeitet von einem Moderator:

Ahiru

Grünschnabel
Wow - vielen Dank - und danke auch für diese Schreibweise - das macht ja sogar SQL-Befehle leserlich!

Habe mich von innen nach außen durchgearbeitet, und auch alles verstanden. Funktioniert auch soweit, bis auf das äußere Select. Das mittlere Select gibt das Ergebnis wie erwartet aus, aber wenn ich das dann in das äußere Select einbette bleibt die Seite in phpMyAdmin weiß. Ich bekomme noch nicht einmal eine Fehlermeldung.
 

Yaslaw

n/a
Moderator
Hab ein Schreibfehler drin. Ersetze alle @nid durch @rid. Denn ich initialisieren @rid und nicht @nid.
 

Ahiru

Grünschnabel
Ok - dann ist diese Zeile: SELECT @sortId := 0, @pid := 0, @rid := 0) AS vars nur zum initialisieren der Variablen, und um diese auf 0 zu setzen? - Hatte mich schon gefragt, was die macht, denn vars taucht nirgendwo sonst noch auf.

Aber leider hat war das nicht der Fehler :-( - hatte das rid und nid in meinem code eh`schon durch sid ( in der echten Tabelle heißt die Region system_id ) ersetzt.

Kann es sein, dass es ein Limit für die Ausführzeit gibt, oder auch dafür wie groß die innere Tabelle sein darf? - Er braucht für den Select in der Mitte schon 3.9 Sekunden.
 

Ahiru

Grünschnabel
Ok - scheint echt eine Grenze zu geben, wie lange er für die Abfrage benötigen darf. Währen der mittlere Select in der Tabelle mit den Testdaten (12.000 Einträge) noch funktioniert, aber 3.9 Sekunden braucht, bekomme ich auch für diesen Select in der richtigen Datenbank (250.000 Einträge) nur eine weiße Seite.
 

Yaslaw

n/a
Moderator
Mache mal einen Explain daruaf und poste das Resultat. Mit guten Indexen kann man ev. noch viel machen
 

Ahiru

Grünschnabel
select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 39569 Using where
2 DERIVED <derived3> system NULL NULL NULL NULL 1
2 DERIVED <derived5> ALL NULL NULL NULL NULL 15003
2 DERIVED <derived4> ALL NULL NULL NULL NULL 39569 Using where; Using join buffer
5 DERIVED CentralCache ALL NULL NULL NULL NULL 39569 Using temporary; Using filesort
4 DERIVED CentralCache ALL NULL NULL NULL NULL 39569 Using filesort
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used

Hoffe das geht so? - wusste jetzt nicht, wie ich tabellen hier darstelle?
 
Zuletzt bearbeitet:

Neue Beiträge

Forum-Statistiken

Themen
272.360
Beiträge
1.558.632
Mitglieder
187.834
Neuestes Mitglied
jordanx0206