SQL Filtern von doppelten Werten

[tobi_712]

Hallo zusammen,
habe bereits einiges nun ausprobiert, aber stehe auf dem Schlauch... hoffe ihr könnt mir helfen

Habe eine Auftrag eine Abfrag zu erstellen, die alle gelieferten (über Lieferscheine LFSCHEIN) Artikel (ARTIKELC) an einen Kunden (KUNDENA) über einen gewissen Zeitraum auflistet.
Hier jedoch die Krux: Ich möchte jeden Artikel nur einmal auflisten.

Über ein Distinct komm ich leider nicht weiter, ebenso nicht über das COUNT, HAVING und GROUP by.

Hier mal meine Abfrage:

select
K.KUNDEN_NR
, K.NAME1
, K.NAME2
, K.STRASSE
, K.LAND
, K.PLZ
, K.ORT
, G.ARTGRUPPE as Warengruppe
, G.BEZEICHNUN as WG_Bezeichnung
, L.ARTIKELNR as EDV_Nummer
, L.K_ATNR as Kundenartikel_NR
, L.ARTBEZ1
, L.ARTBEZ2
, L.ARTBEZ3
, AK.FELD_C1 as Artikelbezeichnung4
, AK.FELD_C2 as Artikelbezeichnung5
, AK.FELD_C3 as Artikelbezeichnung6
, A.HERKUNFT_1 as Zolltarifnummer
, I.BEZEICH1 as Bezeichnungs_Zolltarif
, MAX(LIEF_DATUM) as Lieferdatum
, Count(A.ARTIKEL_NR)
from LFSCHEIN L
JOIN ARTIKELC A on L.ARTIKELNR = A.ARTIKEL_NR
JOIN ART_AKA AK on A.ARTIKEL_NR = AK.ARTIKEL_NR
JOIN KARTIKEL KA on A.ARTIKEL_NR=KA.ARTIKEL_NR
JOIN GRUPPEC G on A.ARTGRUPPE = G.ARTGRUPPE
JOIN KUNDENA K on KA.KUNDEN_NR = K.KUNDEN_NR
JOIN IM_STAMM I on A.HERKUNFT_1 = I.KENNUNG
where K.KUNDEN_NR=30002
AND L.LIEF_DATUM>='01/01/2020'
GROUP by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
HAVING (COUNT(A.ARTIKEL_NR)=1)
order by L.ARTIKELNR

Datenbank: Advantage Database Server
Sprache: SQL

Erklärung zu Tabellen:
Warengruppen (GRUPPEC)
Kundenartikel (KARTIKEL)
Artikelzusatztexte (ART_AKA)
Tabelle mit Zolltarifnummern (IM_STAMM)

Vorab schon mal vielen Dank für eure Hilfe!

 

[Madman1209]

Hi,

Über ein Distinct komm ich leider nicht weiter, ebenso nicht über das COUNT, HAVING und GROUP by.

wieso nicht - was genau passiert bei z.B. "GROUP BY"?

VG,
Mad

 

[tobi_712]

Danke für die fixe Antwort.

Vermutlich, da ich das Group by über mehrere Tabellen machen muss..
Habe anbei mal die Ergebnistabelle als Excel exportiert.
(Nicht wundern, Kundendaten sind entfernt...)

select
--K.KUNDEN_NR
--, K.NAME1
--, K.NAME2
--, K.STRASSE
--, K.LAND
--, K.PLZ
--, K.ORT
G.ARTGRUPPE as Warengruppe
, G.BEZEICHNUN as WG_Bezeichnung
, L.ARTIKELNR as EDV_Nummer
, L.K_ATNR as Kundenartikel_NR
, L.ARTBEZ1
, L.ARTBEZ2
, L.ARTBEZ3
, AK.FELD_C1 as Artikelbezeichnung4
, AK.FELD_C2 as Artikelbezeichnung5
, AK.FELD_C3 as Artikelbezeichnung6
, A.HERKUNFT_1 as Zolltarifnummer
, I.BEZEICH1 as Bezeichnungs_Zolltarif
, MAX(LIEF_DATUM) as Lieferdatum
, Count(A.ARTIKEL_NR)
from LFSCHEIN L
JOIN ARTIKELC A on L.ARTIKELNR = A.ARTIKEL_NR
JOIN ART_AKA AK on A.ARTIKEL_NR = AK.ARTIKEL_NR
JOIN KARTIKEL KA on A.ARTIKEL_NR=KA.ARTIKEL_NR
JOIN GRUPPEC G on A.ARTGRUPPE = G.ARTGRUPPE
JOIN KUNDENA K on KA.KUNDEN_NR = K.KUNDEN_NR
JOIN IM_STAMM I on A.HERKUNFT_1 = I.KENNUNG
where K.KUNDEN_NR=30002
AND L.LIEF_DATUM>='01/01/2020'
GROUP by 1,2,3,4,5,6,7,8,9,10,11,12--,13,14,15,16,17,18,19
HAVING (COUNT(A.ARTIKEL_NR)=1)
order by L.ARTIKELNR

Danke und Grüße
Tobi

 

[Enurian]

Wenn GROUP BY bzw. DISTINCT "nicht funktionieren", sind in irgendeiner Spalte doch noch unterschiedliche Daten drin.
Also zwei vermeintlich doppelte Datensätze nehmen und jede Spalte einzeln prüfen, bis du den Übeltäter hast.
Du kannst genauso gut Spalten im Select aus- und einkommentieren, um dem Problem auf die Schliche zu kommen.

 

[tobi_712]

Danke für deine Antwort,
tatsächlich weiß ich welche Spalte hier der Übeltäter ist: Lieferdatum
Die Filterung funktioniert dahingehend.
Nur Möchte ich eigentlich hauptsächlich auf die Artikelnummer filtern...

 

[Enurian]

Dann verstehe ich das Problem nicht.
Lieferdatum hat doch mit MAX bereits eine Aggregatsfunktion, sodass die Spalte beim GROUP BY nicht das Problem sein kann.
Und was willst du jetzt filtern?

 

[tobi_712]

Also die Abfrage soll für eine Langzeit-Lieferantenerklärung dienen.
Hier sollen nachher in einem Report die Artikel aufgelistet werden, die in einem bestimmten Zeitraum geliefert wurden.
Jedoch soll hier pro Artikelnummer einer Zeile gedruckt werden und eben nicht, falls die Artikel an verschiedenen Tagen mehrmals geliefert wurden, mehrere Zeilen.

Ist das so verständlich?

 

[Enurian]

Ist das so verständlich?

Ja, aber was funktioniert nicht? Mach es mit GROUP BY und Aggregatsfunktion auf das Lieferdatum, wenn das als einziges unterschiedlich ist, wo ist das Problem? Dem GROUP BY ist völlig Schnuppe, woher die selektierten Werte stammen, bezüglich deiner Vermutung irgendwas mit mehreren Tabellen wäre komisch.

 

[tobi_712]

Okay sorry mein Fehler... völlig durch nach mehreren Stunden dran...
Es lag an den Kundenartikelnummern und das ist ja auch richtig dann...

Vielen Dank für den Hinweis und die Beharrlichkeit!

 

[RalphS]

Du bist Dir hoffentlich im Klaren, daß das absoluter Mißbrauch von GROUP BY ist? Falls nicht, laß es Dir gesagt sein.

Schau mal bei Window-Funktionen. Das sind Aggregate ohne das Grouping, bzw wird individuell auf dem Feld aggregiert.

Außerdem: Spaltennummern sind an jeder Stelle in SQL eine ganz. schlechte. Idee.
Namen ausschreiben. Sonst fliegt das mit jeder Änderung der Abfrage um die Ohren.

Gemäß Anforderung könnte man hier von dem S in SQL profitieren. Es geht immer um Relationen. Was die sind, steht nirgendwo geschrieben. Ergebnisse von Abfragen sind genauso Relationen wie Tabellen.

Daher könnte man zunächst einen inneren Rumpf bauen, sozusagen Abfrageskelett mit nur den nötigsten Informationen. Dann paßt ggfs. auch ein GROUP BY auf eine oder zwei Spalten, wenn sich das ergibt.
Und in Schritt zwei dann eine Hülle drumherum, die dann alle erforderlichen Daten an das Skelett dranhängt.

In etwa so:

SELECT h.spalteA, h.spalteB, h.spalteC, werte.... FROM hülle h
JOIN ( SELECT idA, idB, idC FROM woauchimmer
  JOIN was auch immer
WHERE sonstwas
) s ON s.idA = h.spalteA AND s.idB = h.spalteB
WHERE ....
-- GROUP BYs, HAVINGs, was auch immer

S(kelett) hat dann nur Schlüsselspalten (im Zweifel exakt eine, wenn das schon reicht). Dopplungen und übermäßiges Datenschaufeln ausgeschlossen.

 

[Madman1209]

Hi,

@RalphS

mit einer Window-Function wird aber doch das Ziel des TE

Hier jedoch die Krux: Ich möchte jeden Artikel nur einmal auflisten.

nicht erreicht, oder täusche ich mich?

A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row

damit hätte er doch dann nichts gewonnen - oder?

VG,
Mad

 

[parats]

Du bist Dir hoffentlich im Klaren, daß das absoluter Mißbrauch von GROUP BY ist? Falls nicht, laß es Dir gesagt sein.

Schau mal bei Window-Funktionen. Das sind Aggregate ohne das Grouping, bzw wird individuell auf dem Feld aggregiert.

Außerdem: Spaltennummern sind an jeder Stelle in SQL eine ganz. schlechte. Idee.
Namen ausschreiben. Sonst fliegt das mit jeder Änderung der Abfrage um die Ohren.

Dieses!
Ich kann verstehen, dass Group By gerne für das Rauswerfen von Duplikaten genutzt wird, aber das ist nicht der eigentliche Zweck. Denn zuerst sollte man überhaupt wissen woher die Duplikate kommen und dann eben per Window Function sauber aussortieren. Leider wird letzteres kaum als praktikables Beispiel angeführt, da Group By oftmals deutlich kürzer und einfacher zu verstehen ist.

@Madman1209 doch wird es. Mit entsprechendem partition by und gewünschter Sortierung kann danach auf die erste Zeile gefiltert werden.

 

[Madman1209]

Hi,

@parats

dann nochmal die Frage: wie genau liefert mir die Window Function dann die einzelnen Elemente? Die Doku spricht hier ganz klar von "a result for each row".

Das soll im Übrigen keine Provokation sein, mich würde wirklich interessieren - ganz konkret - wie es hier gemeint ist als Lösung.

VG,
Mad

 

[parats]

Das "result for each row" ist das Ergebnis aus der Sortierung unterteilt nach dem partition by. Beginnend bei 1 für bspw Row_Number(). Das ist der innere Teil der dann per derived Table im äußeren Teil gefiltert wird auf = 1.

Ralph hat es oben gut beschrieben.
Ziel ist vorab die Daten so sauber zu bekommen, dass Duplikate entfernt worden sind. Zusatzinformationen zieht man dann danach.

 

[RalphS]

Simples Beispiel. Folgende Tabelle.
( id ; name )
1; Peter
1; Paul
2; Christina

SELECT id, name, count(*) OVER (partition by id)

Ergebnis:
1 Peter 2
1 Paul 2
2 Christina 1

Sagt folgendes:
Mein erstes Ergebnis hat ID 1, Name = Peter. Ganz normal. Die 2 hinten sagt: so viele IDs = 1 habe ich. Mit dieser 2 sehe ich also, daß meine ID vielleicht Primärschlüssel sein will, aber nicht kann (> 1). Dies unabhängig davon, daß der Name beider IDs verschieden ist.

Ich muß nicht gruppieren, um zu erfahren, daß ich zweimal dieselbe ID hab. Ich kann aber später zB mit WHERE auf das Ergebnis der Windowfunktion filtern und sagen, nur diejenigen Ergebnisse mit = 1, oder > 1, oder sonst wie was.

 

[Madman1209]

Hi,

ich beziehe mich hier auf diese Doku oder dieses Beispiel. Wie genau ersetzt mir das die Aggregation? Mit einer einzelnen Abfrage ist das doch nicht wirklich zu lösen so wie der TE das will. Oder wie wäre da konkret der Ansatz?

VG,
Mad

 

[parats]

Genau und worauf man verzichten möchte sind unkontrollierte Gruppierungen die den Speicher der Maschine unnötig auslasten. Ich weiß, dass der minimale Ansatz heutzutage nicht mehr so nötig ist, denn die Ressourcen werden immer mehr. Aber man sollte sich immer angewöhnen die Maschinen nur so stark zu belasten wie die Aufgabenstellung es erfordert.

 

[Madman1209]

Hi,

das Beispiel von RalphS finde ich unpassend, da die Aufgabenstellung hier eine andere ist. Mich interessiert nicht, ob "ID" ein Schlüssel sein kann. Aber um mal bei dem einfachen Beispiel zu bleiben:

ID-Vorname
1-Peter
2-Paul
3-Paul
4-Steffi

wenn meine Aufgabenstellung lautet "ich möchte jeden Vornamen nur einmal ausgeben" - was spricht dann gegen "GROUP BY"? Wo laste ich die Maschine damit aus? Mal abgesehen von einem "DISTINCT" an der Stelle, aber sei es drum. Wo spare ich mir da etwas?

VG,
Mad

 

[parats]

Die Aufgabenstellung ist, gebe mir jede ID einmal aus, dir fehlt eine doppelte ID und diese ist nur ein Platzhalter. Dein Name Paul ist genauso ein Primärschlüssel in der Abfrage, da er mindestens mal unique ist.
Mit einer Abfrage auf 4 Zeilen ist das egal, wirklich. Aber wenn wir über Artikelnummern aus Bestellungen und vielleicht Lieferscheinen der letzten x Jahre reden, dann ist sowas unkontrolliert.

Es gibt btw noch mehr solcher Beispiele wo der vielleicht erste Ansatz nicht unbedingt der Beste Weg ist. Z.b. das filtern von Daten per Join und nicht per Subselect oder exists.

 

[Madman1209]

Hi,

wo genau liest du "jede ID einmal"? Weder die Artikelnummer noch die Artikelbezeichnung (je nachdem was der TE gruppieren möchte) können Unique sein, sonst hätte er ja keine Duplikate.

Ich kenne genau solche Abfragen, die über Millionen von Bestelldatensätzen laufen und weder Speicher- noch Performanceprobleme machen.

Von daher meine Frage: wo genau bringt mir das was genau?

Aber egal, ich les mich mal ein. Sehe bisher nur nicht, wo mir das irgendwas bringt, was ich nicht so auch habe.

VG,
Mad