Delphi-PRAXiS
Seite 1 von 3  1 23   

Delphi-PRAXiS (https://www.delphipraxis.net/forum.php)
-   Datenbanken (https://www.delphipraxis.net/15-datenbanken/)
-   -   [SQL] Wie Gruppensumme bilden? (https://www.delphipraxis.net/207200-%5Bsql%5D-wie-gruppensumme-bilden.html)

BlueStarHH 3. Mär 2021 12:29

Datenbank: Firebird • Version: 3.x • Zugriff über: IBDAC

[SQL] Wie Gruppensumme bilden?
 
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 3. Mär 2021 12:48

AW: [SQL] Wie Gruppensumme bilden?
 
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 3. Mär 2021 13:10

AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:

Zitat von Redeemer (Beitrag 1484330)
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 3. Mär 2021 13:30

AW: [SQL] Wie Gruppensumme bilden?
 
bringt es was, wenn Du substring()durch left(vp.ArtikelNr, 5) ersetzt?

Grüße
Klaus

Delphi.Narium 3. Mär 2021 13:33

AW: [SQL] Wie Gruppensumme bilden?
 
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

SQL-Code:
create index ix_VorgangPos_Gruppe on VorgangPos computed by (substring(ArtikelNr from 1 for 5))

mkinzler 3. Mär 2021 13:46

AW: [SQL] Wie Gruppensumme bilden?
 
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 3. Mär 2021 17:15

AW: [SQL] Wie Gruppensumme bilden?
 
"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 3. Mär 2021 18:29

AW: [SQL] Wie Gruppensumme bilden?
 
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 3. Mär 2021 20:28

AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:

Zitat von Delphi.Narium (Beitrag 1484339)
Bevor wir nach anderen Lösungen für den Aufbau der Abfrage suchen, probiere es bitte mal mit

SQL-Code:
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 3. Mär 2021 23:37

AW: [SQL] Wie Gruppensumme bilden?
 
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.


Alle Zeitangaben in WEZ +1. Es ist jetzt 00:29 Uhr.
Seite 1 von 3  1 23   

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2021 by Daniel R. Wolf