GROUP BY

[Andreas@Subke]

Ich hab bereits ähnliche Themen im Forum versucht nachzuvollziehen, aber ich stehe hier einfach auf dem Schlauch.

Ich habe eine Tabelle von Lagerplätzen, Artikel, Buchungsdatum und Bestand.

Lagerplatz	Artikel	Buchungsdatum (vereinfacht)	Bestand
33364	1	14	56
33364	2	15	57
33364	4	16	58
33364	1	17	59
33364	5	18	0
33364	3	19	61
33364	1	20	62
33364	2	21	63
33364	3	22	64
33364	2	23	65
...	...	...	...

Ich möchte den Warenbestand auf diesem Lagerplatz ermitteln. Also als Ergebnis ungefähr das folgende haben:

Lagerplatz	Artikel	Datum (eigentlich unwichtig, ich will nur den letzten Stand haben)	Bestand
33364	1	20	62
33364	2	23	65
33364	3	22	64
33364	4	16	58

Meine Idee: die jeweils letzte Buchung zu jedem Artikel auf dem Lagerplatz herausfiltern & ausgeben lassen. Die WHERE-Bedingung um Bestand=0 herauszufiltern bekomme ich bestimmt noch selber hin, der Knackpunkt ist das GROUP BY... oder einen anderen Weg...

Hier mein Query:

SELECT
    [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz] AS Lagerplatz,
    [dbo].[tWarenLagerPlatzArtikel].[kArtikel] AS Artikel,
    temp.dGebucht AS Datum,
    temp.fLagerBestand AS Bestand
    
FROM
    [dbo].[tWarenLagerPlatzArtikel]

LEFT JOIN
        ( SELECT kArtikel, dGebucht, fLagerBestand FROM [dbo].[tArtikelHistory] ) temp
    ON
        temp.[kArtikel] = [dbo].[tWarenLagerPlatzArtikel].[kArtikel]

WHERE
    [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz] ='33364'

GROUP BY [dbo].[tWarenLagerPlatzArtikel].[kArtikel]

ORDER BY [dbo].[tWarenLagerPlatzArtikel].[kArtikel], temp.dGebucht DESC

Das GROUP BY wirft mir hier natürlich einen Fehler, dass alle SELECT-Wert in Funktionen verpackt werden sollen. Ich muss gestehen , dass ich häufig mit MySQL arbeite und da das GROUP BY etwas lockerer gehandhabt wird

 

[dabadepdu]

Das GROUP BY wirft mir hier natürlich einen Fehler, dass alle SELECT-Wert in Funktionen verpackt werden sollen. Ich muss gestehen , dass ich häufig mit MySQL arbeite und da das GROUP BY etwas lockerer gehandhabt wird

Ja, das wird bei mySQL so locker gehandhabt, dass es eher falsche Daten ausgibt, als einen Fehler.

Die Faustregel für Group By:
Alle selektierten Ausdrücke müssen entweder gruppiert werden (ins group by) oder aggregiert werden.
Damit fährst Du so lange gut, bis Du was spezielles brauchst und damit meine ich wirklich speziell, keine Anfänger/Allerweltsgruppierung.

Deine Select Clause

SELECT
       [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz] AS Lagerplatz,
       [dbo].[tWarenLagerPlatzArtikel].[kArtikel] AS Artikel,
       max(temp.dGebucht) AS Datum,
       sum(temp.fLagerBestand) AS Bestand

enthält gar keine Aggregationen (Dann brauchst Du auch kein Group By). Ich würde annehmen, dass mindestens der Bestand aufsummiert (Aggregation sum()) werden sollte, was in Deinem Musterergebnis rechnerisch auch nicht der Fall ist. Das letzte Datum ist ebenfalls über einen Aggregationsausdruck (max()) zu erreichen.
Dein Group By ist mySQL Style, also in normalen Datenbanken ist es eine fehlerhafte (unvollständige) Schreibweise.

Ergibt zusammen:

SELECT
       [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz] AS Lagerplatz,
       [dbo].[tWarenLagerPlatzArtikel].[kArtikel] AS Artikel,
       max(temp.dGebucht) AS Datum,
       sum(temp.fLagerBestand) AS Bestand
  FROM
...
 GROUP BY
       [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz],
       [dbo].[tWarenLagerPlatzArtikel].[kArtikel]

Das temp Subselect in Deinem Beispiel ist überflüssig.

 

[Andreas@Subke]

Moin dabadepdu,

vielen Dank für Deine Rückmeldung und Deine Erklärungen. Die Summe der Bestände ist allerdings nicht mein Ziel, sondern ich benötige den Bestand, welcher zu dem jeweiligen Max-Datum gehört.

Ich muss zugeben, dass ich die Ausgangstabelle sehr vereinfach habe. Tatsächlich sind dort auch die Buchungsmengen vermerkt, wenn ich diese Spalte summiere erhalte ich das richtige Ergebnis. Das ist jedoch leider nicht sonderlich performant und mehr ein Workaround. Eigentlich muss ich ja nur die richtigen Zeilen lesen, anstatt zu rechnen.

 

[MDDaniel]

Hallo
Ohne es Performancemäßig ausprobiert zu haben wäre folgendes vielleicht eine Möglichkeit:

WITH lager
AS (
    SELECT kWarenLagerPlatz
        ,kArtikel AS Artikel
    FROM [dbo].[tWarenLagerPlatzArtikel]
         WHERE [dbo].[tWarenLagerPlatzArtikel].[kWarenLagerPlatz] = '33364'
    )
    ,[history] AS (
        SELECT kArtikel
            ,dGebucht
            ,fLagerBestand
            ,ROW_NUMBER() OVER (PARTITION BY kartikel ORDER BY dGebucht DESC) AS stand
        FROM [dbo].[tArtikelHistory] join Lager  on [kArtikel] = Artikel
    )
SELECT lager.[kWarenLagerPlatz] AS Lagerplatz
    ,Artikel
    ,dGebucht AS Datum
    ,fLagerBestand AS Bestand
FROM lager
    LEFT JOIN [history] ON lager.artikel = [history].kArtikel
    WHERE Stand = 1

Was nicht berücksichtigt ist, ist wenn ein Artikel mehrere Lagerplätze hat. Allerdings war das bei deiner Abfrage vorhin auch nicht der Fall, obwohl das möglich ist/sein sollte. Dann müsste das Kriterium entsprechend erweitert werden.

 

[dabadepdu]

Jetzt hab ich es glaub ich verstanden. Was die History Tabelle bedeutet ist mir aber nicht klar.
Mein Vorschlag:

select b_max.*, wlpa.bestand 
  from tWarenLagerPlatzArtikel wlpa join
      (select Lagerplatz, Artikel, max(Buchungsdatum) buchungsdatum_max 
         from tWarenLagerPlatzArtikel
        group by Lagerplatz, Artikel) b_max
    on wlpa.Lagerplatz = b_max.Lagerplatz and 
       wlpa.Artikel = b_max.Artikel and
       wlpa.Buchungsdatum = b_max.buchungsdatum_max
[code]

 

[MDDaniel]

Jetzt hab ich es glaub ich verstanden. Was die History Tabelle bedeutet ist mir aber nicht klar.#

In der History Tabelle scheinen die Bestände vermerkt zu sein, quasi die Inventurdaten pro Artikel. Nach der Abfrage von Andreas zu urteilen muss die Tabelle jeweils mit dem Lagerplatz verbunden werden.
Die Tabelle tWarenLagerPlatzArtikel enthält also keinen Bestand, was ein irgendwie strange ist, aber ok.
Deine vorgeschlagene Abfrage müsste entweder fehlschlagen oder jedenfalls nicht auf die Daten zugreifen die Andreas bei seinem Beispiel verwendet hat.

 

[ukulele]

So ich war eine Weile nicht da, dennoch scheint mir die Aufgabe einfach zu sein. @MDDaniel hat es eigentlich schon gezeigt, verstehe nur den Sinn hinter dem "lager"-Alias nicht. Ganz kurz formuliert:

WITH t AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY Lagerplatz,Artikel ORDER BY Buchungsdatum DESC) AS zeile,
    *
    FROM    tabelle
    )
SELECT    t.*
FROM    t
WHERE    t.zeile = 1

Oder etwas umgestellt ohne WITH:

SELECT    t.*
FROM    (
    SELECT    ROW_NUMBER() OVER (PARTITION BY Lagerplatz,Artikel ORDER BY Buchungsdatum DESC) AS zeile,
    *
    FROM    tabelle
    ) t
WHERE    t.zeile = 1

Es gäbe auch noch einen recht "klassischen" Weg ohne ROW_NUMBER():

SELECT    tabelle.*
FROM    tabelle
INNER JOIN (
SELECT    Lagerplatz,
        Artikel,
        max(Buchungsdatum) AS Buchungsdatum
FROM    tabelle
GROUP BY Lagerplatz,Artikel
    ) t
ON        tabelle.Lagerplatz = t.Lagerplatz
AND        tabelle.Artikel = t.Artikel
AND        tabelle.Buchungsdatum = t.Buchungsdatum

Dieser Weg hat einen wesentlichen Unterschied: Wenn zwei Datensätze identisch sind (Lagerplatz, Artikel und das selbe, höchste Buchungsdatum) dann werden auch beide angezeigt. Bei der Variante mit WITH ist kann nur eine Zeile ausgegeben werden. Das kann eine Rolle spielen, wenn nicht technisch sichergestellt ist, das zwei Angaben zum selben Zeitpunkt eingetragen sein können. Man kann sich dann überlegen, ob es einen Fehler macht, ob man will, das es einen Fehler macht oder wie man mit diesem Problem umgehen würde.

 

[Andreas@Subke]

Es gäbe auch noch einen recht "klassischen" Weg ohne ROW_NUMBER():
SELECT tabelle.* FROM tabelle INNER JOIN ( SELECT Lagerplatz, Artikel, max(Buchungsdatum) AS Buchungsdatum FROM tabelle GROUP BY Lagerplatz,Artikel ) t ON tabelle.Lagerplatz = t.Lagerplatz AND tabelle.Artikel = t.Artikel AND tabelle.Buchungsdatum = t.BuchungsdatumDieser Weg hat einen wesentlichen Unterschied: Wenn zwei Datensätze identisch sind (Lagerplatz, Artikel und das selbe, höchste Buchungsdatum) dann werden auch beide angezeigt. Bei der Variante mit WITH ist kann nur eine Zeile ausgegeben werden. Das kann eine Rolle spielen, wenn nicht technisch sichergestellt ist, das zwei Angaben zum selben Zeitpunkt eingetragen sein können. Man kann sich dann überlegen, ob es einen Fehler macht, ob man will, das es einen Fehler macht oder wie man mit diesem Problem umgehen würde.

Moin Ukulele,

vielen Dank! Ja genau das hat mir gefehlt.

Die Dubletten bei dem exakt gleichen Buchungsdatum ist zwar logisch tatsächlich ein Problem, aber da der Zeitpunkt millisekunden-genau eingetragen wird, gönn ich mir diesen Fehler einfach mal, zumal er mit der nächsten Buchung auf diesem Lagerplatz wieder korrigiert wird.

In der History Tabelle scheinen die Bestände vermerkt zu sein,

Ja, auch richtig. In der History-Tabelle wird vermerkt, WANN & WAS auf dem Lagerplatz gebucht wird. Und zu jedem Eintrag wird halt auch der aktuelle Bestand auf diesem Lagerplatz vermerkt. Es kann in der Praxis durchaus vorkommen, dass verschiedene Artikel auf einem Lagerplatz liegen.
Wo im Lager der Artikel sonst noch zu finden ist, spielt für meine Anwendung keine Rolle. Ich muss nur den aktuellen Stand zu dem Lagerplatz "rauspuhlen"

Ich danke Euch, und denke, dieser Post kann hier geschlossen werden.

 

[ukulele]

Wenn du den "klassischen" Weg gehen willst, zwei gleiche Zeitstempel real vorkommen können, dann kannst du die doppelte Zeile abfangen in dem du z.B. erneut aggregierst:

SELECT    tabelle.Lagerplatz,
tabelle.Artikel,
tabelle.Buchungsdatum,
avg(tabelle.Bestand) AS Bestand
FROM    tabelle
INNER JOIN (
SELECT    Lagerplatz,
        Artikel,
        max(Buchungsdatum) AS Buchungsdatum
FROM    tabelle
GROUP BY Lagerplatz,Artikel
    ) t
ON        tabelle.Lagerplatz = t.Lagerplatz
AND        tabelle.Artikel = t.Artikel
AND        tabelle.Buchungsdatum = t.Buchungsdatum
GROUP BY
tabelle.Lagerplatz,
tabelle.Artikel,
tabelle.Buchungsdatum

Allerdings ist der Weg dann eigentlich weniger attraktiv als die Variante mit ROW_NUMBER(). Nur auf sehr alten SQL Versionen macht das eventuell Sinn, schneller und einfacher ist die Arbeit mit ROW_NUMBER().

Hier wird nie ein Thread erledigt