Delphi-PRAXiS

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 11: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 11: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 12: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 12: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 12: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 12: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 16: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 17: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 19: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 22: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.

mkinzler 4. Mär 2021 07:08
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Hast du überhaupt einen Index auf ArtikelNr?
Wird ihm so wenig bringen. Aber ein (weiterer) Index als expression Index könnte hilfreich sein.

SQL-Code:
CREATE INDEX IDX_ARTIKELGRUPPE ON VorgangPos
  COMPUTED BY ( substring(ArtikelNr from 1 for 5) );

Jumpy 4. Mär 2021 08:30
AW: [SQL] Wie Gruppensumme bilden?
 
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  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
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B

Delphi.Narium 4. Mär 2021 09:32
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von BlueStarHH (Beitrag 1484360)
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!
Ob ein Index benutzt wird oder nicht, erfährt man im Ausführungsplan.

Für FireBird nutze ich eigentlich immer FlameRobin.
Dort erhält man den Ausführungsplan über das Menü "Statement" und dort das Untermenü "Show execution plan".

Und: Über welche Datenmengen reden wir hier? Ein paar, ein paar hundert, ein paar tausend, mehrere Millionen?

BlueStarHH 4. Mär 2021 11:38
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von Jumpy (Beitrag 1484384)
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  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
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B
Das läuft in 2,5 Minuten durch. Danke! In VorgangPos sind im Moment ca. 400.000 Datensätze.

Jumpy 5. Mär 2021 07:15
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von BlueStarHH (Beitrag 1484402)
Zitat:
Zitat von Jumpy (Beitrag 1484384)
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  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
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B
Das läuft in 2,5 Minuten durch. Danke! In VorgangPos sind im Moment ca. 400.000 Datensätze.
Dann versuchen wir noch eine Optimierung:

SQL-Code:
with Basis as (
  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
)

Select B.*, G.Menge
From Basis B
Left Join
  (Select Gruppe, sum(Menge) From Basis Group By Gruppe) G
On G.Gruppe=B.Gruppe

Ghostwalker 5. Mär 2021 12:19
AW: [SQL] Wie Gruppensumme bilden?
 
Hi,
ohne jetzt Firebird im Detail zu kennen, würd ich das so schreiben

Code:
  select
    VP2.Gruppe As Gruppe,
    ArtikelNr,
    max(Beschreibung) as Beschreibung,
    max(Farbe) as Farbe,
    sum(Menge) as Menge,
    VP2.Gruppenmenge as Gruppenmenge
    sum(NettoSumme) as NettoSumme
  from VorgangPos vp
       (Select
          Substring(ArtikelNr from 1 for 5) as Gruppe,
          Sum(Menge) as Gruppenmenge
       From VorgangPos) AS VP2
  group by ArtikelNr
  order by ArtikelNr

Redeemer 5. Mär 2021 14:32
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von mkinzler (Beitrag 1484376)
Zitat:
Hast du überhaupt einen Index auf ArtikelNr?
Wird ihm so wenig bringen.
Hängt vom Datenbanksystem ab. MSSQL ist da teilweise überraschend intelligent und zieht beispielsweise einen solchen Index zur Lösung heran, wenn man ein LIKE-Match macht, das nicht mit % beginnt.

Zitat:
Zitat von mkinzler (Beitrag 1484376)
Aber ein (weiterer) Index als expression Index könnte hilfreich sein.
SQL-Code:
CREATE INDEX IDX_ARTIKELGRUPPE ON VorgangPos
  COMPUTED BY ( substring(ArtikelNr from 1 for 5) );
Das war schon vorgeschlagen worden.

IBExpert 6. Mär 2021 08:35
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von mkinzler (Beitrag 1484343)
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
Der Vorschlag von mkinzler ist der einzige, der dich weiter bringen wird, weil nur der genau die Anzahl der Records im Resultset benutzt, um damit den inneren sum befehl für jedes ergebnis genau ein mal ausführt.

Wenn dein äußeres Statement schnell ist, bekommst du auf den Weg die garantie, das dein inneres Statement genau n mal aufgerufen wird. mit dem debugger in ibexpert kannst du dann sogar noch die genauen laufzeiten der einzelaufrufe ermitteln und optimieren, weil evtl genau ein artikel 99% der zeit verbraucht, das wird dir aber mit deinem wild verschachtelten sql niemals klar sein.

Es geht dann weiter mit einem Expression index auf substring(ArtikelNr from 1 for 5) und einem doppeltpunkt vor der Gruppe ( substring(ArtikelNr from 1 for 5) = :Gruppe) damit die variable auch die ist die von oben kommt (das hätte mkinzler aber auch da gesetzt, wenn es darum geht, nicht mal eben nur eine hilfreiches Beispiel hier reinzutippen, sondern das in der relaen Datenbank auszuführen).

wenn du keine sp willst, dann mach das als execute block

Firebird kann zwar with und andere konstruktionen wie subselects etc, aber vermutlich wird dir dabei in ibexpert services-database monitoring das statements mit milliarden indexed oder non indexed reads um die ohren fliegen, sonst wäre das nicht so lahm

und bitte nicht immer "andere server können das alles ganz toll mit ..."
das bringt niemanden wirklich weiter, der das mit firebird lösen soll.

ziel sollte es bei jeder Programmierung sein, mit einem jederzeit nachvollziehbaren Statement ein möglichst performantes statements zu erreichen. Und der weg, die ebenen in einem block oder einer sp mit leicht verständlichen unteraufrufen zu implementieren sorgt für gutes Verständnis des Codes, auch wenn du dir den Kram selber in 5 Jahren noch mal anschauen musst.

Ich kann dir gerne das SQL von einem Kunden zu analyse übergeben, besteht aus ca 400 Zeilen und ergibt folgende Performance Analysis
(ist auch ziemlich verschachtelt, läuft aber trotzdem in ca 0,1 sekunden durch, aber leider nur ohne fetchall, dann brauch das immer noch
vertretbare 4 minuten für ein resultset von ca 1mio records und auf dem weg dahin ca 25mio indexed reads und 1 mio non indexed reads.
Selbst ich kann dafür nicht abschätzen ob ich eine stunde, ein Tag oder eine Woche brauche, um den Speed deutlich zu verbessern
weil der SQL Basiscode nicht von mir stammt, und weil der Kunde den sql nur ein mal am tag braucht, muss das auch niemand beschleunigen,
aber bei anderen Statements kann das ganz anders aussehen.



Code:
Plan
PLAN (S POL INDEX (FK_PAYMENT_ORDER_LINE_1))
PLAN JOIN (S CH INDEX (IX_CALC_HEADER_DOC_ID), S VE INDEX (PVE_CALC_HEADER))
PLAN (A2 INDEX (ACTIONS_ACTION_ID))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (RDB$PRIMARY12))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (RDB$PRIMARY12))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (S A NATURAL, S AD INDEX (PK_ADRESS)), S CO INDEX (RDB$PRIMARY5)), S VR INDEX (RDB$PRIMARY35)), S F INDEX (PK_FILES)), S FD INDEX (PK_FILE_DETAILS)), S VR2 INDEX (RDB$PRIMARY35)), S U INDEX (RDB$PRIMARY17)), S PP INDEX (RDB$PRIMARY56)), S Q INDEX (POOL_QUOTATION_QUOTATION_NO)), S Q2 INDEX (RDB$PRIMARY59)), S C INDEX (RDB$PRIMARY4)), S AST INDEX (APPLICATION_SETUP_LOCATION)), S CN INDEX (RDB$PRIMARY23)), S POH INDEX (PAYMENT_ORDER_NO)), S A2 INDEX (ACTIONS_ACTION_ID)), S PP2 INDEX (RDB$PRIMARY56)), S Q3 INDEX (POOL_QUOTATION_QUOTATION_NO)), S F2 INDEX (PK_FILES)), S FD2 INDEX (PK_FILE_DETAILS)), S VR3 INDEX (RDB$PRIMARY35)), S COM INDEX (RDB$PRIMARY4)), S APS INDEX (APPLICATION_SETUP_LOCATION))

Adapted Plan
PLAN (S POL INDEX (FK_PAYMENT_ORDER_LINE_1))
PLAN JOIN (S CH INDEX (IX_CALC_HEADER_DOC_ID), S VE INDEX (PVE_CALC_HEADER))
PLAN (A2 INDEX (ACTIONS_ACTION_ID))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (INTEG_44))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (INTEG_44))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (S A NATURAL, S AD INDEX (PK_ADRESS)), S CO INDEX (PK_CONTACTS)), S VR INDEX (INTEG_184)), S F INDEX (PK_FILES)), S FD INDEX (PK_FILE_DETAILS)), S VR2 INDEX (INTEG_184)), S U INDEX (PK_USERS)), S PP INDEX (PK_CONTACTS6)), S Q INDEX (POOL_QUOTATION_QUOTATION_NO)), S Q2 INDEX (PK_CONTACTS9)), S C INDEX (PK_COMPANY)), S AST INDEX (APPLICATION_SETUP_LOCATION)), S CN INDEX (INTEG_164)), S POH INDEX (PAYMENT_ORDER_NO)), S A2 INDEX (ACTIONS_ACTION_ID)), S PP2 INDEX (PK_CONTACTS6)), S Q3 INDEX (POOL_QUOTATION_QUOTATION_NO)), S F2 INDEX (PK_FILES)), S FD2 INDEX (PK_FILE_DETAILS)), S VR3 INDEX (INTEG_184)), S COM INDEX (PK_COMPANY)), S APS INDEX (APPLICATION_SETUP_LOCATION))

------ Performance info ------  ohne fetchall
Prepare time = 31ms
Execute time = 63ms
Avg fetch time = 2,17 ms
Current memory = 89.204.432
Max memory = 89.262.048
Memory buffers = 20.000
Reads from disk to cache = 155
Writes from cache to disk = 0
Fetches from cache = 5.400

------ Performance info ------ mit fetchall
Prepare time = 16ms
Execute time = 4m 19s 750ms
Avg fetch time = 0,28 ms
Current memory = 89.283.648
Max memory = 90.161.856
Memory buffers = 20.000
Reads from disk to cache = 221.092
Writes from cache to disk = 1
Fetches from cache = 87.363.844

BlueStarHH 10. Mär 2021 07:14
AW: [SQL] Wie Gruppensumme bilden?
 
@Ibexpert: Danke für diese ausführliche Antwort. Dann probiere ich nochmal den Code von mkinzler:

SQL-Code:
execute block
as

begin
  for select
    substring(vp.ArtikelNr from 1 for 5) as Gruppe,
    vp.ArtikelNr as 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, :Beschreibung, :Farbe, :Menge, :NettoSumme do
  begin
    for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge do suspend;
  end
end
Da erscheint diese Fehlermeldung:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Ich bin verwirrt...

mkinzler 10. Mär 2021 07:21
AW: [SQL] Wie Gruppensumme bilden?
 
Es fehlt die Group by


SQL-Code:
execute block
as

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

BlueStarHH 10. Mär 2021 07:35
AW: [SQL] Wie Gruppensumme bilden?
 
Zitat:
Zitat von mkinzler (Beitrag 1484765)
Es fehlt die Group by


SQL-Code:
execute block
as

begin
  for select
    substring(vp.ArtikelNr from 1 for 5) as Gruppe,
    vp.ArtikelNr as ArtikelNr,
    max(vp.Beschreibung) as Beschreibung,
    max(vp.Farbe) as Farbe,
    sum(vp.Menge) as Menge,
    sum(vp.NettoSumme) as NettoSumme
  from
    VorgangPos vp
  group by
    vp.ArtikelNr
    into :gruppe, :ArtikelNr, :Beschreibung, :Farbe, :Menge, :NettoSumme do
  begin
    for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge do suspend;
  end
end
Super, das läuft im IBExpert in etwas mehr als einer Sekunde durch! Das Ergebnis ist ja nun in einzelnen Variablen im Execute block. Nur wie kann ich das Ergebnis nun als Datenmenge in Delphi nutzen? Sorry für die dumme Frage, aber ich hab noch nie mit dem execute block gearbeitet. Wenn ich das ganze in eine SP packe, habe ich ja das selbe Problem oder nicht? Die SP kann ja keine Datenmenge aus mehreren Records zurückgeben oder doch?

mkinzler 10. Mär 2021 08:07
AW: [SQL] Wie Gruppensumme bilden?
 
Im Header der SP / execution block die Spalten für die Rückgabe deklarieren, diese werden dann durch das suspend zurückgegeben.

SQL-Code:
execute block
  returns(
   gruppe char(5), ...
  )
as
...

IBExpert 10. Mär 2021 10:06
AW: [SQL] Wie Gruppensumme bilden?
 
und ergänzend zu mkinzler, wenn du da alle variablen als return parameter drin hast, kannst du bei naherzu jeder Query Komponente, die du auch für einen select benutzen kannst, die property sql mit dem execute block .... text füllen und mit open dann durch die datenmenge laufen.

und wenn es mal eine summe, aber auch mal keine summe geben könnte , dann geht noch folgende änderung

Code:
   
select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge;
suspend;
damit würdest du auch einen record zur äußeren Hauptdatenmenge bekommen, wenn es gar keine details in VorgangPos gibt
(ist in diesem fall wegen dem Sum() nicht so wichtig, weil der immer genau einen record liefert, wen das die einzige spalte ist, aber wichtig ist das der "for select ... into" auf einer datenmenge die immer nur einen record liefert auch wenig sinnvoll.

und als logik: immer wenn dein quelltext in einem execute block oder auch in eine sp beim schlüsselwort suspend landet, wird das was in den return parametern steht als record erzeugt und kann damit in delphi bei der query mit while not eof und next ausgewertet werden

eine execute block muss auch gar noch zwingend auf einer datenmenge entstehen auch so was geht nämlich

Code:
execute block
returns (anz integer)
as
begin
 anz=1;
 suspend;
 anz=2;
 suspend;
end
auf dem weg kann man sehr komplexe ergebnisse zeilenweise und spaltenweise zusammenstellen, bei denen man mit einem sql statement abstruse verschachtelungen oder unions brauchen würde


Alle Zeitangaben in WEZ +1. Es ist jetzt 02:00 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