GROUP BY, wobei null/leer und Wert gruppiert werden

[Rouven]

Hi,

ich hab da mal ein etwas ungewöhnliches Problem. Bevor jemand auf die Idee kommt das auf ein falsches Design zu schieben: Ja, das Design der Relation ist "falsch" bzw. ungünstig, muss aber auf Grund von zugelieferten Daten so beibehalten werden.

Tabelle Artikel:
id  |  tkz  |  hstk
1   |  abc  |
2   |  abc  |  xyz
3   |  def  |  123
4   |  def  |  456
5   |  hij  |  mnk
6   |  hij  |  mnk

Es soll eine Liste potenziell redundanter Artikel aufgestellt werden. Artikel sind potenziell redundant genau dann, wenn sie bei gleichem hstk das gleiche tkz haben, oder bei gleichen tkz der hstk bei einem befüllt und bei dem anderen leer ist.
Hier: Artikel 1/2 und 5/6

Grundstruktur:
SELECT tkz
FROM artikel
GROUP BY s.u.
HAVING COUNT(*) > 1

Ein Abfrage
GROUP BY tkz
liefert leider auch 'def' raus, die sind aber verschieden.
Eine Abfrage
GROUP BY tkz, hstk
verfehlt 'abc', weil leer und 'xyz' verschiedene Werte sind.
Jemand eine Idee, z.B. über GROUPING SETS oder ROLLUP?

DBMS ist eine IBM DB2 8.1, prinzipiell muss die Abfrage aber auf einer 7.x lauffähig sein.

MfG
Rouven

[Vinzenz Mai]

Hallo Rouven,

Tabelle Artikel:
id  |  tkz  |  hstk
1   |  abc  |
2   |  abc  |  xyz
3   |  def  |  123
4   |  def  |  456
5   |  hij  |  mnk
6   |  hij  |  mnk

Es soll eine Liste potenziell redundanter Artikel aufgestellt werden. Artikel sind potenziell redundant genau dann, wenn sie bei gleichem hstk das gleiche tkz haben, oder bei gleichen tkz der hstk bei einem befüllt und bei dem anderen leer ist.

Unter der Annahme, dass zu gleichen tkz-Einträgen stets zwei hstk-Einträge existieren und dabei mindestens einer nicht NULL ist, hätte ich folgende Idee:

Eine Abfrage
GROUP BY tkz, hstk
verfehlt 'abc', weil leer und 'xyz' verschiedene Werte sind.

Somit bräuchte man noch eine Abfrage, die genau die Paare wie 'abc' zurückliefert. Beide Ergebnismengen mit UNION verbinden.

  
-- Zuerst die Paare mit einmal NULL in hstk  
SELECT  
 A.tkz,  
 MAX(A.hstk) AS hstk  
FROM Artikel A  
LEFT OUTER JOIN Artikel B  
ON A.tkz = B.tkz  
WHERE B.hstk IS NULL  
GROUP BY A.tkz  
  
UNION  
-- und nun die Dubletten  
SELECT  
 C.tkz,  
 C.hstk  
FROM Artikel C  
GROUP BY tkz, hstk  
HAVING COUNT(*) > 1  

liefert mir (MS SQL-Server 2005) folgendes Ergebnis:

tkz        hstk
---------- ----------
abc        xyz
hij        mnk
Warnung: Ein NULL-Wert wird durch einen Aggregat- oder sonstigen SET-Vorgang gelöscht.

(2 Zeile(n) betroffen)

Ich hab's noch nicht mit der DB2 getestet, denke aber es könnte auch dort das gewünschte Ergebnis liefern.

Freundliche Grüße

Vinzenz

[Rouven]

Hi Vinzenz,

Unter der Annahme, dass zu gleichen tkz-Einträgen stets zwei hstk-Einträge existieren und dabei mindestens einer nicht NULL ist, hätte ich folgende Idee:

Ich glaube Iljas Lösung ist da eher zielführend, weil derartige Annahmen unangebracht sind. Theoretisch könnte ein Artikel dutzende Duplikate mitbringen. Wir kriegen von extern Daten geliefert, von verschiedenen Quellen, wobei weder die Quellen untereinander noch die Quellen intern konsistent sind.

MfG
Rouven

[Vinzenz Mai]

Hallo Rouven,

Ich glaube Iljas Lösung ist da eher zielführend, weil derartige Annahmen unangebracht sind.

mir gefällt Iljas Lösung auch besser, sie ist einfacher. Das Resultat ist unter MSSQL2005 das gleiche:

  
SELECT  
    C.tkz,  
    C.hstk  
FROM Artikel C  
GROUP BY tkz, hstk  
HAVING COUNT(*) > 1  
  
UNION  
  
SELECT  
    tkz,  
    'irgendwas'  
FROM artikel  
GROUP BY tkz  
HAVING COUNT(*) <> COUNT(hstk)  

Resultat:
tkz        hstk
---------- ----------
abc        bla
hij        mnk
Warnung: Ein NULL-Wert wird durch einen Aggregat- oder sonstigen SET-Vorgang gelöscht.

(2 Zeile(n) betroffen)

Freundliche Grüße

Vinzenz

[Ilja]

yo,

das IBM DBMS kenne ich leider nicht. aber die lösung hast du eigentlich schon selbst gefunden. es sind zwei verschiedene bedingungen. insofern auch zwei abfragen mit UNION verbunden und ein kleiner trick mit COUNT().....

SELECT tkz, hstk
FROM artikel
GROUP BY tkz, hstk
HAVING COUNT(*) > 1

UNION

SELECT tkz, 'platzhalter' AS Platzhalter
FROM artikel
GROUP BY tkz
HAVING COUNT(*) <> COUNT(HSTK)

Ilja

[Rouven]

Hi Ilja,

OK, das könnte ich tatsächlich mal probieren, wobei mir bisher unbekannt war, dass COUNT(*) ein von COUNT(HSTK) unterschiedliches Ergebnis liefern kann sofern beides  in dieser Art auf die Gruppierung angewendet wird. Aber ich werde das testen...

MfG
Rouven

[Ilja]

yo,

OK, das könnte ich tatsächlich mal probieren, wobei mir bisher unbekannt war, dass COUNT(*) ein von COUNT(HSTK) unterschiedliches Ergebnis liefern kann sofern beides  in dieser Art auf die Gruppierung angewendet wird. Aber ich werde das testen...

der trick dabei ist, dass COUNT(*) alle datensätze zählen wird, aber COUNT(HSTK) nur die datensätze, die in der entsprechenden spalte keine NULL werte enthalten. und darum geht es, festzustellen, ob NULL werte sich in der gruppieruing befinden.

Ilja

[Vinzenz Mai]

Hallo Ilja,

der trick dabei ist, dass COUNT(*) alle datensätze zählen wird, aber COUNT(HSTK) nur die datensätze, die in der entsprechenden spalte keine NULL werte enthalten. und darum geht es, festzustellen, ob NULL werte sich in der gruppieruing befinden.

das gleiche Ziel hatte ich auch vor Augen. Deine Lösung ist jedoch weitaus einfacher, eleganter und gefällt mir deswegen besser :-)

Freundliche Grüße

Vinzenz

PS: Was machen Deine Access-Formulare?

[Ilja]

yo,

das gleiche Ziel hatte ich auch vor Augen. Deine Lösung ist jedoch weitaus einfacher, eleganter und gefällt mir deswegen besser :-)

in prinzip hatten wir auch beide den gleichen gedanken, zwei verschiedene grupperieungen mit UNION verbinden. beim überlegen, wie man mindesntens einen NULL wert und mindestens einen nicht NULL wert feststellen kann, ist manchmal auch einfach zufall, was einen dabei einfällt.

PS: Was machen Deine Access-Formulare?

uhmm, vor allem kopfschmerzen. ich arbeite dort nur noch drei wochen und habe neben der edv inventariserung noch zwei andere projekte am hals. das tabellen-design war schon schwer genug, hat mich einige tage gekostet. was mich aber ein wenig verärgert ist, dass ich die datenbank nicht für mich mache, sondern für die anderen, die in zukunft damit arbeiten können. es scheint sie aber nicht wirklich zu interessieren und boykotieren das projekt zum teil.

dabei ist genug zu tun, ich muss fünf jahre inventariserung nachholen und darf zur freude auch alle daten noch selber in die tabellen eingeben. dass sind schon ein paar tausend datensätze. mein chef will zwar wenigstens diese datenbank haben, aber ist auch nicht wirklich eine hilfe.

insofern habe ich mich leider noch nicht um die formulare kümmern können, stecke noch bei der dateneingabe fest......

Ilja