View Full Version : SQL-Abfrage mit Sub-Sub-Select ;)
Moin @ll,
ich habe schon öfter vor dem Problem gestanden und jetzt möchte ich gern einer möglichen Lösung nachgehen. Vielleicht habt ihr eine Idee dazu oder die Abfrage funktioniert wirklich nicht in dieser Form.
Das Problem ist, dass ich in einem Select ein Sub-Select aufrufe, das selbst auch ein weiteres Sub-Select aufruft und ich dort eine Spalte aus dem Select verwenden möchte...
Ein Beispiel sagt mehr als Tausend Worte ;);)
select l.LLGNR,
(select prozent
from (select MUVUVISTS,
count(*) * 100.0 / sum(count(*)) over() Prozent
from MIETUVIP m
where m.MUVLGNR = l.LLGNR
group by MUVUVISTS
)
where muvuvists = ('1', '3')
) ProzentSts
from LIEGEP l
Das innere Select liefert den Prozentsatz für jeden STS. Benötigt wird nur ein Prozentsatz - dafür das mittlere Select (STS = '1' oder '3').
Das äußere SQL liefert die LLGNR, die an das innere Select für die Selektion gegeben werden muss, weil ja der count(*) nicht über die ganze Datei sondern nur für die LLGNR sein soll.
Vielen Dank im Voraus
Wolfgang
Sub-Selects in der SELECT-Zeile (egal ob verschachtelt oder nicht) müssen genau einen Wert zurückbringen. In deinem Beispiel könnte es mehrere Werte geben, da nur auf MUVUVISTS verdichtet wird, die Verknüfung jedoch über MUVLGNR und nicht über (LNGR + UVISTS)
M.E. liegt hier schon mal ein Denkfehler
Ansonsten brauchst Du m.E. keine Verschachtelung: Der folgende einfache Sub-Select sollte das gleiche Ergebnis liefern:
select MUVUVISTS,
count(*) * 100.0 / sum(count(*)) over(Partition By MUVLNGR) Prozent
from MIETUVIP m
where m.MUVLGNR = l.LLGNR
group by MUVUVISTS)
Ansonsten würde ich empfehlen die (verschachtelten) Sub-Selects durch Common Table Expressions zu ersetzten.Common Table Expressions (https://www.ibm.com/docs/en/i/7.5.0?topic=statement-common-table-expression)
Die machen den Source Code nicht nur einfacher zu lesen, sondern auch einfacher zu analysieren ... und (zumindest nach meiner Erfahrung) kann der Query Optimizer CTEs besser optimieren als (verschachtelte) Sub-Selects.
Die folgende Abfrage sollte das Ergebnis liefern, dass du willst und hierbei macht es auch nichts aus, dass es pro MUVLGNR mehrere Sätze gibt.
With x as (Select MUVLGNR, MUVUVISTS,
Count(*) * 100,0 / Sum(Count(*)
Over(Partition By MUVLGNR)) Prozent
from MIETUVIP m
Where MUVUVISTS in ('1', '3')
Group By MUVLGNR, MUVUVISTS)
Select LLGNR, MUVUVISTS, Prozent
from LIEGEP join x on LLGNR = MUVLLGNR;
Eventuell hätte man sogar ohne Common Table Expression auskommen können, indem man beide Dateien direkt verjoint und dann direkt auf LLGNR und MUVUISTS verdichtet.
Select LLGNR, MUVUVISTS,
Count(*) * 100,0 / Sum(Count(*)
Over(Partition By MUVLGNR)) Prozent
from LIEGEP join MIETUVIP m
on LLGNR = MUVLLGNR
Where MUVUVUSTS in ('1', '3')
Group By LLGNR, MUVUVISTS;
Was die Optimierung angeht, so ist es egal ob CTE's oder Subselect (Derived Table).
CTE's werden in Derived Table umgeschrieben und je Verwendung (also auch durchaus mehrfach) ausgeführt.
Nicht zu verachten ist auch ein
from tablea a
left join lateral
(select a, b, c, aggr(x), aggr(y)
from tableb b
where a.key = b.key ...
group by a b c
) b
Vorteile:
man kann mehrere Select-Subselects in einem Lateral-Join vereinen.
über die Where-Klausel kann man filtern, erlaubt sind auch Gruppierung, Having, Order by und Limit.
Erlaubt sind durchaus auch mehrere Zeilen (z.B. cross join lateral (...))
Der Optimizer optimiert für diesen Zugriff separat.
Nachteile:
Keine!
Was die Optimierung angeht, so ist es egal ob CTE's oder Subselect (Derived Table).
CTE's werden in Derived Table umgeschrieben und je Verwendung (also auch durchaus mehrfach) ausgeführt.
Bekannt ist, dass die SQL-Abfragen nach bestimmten Algorithmen umgeschrieben werden, aber wie das umgeschriebene SQL-Statement dann aussieht, weißt weder Du noch ich.
Allerdings zeigt die Erfahrung, dass bei komplexen Abfragen mit mehrfach verschachtelten Sub-Selects an allen möglichen Stellen (SELECT-Liste, FROM-Anweisung, WHERE-Bedingungen, ORDER BY) dass sowohl der Programmierer des öfteren den Überblick verliert, als auch der Optimizer irgendwo ins trudeln kommt, so dass ggf. gleiche (Sub-)Selects mehrfach ausgeführt werden.
CTEs hingegen werden am Anfang eines SELECT-Statements angegeben und können einzeln ausgeführt werden und sind deshalb (auch von dem Optimizer) wesentlich einfacher zu analysieren.
LATERAL braucht man eigentlich nur dann, wenn Spalten-Werte aus der ersten Tabelle/View als Parameter an User Defined Functions (UDTF) übergeben werden müssen.
Alles andere kann mit "gewöhnlichen" JOINS (incl. CROSS JOIN) gelöst werden. Das zuvor angeführte Beistpiel bringt auch ohne LATERAL die gleiche Lösung:
from tablea tbla
left join (select a, b, c, aggr(x) AggX, aggr(y)
from tableb b
group by a b c
) tblb
on a = tbla.Key1
and b = tbla.Key2
and c = tbla.Key3
Allerdings hat das alles wiederum nichtsmit der ursprünglichen Frage bzw. Problemlösung zu tun.
Aber natürlich hat das damit zu tun, da ich mir inzwischen angewöhnt habe, skalare subselects die mir genau 1 Wert zurückliefern in lateral joins umzuschreiben, weil eben dies besser optimiert werden kann und somit auch mehrere skalare subselects zu einem lateral join vereinfacht werden können.
Und sicherlich bringt dein left join dieselbe Lösung ist aber schlechter optimierbar da sie den Join auf dem Group-Ergebnis ausführen muss.
from tablea tbla
left join lateral (select aggr(x) as ax, Agg(y) as ay, aggr(z) as az
from tableb b
where b.key1 = tbla.Key1
and b.key2 = tbla.Key2
and b.key3 = tbla.Key3
) tblb on 1=1
Ergänzt:
Den Group By kann man sich sogar sparen, da direkt per Where gefiltert wird.
Statt left geht alternativ nun auch cross join, so dass man auch 1=1 spart.
Man kann die Aggregate im Outerselect verrechnen (ax * 100.0 / ay) oder bereits im subselect als aggr(x) * 100.0 / aggr(y).
Desweiteren erlaubt ein Lateral auch einen Order By mit Limit, da "fetch first n rows" nur am Ende erlaubt ist und Order by in Subselects (außer over()) ignoriert werden.
Somit kann man z.B. eine Preisfindung mittels
cross / left join lateral (select preis from preistable
where datum between gueltigvon and gueltigbis
order by gueltigbis -- je nach Modell auch order by gueltigvon desc
limit 1
) p [on 1=1]
durchführen, was ansonsten ungleich komplexer ist.
Und warum soll ich lateral nur bei UDTF's verwenden wenn sie doch um so viel einfacher und performanter verwendet werden können als CTE's?
Und was die CTE's angeht, kann man das im Job über die Anzahl Open einer Tabelle und die Zugriffe prüfen, da CTE's keine temporäre Tabelle aufbauen.
Temporäre Tabellen werden im generellen Optimierungsweg unabhängig von CTE's.
Und im Debugmode (bzw. SQL-Analysator) kann man auch schon mal entdecken, warum Abfragen langsamer sind als gedacht.
Z.B. wurde bei einer einfachen Abfrage
where numfeld = 0
das numfeld in cast(numfeld as ...) umgeschrieben, was eine Indexverwendung ausschloss.
Durch numfeld = cast(0 as numfeld-typ) wurde dann wieder der Index angenommen.
Ich bin nun mal auf einem Kundensystem und zeige euch einen SQL, der so einiges zusammenfasst:
- Bestandsanalyse über den gesamten Artikelstamm
- Summe Lagerbestände über alle Läger
- Ermitteln Anzahl Unterdeckungen Mindest-/-lagerbestand
- Ermitteln Verbrauchsdaten für 6/12/18/24-Monte (Pivotfunktion)
- Errechnen Inventurwert an Hand des letzten EK-Wertes
- Ausgabe in einer Datei für eine CSV-Ausgabe
Und das alles in einem SQL mittels Lateral, was ohne nicht performant gewesen wäre bzw. nur per Programm lösbar.
Felder, die mit "§" anfangen sind Userparameter, die per Programm dann ersetzt werden.
Der gravierende Vorteil hier ist, dass der Join auf Satz- und nicht Group By-Ebene erfolgt und der Optimizer direkt die Daten mit der Where-Klausel liest.
Wichtig zu wissen ist, dass der Lateral Join eine Single-Access-Optimierung erfährt, da mit jeder Zeile auf den Join direkt zugegriffen wird.
-- Ausgabetabelle erstellen
create TABLE qtemp/bestausw as (
select a.ARTIKELKENNZEICHEN
,a.ARTIKELNUMMER
,a.ARTIKELBEZEICHNUNG1
,a.ARTIKELBEZEICHNUNG2
,a.ARTIKELBEZEICHNUNG3
,a.ARTIKELBEZEICHNUNG4
,a.VERKAUFSFAEHIG
,a.VERKAUFSFAEHIGTEXT
,a.PRODUKTSCHLUESSEL
,a.PRODUKTSLTEXT
,a.ARTIKELGRUPPE
,a.ARTIKELGRUPPETEXT
,a.ARTIKELHAUPTGRUPPE
,a.ARTIKELHAUPTGRUPPETEXT
,a.ANLAGEDATUM
,l.*
-- Berechnung aus letztem Einkauf (Tabelle D360)
,dec(round(w.lewerti / nullif(w.learbestd, 0) * l.LAGERBESTAND, 2), 15 , 2) Inventurwert
,dec(round(w.lewertv / nullif(w.learbestd, 0) * l.LAGERBESTAND, 2), 15 , 2) Vollkostwert
,l.Auftragsbestandgesamt - l.AuftragsbestandGebucht AuftragsbestandNichtGebucht
,s.*
,v.*
,round(v.Verbrauch6M / 6.0, 2) DVerbrauch6M
,round(v.Verbrauch12M / 12.0, 2) DVerbrauch12M
,round(v.Verbrauch18M / 18.0, 2) DVerbrauch18M
,round(v.Verbrauch24M / 24.0, 2) DVerbrauch24M
,round((v.Verbrauch6M / double(6)) * 100.00
/ nullif(v.Verbrauch12M / double(12), 0) - 100.00, 2) Prozent6M
,round((v.Verbrauch12M / double(12)) * 100.00
/ nullif(v.Verbrauch18M / double(18), 0) - 100.00, 2) Prozent12M
,round((v.Verbrauch18M / double(18)) * 100.00
/ nullif(v.Verbrauch24M / double(24), 0) - 100.00, 2) Prozent18M
,round((v.Verbrauch24M / double(24)) * 100.00
/ nullif(v.DVerbrauchGesamt, 0) - 100.00, 2) Prozent24M
, current_timestamp as Laufdatum
from ARTSTAM01V a
-- Ermitteln Summe aller Bestände und Reservierungen
left join lateral (
select
sum(B.ARLAGB) Lagerbestand
,sum(B.ARBB) Bestellbestand
,sum(B.ARMINB) Mindestbestand
,sum(B.ARMELD) Meldebestand
,sum(B.ARAB + b.ARREVB) Auftragsbestandgesamt
,sum(B.ARREVB) Reservierterbestand
,sum(b.DPA01 + b.DPA02 + b.DPA03 + b.DPA04 + b.DPA05
+ b.DPA06 + b.DPA07 + b.DPA08 + b.DPA09 + b.DPA10
+ b.DPA11 + b.DPA12 + b.DPA13 + b.DPA14 + b.DPA15
+ b.DPA16 + b.DPA17 + b.DPA18 + b.DPA19 + b.DPA20
+ b.DPA21 + b.DPA22 + b.DPA23 + b.DPA24 + b.DPA25
+ b.DPA26 + b.DPA27 + b.DPA28 + b.DPA29 + b.DPA30
+ b.DPA31 + b.DPA32 + b.DPA33 + b.DPA34 + b.DPA35
+ b.DPA36 + b.DPA37 + b.DPA38 + b.DPA39 + b.DPA40
+ b.DPA41 + b.DPA42 + b.DPA43 + b.DPA44 + b.DPA45
+ b.DPA46 + b.DPA47 + b.DPA48 + b.DPA49 + b.DPA50
+ b.DPA51 + b.DPA52 + b.DPA53
+ b.DPRAB
) AuftragsbestandGebucht
from d031 B
inner join LAGER ls
on ls.KONZERNGESELLSCHAFT = a.KONZERNGESELLSCHAFT
where a.KONZERNGESELLSCHAFT = B.ARORT
and a.ARTIKELKENNZEICHEN = B.ARKZ
and a.ARTIKELNUMMER = B.ARNR
and ls.LAGERORT =
case §LAGER when '' then '1' else §LAGER end
and B.ARHALB in (ls.lagerort, ls.QSLAGER, ls.RAMPENLAGER)
group by B.ARORT, b.ARKZ, b.ARNR
) l on 1=1
-- Ermitteln Unterdeckungen
left join lateral (
select
count(*) AnzahlTage
,sum(UNTERDECKUNGMINDESTBESTAND) UNTERDECKUNGMINDESTBESTAND
,sum(UNTERDECKUNGLAGERBESTAND) UNTERDECKUNGLAGERBESTAND
from D360VK01P d3
where d3.KONZERNGESELLSCHAFT = a.KONZERNGESELLSCHAFT
and d3.ARTIKELKENNZEICHEN = a.ARTIKELKENNZEICHEN
and d3.ARTIKELNUMMER = a.ARTIKELNUMMER
and d3.STATISTIKJAHR = year(current date)
and d3.LAGERNUMMER =
case §LAGER when '' then '1' else §LAGER end
group by KONZERNGESELLSCHAFT
,ARTIKELKENNZEICHEN
,ARTIKELNUMMER
,STATISTIKJAHR
,LAGERNUMMER
) S on 1=1
-- Ermitteln Monatsverbräuche als Pivot
left join lateral (
select
sum(case when v.ARJJMM between year(current date - 6 months) * 100 + month(current date - 6 months)
and year(current date - 1 months) * 100 + month(current date - 1 month)
then arvb end) Verbrauch6M
,sum(case when v.ARJJMM between year(current date - 12 months) * 100 + month(current date - 12 months)
and year(current date - 1 months) * 100 + month(current date - 1 month)
then arvb end) Verbrauch12M
,sum(case when v.ARJJMM between year(current date - 18 months) * 100 + month(current date - 18 months)
and year(current date - 1 months) * 100 + month(current date - 1 month)
then arvb end) Verbrauch18M
,sum(case when v.ARJJMM between year(current date - 24 months) * 100 + month(current date - 24 months)
and year(current date - 1 months) * 100 + month(current date - 1 month)
then arvb end) Verbrauch24M
,sum(arvb) VerbrauchGesamt
,count(*) - 1 Monate
,round( sum(arvb) / double(nullif( count(*)-1, 0)), 2) DVerbrauchGesamt
from d220 v
where v.ARORT = a.KONZERNGESELLSCHAFT
and v.ARKZ = a.ARTIKELKENNZEICHEN
and v.ARNR = a.ARTIKELNUMMER
group by v.ARORT, v.ARKZ, v.ARNR
) V on 1=1
-- ermitteln letzten Einkauf je Artikel
left join lateral (
select *
from d360 w
where w.lenlg = a.KONZERNGESELLSCHAFT
and w.LELAGNR =
case §LAGER when '' then '1' else §LAGER end
and w.LEARKZ = a.ARTIKELKENNZEICHEN
and w.LEARNR = a.ARTIKELNUMMER
order by ledatum desc
limit 1
) w on 1=1
where a.KONZERNGESELLSCHAFT = §KONZERN
and status <> 'L'
) with data;
Hi,
nicht schlecht :-)
Gruß
Michael
Die Zwischenrechung mit Double() ist in der Tatsache begründet, dass SQL die Zwischenfelder zu groß macht (> 31 Stellen) und den Rechenvorgang dann abbricht. Auch bei Dec64 steigt SQL da aus.
Mit Dec(Round ()) bekommt man das dann wieder hin.
Und für alle, die wissen wollen was die IBM i noch so alles kann:
In einem ERP laufen Verbrauchszahlen aus dem Ruder, da unklar ist wer wann wo was genau bucht. Daher musste ich eine Korrektur bauen, die mit SQL natürlich erheblich einfacher war.
Die Zieltabelle enthält verdichtete Verbrauchszahlen auf JJJJMM-Basis je Artikel.
Die Verbräuche werden durch Lagerbewegungen bestimmt, die in 2 Tabellen vorkommen.
Der MERGE erlaubt dann die Korrektur auf dem Ziel. CTE's sind bei dieser Form nicht erlaubt, jedoch beliebige derived Tables, sowohl scalar also joined.
Hier noch mal zur Erklärung, dass ein "Order by" mit "Limit 1" große Vorteile bietet, da komplexe Bedingungen um genau 1 Ergebnis zu erhalten nun obsolet sind.
if IndexChecked = *off;
IndexChecked = *on;
// nur zur Sicherheit, falls der Index entfernt wurde
exec sql create index FILELIB.D060IV
on D060 (PLORT, PLDAT, PLMENG, PLVGA, PLPGM);
endif;
exec sql
merge into D220 to
using (
select *
from (
select plort ARORT
,plnrkz ARKZ
,plnr ARNR
,plhalb ARHALB
,year(plstda) * 100 + month(plstda) ARJJMM
,sum(0-plmeng) arvb
from (
select plort
,plnrkz
,plnr
,plhalb
,plmeng
,pldat
,plvga
,plgrau
,plpgm
,case when plvga <> 500 then pldat
else (select streda
from D201
where stpnlg = v.plort
and streda >= v.pldat
and staunr = v.plgrau
order by stpnlg
,stjjjj
,stsa
,stnr
,staunr
,streda
limit 1
)
end plstda
from (
select plort
, plnrkz
, plnr
, case plhalb
when '1' then '1'
else '2'
end as plhalb
,pldat
,plmeng
,plvga
,plgrau
,plfanr
,plpgm
from D060 a
where a.PLDAT >= first_day( current date - 1 month) // 1. des Vormonats
and a.PLORT = :pKonzerngesellschaft
and a.PLNRKZ = :pArtikelkennzeichen
and a.PLNR = :pArtikelnummer
and a.PLMENG < 0.00
and a.PLVGA in (100, 110, 140, 500, 610) // VGA Verbräuche
and a.PLPGM not in ('FAKOMMT01R', 'BOPFEAU01R', 'BOPA213', 'BOPA213TAB')
union all
select plort
, plnrkz
, plnr
, case plhalb
when '1' then '1'
else '2'
end as plhalb
,pldat
,plmeng
,plvga
,plgrau
,plfanr
,plpgm
from D062 a
where a.PLDAT >= first_day( current date - 1 month) // 1. des Vormonats
and a.PLORT = :pKonzerngesellschaft
and a.PLNRKZ = :pArtikelkennzeichen
and a.PLNR = :pArtikelnummer
and a.PLMENG < 0.00
and a.PLVGA in (100, 110, 140, 500, 610) // VGA-Verbräuche
and a.PLPGM not in ('FAKOMMT01R', 'BOPFEAU01R', 'BOPA213', 'BOPA213TAB')
) v
)
where not plstda is null -- noch nicht in Statistik
group by plort
,plnrkz
,plnr
,plhalb
,year(plstda) * 100 + month(plstda)
)
) fr
on fr.arort = to.arort
and fr.arhalb = to.arhalb
and fr.arkz = to.arkz
and fr.arnr = to.arnr
and fr.arjjmm = to.arjjmm
and fr.arvb <> to.arvb
when matched then
update set to.arvb = fr.arvb
;