[SQL] Wie Gruppensumme bilden?

[BlueStarHH]

Ich habe eine Tabelle, in der Vorgangspositionen (die einzelnen Artikel einer Rechnung) enthalten sind. Diese möchte ich zusammengefasst (group by) nach ArtikelNr ausgeben. Zu jeder ArtikelNr sollen weitere Felder ausgeben werden, so wie hier:

Code:

select 
  substring(ArtikelNr from 1 for 5) as "Gruppe",
  ArtikelNr,
  max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
  sum(Menge) as Menge, sum(NettoSumme) as NettoSumme

from VorgangPos vp
group by ArtikelNr
order by ArtikelNr

Das klappt gut. Sieht z.B. so aus:

Code:

Gruppe   ArtikelNr       Beschreibung   Farbe   Menge    NettoSumme
12345   12345-7        Mauspad        blau    2       12,-
12345   12345-14       Maus           grau    3       50,- 
45678   45678-2        Tastatur       grau    1       70,-

Jetzt möchte ich zusätzlich noch zu jeder ArtikelNr, die die selbe Gruppe hat, die Gesamtsumme aller Mengen in dieser Gruppe ausgeben. Z.B. so:

Code:

Gruppe   ArtikelNr       Beschreibung   Farbe   Menge    NettoSumme    Gruppenmenge
12345   12345-7        Mauspad        blau    2       12,-         5   (da 2+3 =5)
12345   12345-14       Maus           grau    3       50,-         5   (da 2+3 =5)
45678   45678-2        Tastatur       grau    1       70,-         1

Mein Versuch sieht so aus:

Code:

select 
  substring(ArtikelNr from 1 for 5) as "Gruppe",
  ArtikelNr,
  max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
  sum(Menge) as Menge, sum(NettoSumme) as NettoSumme,

  min((
    select sum(Menge) from VorgangPos vpSub
    where substring(vpSub.ArtikelNr from 1 for 5) = substring(vp.ArtikelNr from 1 for 5)
  )) as Gruppenmenge

from VorgangPos vp
group by ArtikelNr
order by ArtikelNr

Über Delphi läuft das endlos (nach 10 Minuten aufgegeben). Mit dem IBExpert ist das auch nicht ausführbar, der friert dann ein, wenn er das Ergebnis anzeigen möchte.
Die Gruppe ist (leider) in der ArtikelNr gespeichert. Es kann nichts an der Datenstrutkur geändert werden. Der Bindestrich dort ist nur zur besseren Übersicht eingefügt.

Was mache ich falsch? Wie geht's richtig? Danke!

[Redeemer]

Man würde hier einen INNER oder LEFT OUTER JOIN von VorgangPos auf VorgangPos machen:

Code:

select
  substring(vp.ArtikelNr from 1 for 5) as "Gruppe",
  vp.ArtikelNr,
  max(vp.Beschreibung) as Beschreibung,
  max(vp.Farbe) as Farbe,
  sum(vp.Menge) as Menge,
  sum(vp.NettoSumme) as NettoSumme,
  sum(vp2.Menge) Gruppenmenge

from VorgangPos vp
inner join VorgangPos vp2 ON substring(vp2.ArtikelNr from 1 for 5) = substring(vp.ArtikelNr from 1 for 5)
group by vp.ArtikelNr
order by vp.ArtikelNr

[BlueStarHH]

Zitat von Redeemer:

Man würde hier einen INNER oder LEFT OUTER JOIN von VorgangPos auf VorgangPos machen:

Code:

select
  substring(vp.ArtikelNr from 1 for 5) as "Gruppe",
  vp.ArtikelNr,
  max(vp.Beschreibung) as Beschreibung,
  max(vp.Farbe) as Farbe,
  sum(vp.Menge) as Menge,
  sum(vp.NettoSumme) as NettoSumme,
  sum(vp2.Menge) Gruppenmenge

from VorgangPos vp
inner join VorgangPos vp2 ON substring(vp2.ArtikelNr from 1 for 5) = substring(vp.ArtikelNr from 1 for 5)
group by vp.ArtikelNr
order by vp.ArtikelNr

Danke, auch das ist nicht nutzbar. Läuft schon seit 20 Minuten und bricht dann ab mit "No free space found in temporary directories. Es steht nicht genug Speicherplatz auf dem Datenträger zur Verfügung". Dabei waren mehere GB frei. Ich habe das Gefühl, dass substring dafür verantwortlich ist. Wie kann das beschleunigt werden? Jeder Datensatz hat eine eindeutige ID (Integer). Kann man damit irgendwie die Gruppen schon vorher bilden und substring muss dann evtl. nicht so oft aufgerufen werden? Irgendwas mit temporären Tabellen? Wie? Alles nur vage Vermutungen, da habe ich nicht so die Erfahrung.

[Klaus01]

bringt es was, wenn Du substring()durch left(vp.ArtikelNr, 5) ersetzt?

Grüße
Klaus

[Delphi.Narium]

Dashier kann nicht schnell sein:

SQL-Code:

  min((
    select sum(Menge) from VorgangPos vpSub
    where substring(vpSub.ArtikelNr from 1 for 5) = substring(vp.ArtikelNr from 1 for 5)
  )) as Gruppenmenge

Je Datensatz der Ergebnismenge muss ein Subselect mit einer Einschränkung auf einen Teilstring gemacht werden, für den es (vermutlich / höchstwahrscheinlich) keinen Index gibt.

Bevor wir nach anderen Lösungen für den Aufbau der Abfrage suchen, probiere es bitte mal mit

create index ix_VorgangPos_Gruppe on VorgangPos computed by (substring(ArtikelNr from 1 for 5))

[mkinzler]

In Form einer SP/einem Codeblock:

SQL-Code:

...for select
  substring(vp.ArtikelNr from 1 for 5),
  vp.ArtikelNr,
  max(vp.Beschreibung) as Beschreibung,
  max(vp.Farbe) as Farbe,
  sum(vp.Menge) as Menge,
  sum(vp.NettoSumme) as NettoSumme
from 
  VorgangPos vp 
  into :gruppe, ArtikelNr, :... do
begin
  for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5)  = Gruppe into :Gruppenmenge do suspend;
end

[mjustin]

"Richtig" wäre nach meinem Bauchgefühl mit Windowing Funktionen zu arbeiten:

https://www.firebirdsql.org/file/com...3windowing.pdf

Dabei setzt man eine partition auf die gewünschten Gruppen und kann dann je Gruppe mit Aggregatfunktionen arbeiten.

Vielleicht kann hier im Forum das für Firebird (3) kurz in SQL giessen.


p.s. ich habe Windowing mit MS SQL / Oracle / DB2 bereits eingesetzt und eine Gruppenmenge sollte damit problemlos und vor allem sehr performant realisierbar sein. Vielleicht kann ich es in den nächsten Tagen an einer Beispiel-DB zeigen.

[jobo]

Wenn durch eine solche Abfrage ein paar GB gedumped werden und alles so ewig dauert, dann mal so ins Blaue:
Vielleicht ein Join Kriterium vergessen?
Wenn die Daten trotz Gruppierung mehr werden kann irgendwas nicht stimmen.

Die Variante von Reedemer (äußerer Join) würde ich jedenfalls vorziehen. Wenn fb Partition Windows kann, dann wohl das. Die Partition / Gruppierung & Join anhand eines Teilstrings zu machen, ist natürlich generell nicht die Grundlage für irrsinnige Geschwindigkeit.

[BlueStarHH]

Zitat von Delphi.Narium:

Bevor wir nach anderen Lösungen für den Aufbau der Abfrage suchen, probiere es bitte mal mit

create index ix_VorgangPos_Gruppe on VorgangPos computed by (substring(ArtikelNr from 1 for 5))

Das habe ich vor der Ausführung meines SQL-Statements aus dem Eingangspost ausgeführt. Mein läuft SQL-Statement im IBExpert läuft nun seit mehr als 30 minuten... Woher weiss ich, dass der Index benutzt wird? Ist die DB so schlau, das automatisch zu nutzen? Die anderen Vorschläge teste ich morgen. Danke!

[Redeemer]

Wenn es so lange läuft, wird der Index nicht genutzt. Der dient je gerade dazu, die Komplexität logarithmisch zu begrenzen.

Ein paar Ideen, je nachdem, was dein DBMS kann:

• Hast du überhaupt einen Index auf ArtikelNr?
• Führe im JOIN einen Linksvergleich mit LIKE aus (setzt Index auf ArtikelNr voraus):
  Code:

  LEFT OUTER JOIN VorgangPos vp2 ON vp2.ArtikelNr LIKE substring(vp.ArtikelNr from 1 for 5) || '%'

  (Ich hab keine Ahnung, was in deinem DBMS der Konkatenationsoperator ist. Da hat jedes seinen eigenen, Doppelpipe ist einfach der SQL-Standard, an den sich fast niemand hält.)
• LEFT statt SUBSTRING benutzen.
• Index auf eine berechnete Spalte mit dem Vergleichswert setzen.