AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken [SQL] Wie Gruppensumme bilden?

[SQL] Wie Gruppensumme bilden?

Ein Thema von BlueStarHH · begonnen am 3. Mär 2021 · letzter Beitrag vom 10. Mär 2021
Antwort Antwort
Seite 1 von 3  1 23   
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hannover-Hainholz
797 Beiträge
 
Delphi 11 Alexandria
 
#1

[SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 12:29
Datenbank: Firebird • Version: 3.x • Zugriff über: IBDAC
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!
  Mit Zitat antworten Zitat
Redeemer

Registriert seit: 19. Jan 2009
Ort: Kirchlinteln (LK Verden)
1.010 Beiträge
 
Delphi 2009 Professional
 
#2

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 12:48
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
Janni
2005 PE, 2009 PA, XE2 PA
  Mit Zitat antworten Zitat
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hannover-Hainholz
797 Beiträge
 
Delphi 11 Alexandria
 
#3

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 13:10
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.

Geändert von BlueStarHH ( 3. Mär 2021 um 13:56 Uhr)
  Mit Zitat antworten Zitat
Klaus01

Registriert seit: 30. Nov 2005
Ort: München
5.752 Beiträge
 
Delphi 10.4 Sydney
 
#4

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 13:30
bringt es was, wenn Du substring()durch left(vp.ArtikelNr, 5) ersetzt?

Grüße
Klaus
Klaus
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
2.400 Beiträge
 
Delphi 7 Professional
 
#5

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 13:33
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))

Geändert von Delphi.Narium ( 3. Mär 2021 um 13:44 Uhr) Grund: Schreibfehler
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.851 Beiträge
 
Delphi 11 Alexandria
 
#6

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 13:46
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
Markus Kinzler
  Mit Zitat antworten Zitat
mjustin

Registriert seit: 14. Apr 2008
3.003 Beiträge
 
Delphi 2009 Professional
 
#7

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 17:15
"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.
Michael Justin
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#8

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 18:29
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.
Gruß, Jo
  Mit Zitat antworten Zitat
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hannover-Hainholz
797 Beiträge
 
Delphi 11 Alexandria
 
#9

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 20:28
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!
  Mit Zitat antworten Zitat
Redeemer

Registriert seit: 19. Jan 2009
Ort: Kirchlinteln (LK Verden)
1.010 Beiträge
 
Delphi 2009 Professional
 
#10

AW: [SQL] Wie Gruppensumme bilden?

  Alt 3. Mär 2021, 23:37
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.
Janni
2005 PE, 2009 PA, XE2 PA
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 3  1 23   

Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 08:03 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz