Doppelzählungen verhindern

[Kristin]

Hi!

wie kann ich Doppelzählungen, wenn ich z.B. in der where Klausel ein substr(xyz, 1, 5) verwende.

Der SQL befehl sieht ungefähr wie folgt aus:

SELECT  
    a.xyz  
    TO_CHAR(SUM(COALESCE(a.y, 0)), '99999999') AS x,  
    TO_CHAR(SUM(COALESCE(a.x, 0)), '99999999') AS y,  
    ...  
FROM ... a  
    INNER JOIN ... b ON a.plz = b.plz ...  
    INNER JOIN ... c ON ... substr(a.gkz, 1, 5) = b.gkz ...  
...

a.a ist eine 8 stellige Zahl mit führenden Nullen.

wenn z.B. folgende Zahlen existieren 00123456, 00123457, 00123458
wird der Wert von der Tabelle a 3 mal verwendet.

Aktuell löse ich es wie folgt:

SELECT  
    a.xyz  
    COALESCE(a.y, 0), '99999999') AS x,  
    COALESCE(a.x, 0), '99999999') AS y,  
FROM  
(  
    SELECT DISTINCT  
        a.plz  
        a.xyz  
        TO_CHAR(SUM(COALESCE(a.y, 0)), '99999999') AS x,  
        TO_CHAR(SUM(COALESCE(a.x, 0)), '99999999') AS y,  
        ...  
    FROM ... a  
        INNER JOIN ... b ON a.plz = b.plz ...  
        INNER JOIN ... c ON ... substr(a.gkz, 1, 5) = b.gkz ...  
    ...  
)  
...

gibt es eine elegantere Lösung?

Kristin

[Kristin]

es handelt sich um eine PostgreSQL 7.4 Datenbank

[Ilja]

moin,

a.a ist eine 8 stellige Zahl mit führenden Nullen.

ich kan keine a.a spalte finden, du meinst a.gkz ? wenn man schon eine sql abfrage verkürzt darstellt, dann doch mit allen notwendigen infos, da scheint zum beispiel eine gruppierung noch zu existieren, da du aggregierst mit der SUM funktion...

was dein problem angeht, joins können problematisch sein, wenn dadurch die anzahl der datensätze verändert wird, die man eigentlich haben will. deswegen nehme ich in diesen bestimmten fällen keine "unötigen" joins mit rein, sondern nehme nur die tabellen, die mir die gewünschte anzahl an datensätze liefert.

brauche ich dazu noch spalten aus anderen tabellen, dann arbeite ich sehr gerne mit korrelierten unterbafrgen, weil sie die anzahl der gewünschten datensätze nicht verwässert. wie diese aber genau auszusehen hat, da fehlen dann noch in paar mehr infos über deine tabelle und was genau du erreichen willst inklusive beispieldaten.

Ilja

[Kristin]

Hi ilja,

In der ersten Tabelle a sind Mitgliederzahlen
Beispielformat
plz, anzahl

dann gibt es eine Tabelle b, welche ein mapping von plz zu gkz (8 stellige Zahl) erledigt.

Bei der Abfrage sollen jetzt alle Mitgliederzahlen für jeden Landkreis einzeln ausgegeben werden. Die Landkreise (die ersten 5 stellen einer gkz) werden als Liste übergeben (substr(b, 1, 5) in ('.....', '.....')).

Bei Hamburg (gkz 02000000) und Berlin (gkz 11000000) tauchen z.b. keine Doppelzählungen auf, da es keine weitere gkz mit den ersten 5 Zahlen gibt

Der Landkreis Lindau (Bodensee) hat 20 verschiedene gkzs (09776111, 09776112, 09776113, 09776114, 09776115, 09776116, 09776117, 09776118, 09776120, 09776121, 09776122, 09776124, 09776125, 09776126, 09776127, 09776128, 09776129, 09776130 und 09776131).
Mein Problem ist jetzt, wenn ich in der Abfrage substr(b.gkz) = '09776' mache, einige Mitglieder pro plz dann doppelt gezählt werden.

ich hoffe, dass es jetzt verständlicher ist

gkz = Gemeindekennzahl

Kristin

[Ilja]

moin,

das erinnert mich alles an die zeit, als ich in einem rathaus in berlin für die stadtplaner gearbeitet hatte, da waren die daten auch so herlich im "beamten-stil" aufbereitet.

nun aber wieder zu dir, weg von meinen alten erinnerungen. grundsätzlich ist dieser join problematisch, mal abgeshen davon, dass deine tabelle b nicht normalisiert ist (atomar). wenn ich alles richtig verstanden habe, dann hat ein landkreis immer eine eindeute PLZ, die in tabelle a steht ?

Ilja

[Kristin]

Hi!

das erinnert mich alles an die zeit, als ich in einem rathaus in berlin für die stadtplaner gearbeitet hatte, da waren die daten auch so herlich im "beamten-stil" aufbereitet.

am Tabellen Layout kann ich leider nicht viel ändern, da ich das soweit übernommen habe. Das Design ist historisch seit 5 Jahren gewachsen...

die Daten kommen von der Post, dem statitischen Bundesamt, an deren Behörden, etc. daraus eine Einheit machen ist manchmal ein Kunststück :-)

nun aber wieder zu dir, weg von meinen alten erinnerungen. grundsätzlich ist dieser join problematisch, mal abgeshen davon, dass deine tabelle b nicht normalisiert ist (atomar). wenn ich alles richtig verstanden habe, dann hat ein landkreis immer eine eindeute PLZ, die in tabelle a steht ?

normalerweise kan eine PLZ zu mehreren Landkreisen gehören.
Das kommt aber bei unserer Anwendung nicht vor, da wir das verhindern bzw. korrigieren.

die Tabelle b sieht wie folgt aus
plz, gkz

eine plz kann zu mehreren gkzs gehören, eine gkz kann zu mehreren plz gehören.
Wir haben es aber so vereinbart, dass eine plz immer in ein Landkreis (erste 5 stellen) liegen muss.

Das die Tabelle b und andere nicht normalisiert sind, will ich nicht abstreiten. Einige sind/wurden nicht normalisiert, da sonst die SQL Abfragen zu lange dauern. Einige dauern schon unnormalisiert > 30 Minuten, normalisiert kamen wir manchmal auf > 2 Tage ;-)

Die Datenbank ist knapp 60GB gross und einige Abfragen gehen mit unterschiedlichsten Joins über 25 Tabellen.

soviel zur Theorie ;-)

Wie kann ich nun verhindern, dass durch das substr(...) Datenzeilen doppelt gezählt werden?

Wenn ich das mit der inneren SQL Abfrage mache, wird nichts doppelt gezählt. Gibt es eine saubere Lösung?

[Ilja]

yo,

ganz ehrlich, ich verstehe bisher die fachlichkeit noch nicht. dabei hilft es mir nicht viel, von tabelle a und tabelle b zu hören, richtige namen und vor allem die bedeutung wären da schon besser.

ich will noch mal auf meine erste aussage verweisen, nämlich schon bei den joins die richtige anzahl an datensätze zu bekommen und zusätzliche informationen durch andere tabellen über unterabfragen reinzunehmen. was wir in deinem falle brauchen ist also eine tabelle, wo die landkreise drinne stehen und zwar nur die landkreise. wenn ich das richtig verstanden haben, dann steht in tabelle b die kennung der landkreise in der gkzs mit drinne. aber das kann ja nur eine verknüpfung sein und nicht eine tabelle nur für die lankreise oder ?

tuning und normalisierung sind ofmtlas auch gut unter einem hut zu bekommen. auch eine große datenbank muss nicht langsam sein. es kommt immer auf den jeweiligen fall drauf an. und das was ich meine, mämlich die funstellige zahl in eine extra spalte zu schreiben, darüber dann einen index zu legen, weil er als fremdschlüssel fungiert, das kann sich nur positiv auf die performance auswirken.

Ilja

[Kristin]

Hi

hier ein Kurzschema

zahlen            plzgkz        landkreise
----+--------     ----+----     ----+------
plz | zahlen      plz | gkz     gkz | name

zahlen: plz = 5 Stellig
plzgkz: plz = 5 stellig, gkz 8 stellig
landkreise: gkz = 5 stellig (erste 5 Stellen der gkz)

um die Zahlen für jede plz aufzulisten müßte der SQl Befehl ungefähr so aussehen

SELECT
    z.plz
    SUM(COALESCE(z.zahlen, 0)) AS zahlen,
FROM zahlen z
INNER JOIN plzgkz p ON z.plz = p.plz AND substr(p.gkz, 1, 5) = '09776'
GROUP BY
    z.plz
ORDER BY
    z.plz

in der Tabelle zahlen gibt es nur einen Eintrag mit der plz 17039 und den Wert 100000, in der Tabelle plzgkz gibt es zu der plz 17039 13 unterschiedliche gkz.

plz  | zahlen | gkz      | gkz (erste 5 Stellen)
-------+--------+----------+-------
 17039 | 100000 | 13055079 | 13055
 17039 | 100000 | 13055002 | 13055
 17039 | 100000 | 13055049 | 13055
 17039 | 100000 | 13055081 | 13055
 17039 | 100000 | 13055051 | 13055
 17039 | 100000 | 13055003 | 13055
 17039 | 100000 | 13055065 | 13055
 17039 | 100000 | 13055066 | 13055
 17039 | 100000 | 13055008 | 13055
 17039 | 100000 | 13055069 | 13055
 17039 | 100000 | 13055072 | 13055
 17039 | 100000 | 13055046 | 13055
 17039 | 100000 | 13055076 | 13055

für die plz will ich jetzt die Zahl 100000 haben, ich erhalte jedoch 1300000

Ich muss also irgendwie verhindern, dass er jede gkz Spalte verwendet, wo die 5 ersten Zeichen 13055 sind

Kristin

[Ilja]

yo,

ok, denke jetzt habe ich es besser verstanden, aber das design ist grausam, auch wenn es historisch bedingt ist. man sollte sich überlegen, ob man nicht die zeit und mühe in ein design investiert, also sich mit den reports hinten herum zu schlagen.

wie auch immer, die basis deiner abfrage ist die tabelle landkreise, damit bekommst du schon mal die richtige anzahl von datensätze. dort baust du eine "quasi korrelierte unterabfrage" ein. quasi deswegen, weil ihr nicht wirklich richtige fremdschlüssel habt, weswegen du auch öfters mehrere treffer bekommst. da es sich um eine unterabfrage handelt, wir also nur einen wert zurück haben dürfen/wollen, nehmen wir halt einen der sowieso immer gleichen werte mit dem trick von MIN oder MAX.

SELECT l.kgz, l.name, (SELECT MIN(z.zahlen)
                       FROM plzgkz p
                       INNER JOIN zahlen z ON z.plz = substr(p.gkz, 1, 5)
                       WHERE p.gkz = l.gkz
                      )
FROM landkreis l
;

Ilja

[Kristin]

Hi

SELECT l.kgz, l.name, (SELECT MIN(z.zahlen)
                       FROM plzgkz p
                       INNER JOIN zahlen z ON z.plz = substr(p.gkz, 1, 5)
                       WHERE p.gkz = l.gkz
                      )
FROM landkreis l
;

ähm...

ich kann jetzt falsch liegen, aber so kann das doch nicht klappen, oder?
p.pkz ist eine 8 stellige Zahl l.gkz eine 5 stellige Zahl
Der Vergleich kann, meines Wissens, nur substr(p.gkz, 1, 5) = l.gkz so erfolgen, dann habe ich aber wieder das Problem, dass die Daten doppelt gezählt werden. :-\

ich brauche folgendes Format

landkreis, zahlen (zahlen sind mehrere Spalten mit Zahlen, der einfachhalber hier auf eine Spalte reduziert)

er muss intern alle Zahlen pro Landkreis aufaddieren, er darf eine PLZ aber nur einmal verwenden (in meinen Fall werden jedoch einige doppelt gezählt)

Wenn es nicht verständlich sein sollte, versuche ich morgen einige Testdaten zur Verfügung zu stellen.

Kristin

[Ilja]

yo,

ich kann jetzt falsch liegen, aber so kann das doch nicht klappen, oder?

habe nur die tabellen verwechselt, also joins richtig setzen und es geht.

SELECT l.kgz, l.name, (SELECT MIN(z.zahlen)
                        FROM plzgkz p
                        INNER JOIN zahlen z ON z.plz = p.plz1
                        WHERE substr(p.gkz, 1,5) = l.gkz
                       )
FROM landkreis l
;

[Kristin]

Hi Ilja,

ich kann jetzt falsch liegen, aber so kann das doch nicht klappen, oder?

habe nur die tabellen verwechselt, also joins richtig setzen und es geht.

SELECT l.kgz, l.name, (SELECT MIN(z.zahlen)
                        FROM plzgkz p
                        INNER JOIN zahlen z ON z.plz = p.plz1
                        WHERE substr(p.gkz, 1,5) = l.gkz
                       )
FROM landkreis l
;

hinter from landkreis l habe ich noch
WHERE l.gkz in (...) rangehängt

die Zahlen von jeder PLZ werden, wenn ich das richtig sehe auch nicht pro Landkreis summiert, sondern nur der minimale Wert verwendet.
Auch wenn ich den maximalen Wert verwende, stimmt das Ergebnis nicht, da nicht pro Landkreis addiert wird.

Warum benötigt diese Variante der Abfrage so lange?
Die Variante von mir, wo noch mehr INNER JOINs verwendet werden, benötigt ca. 6500 ms.
Sollte es nicht bei jeden weiteren JOIN langsamer werden?

Kristin

[Kristin]

Hi Ilja,

Warum benötigt diese Variante der Abfrage so lange?
Die Variante von mir, wo noch mehr INNER JOINs verwendet werden, benötigt ca. 6500 ms.
Sollte es nicht bei jeden weiteren JOIN langsamer werden?

dieser Absatz ist hinfällig, da deine Variante etwas schneller ist, nachdem ich die where Klausel hinzugefügt hatte

Kristin

[Ilja]

yo,

die Zahlen von jeder PLZ werden, wenn ich das richtig sehe auch nicht pro Landkreis summiert, sondern nur der minimale Wert verwendet.
Auch wenn ich den maximalen Wert verwende, stimmt das Ergebnis nicht, da nicht pro Landkreis addiert wird.

in deinen beispieldaten war mir nicht bewusst, das eine landkreis gkz auch mehrere plz haben kann. aber ich denke du hast das weiter oben mal erwähnt. jetzt wird mir auch klarer, was du mit den join haben willst:

auf einen landkreis alle verschiedenen plz nur einmal, hoffe so ist es jetzt richtig.

der grundaufbau ist letztlich der gleiche, du nimmst als basis die landkreise. die unterabfrage wird ein wenig verändert, es braucht halt zwei unterabfragen dafür. ich gehe davon aus, plz in der tabelle zahlen ist Unique.

SELECT l.kgz, l.name, (SELECT SUM(z.zahlen)
                       FROM zahlen z
                       WHERE z.plz IN (SELECT p.plz
                                        FROM plzgkz p
                                        WHERE substr(p.gkz, 1,5) = l.gkz
                                       )
                        ) Summe
FROM landkreis l
;

[Kristin]

Hi

in deinen beispieldaten war mir nicht bewusst, das eine landkreis gkz auch mehrere plz haben kann. aber ich denke du hast das weiter oben mal erwähnt. jetzt wird mir auch klarer, was du mit den join haben willst:

auf einen landkreis alle verschiedenen plz nur einmal, hoffe so ist es jetzt richtig.

der grundaufbau ist letztlich der gleiche, du nimmst als basis die landkreise. die unterabfrage wird ein wenig verändert, es braucht halt zwei unterabfragen dafür. ich gehe davon aus, plz in der tabelle zahlen ist Unique.

SELECT l.kgz, l.name, (SELECT SUM(z.zahlen)
                       FROM zahlen z
                       WHERE z.plz IN (SELECT p.plz
                                        FROM plzgkz p
                                        WHERE substr(p.gkz, 1,5) = l.gkz
                                       )
                        ) Summe
FROM landkreis l
;

ERROR: subquery must return only one column

danke

wie kann ich im SUBSELECT mehrere Spalten abfragen z.B.
SELECT SUM(z.SPALTE1), SUM(z.SPALTE2), SUM(z.SPALTE3), SUM(z.SPALTEx)

Kristin

[Ilja]

yo,

ERROR: subquery must return only one column

die fehlermeldung kam doch aber nicht bei der query, ich vermute, du hast noch spalten hinzugefügt wie es weiter unten steht ?

wie kann ich im SUBSELECT mehrere Spalten abfragen z.B.
SELECT SUM(z.SPALTE1), SUM(z.SPALTE2), SUM(z.SPALTE3), SUM(z.SPALTEx)

wenn man mehrere spalten zurück geben will, dann hat man unter anderem zwei verschiedene ansätze:

1. entweder du machst auch mehrere unterabfragen, was ich persönlich gerne mache.

2. oder aber du musst eben die joins benutzen, was wiederrum den nachteil haben kann, dass du mehr datensätze bekommst, als du willst und darauf dann achten musst.

du musst dich aber für einen weg entscheiden....

Ilja

[Kristin]

Hi

1. entweder du machst auch mehrere unterabfragen, was ich persönlich gerne mache.

das müßte doch langsamer und datenbankintensiver sein, oder?

Kristin

[Ilja]

yo,

das müßte doch langsamer und datenbankintensiver sein, oder?

meine erfahrung ist die, der ruf von unterabfragen ist deutlich schlechter als die wirklichkeit. sicherlich kippt es irgendwann. aber solange die ausführungszeit noch im rahmen des möglichen ist, find ich unterabfragen besser zu lesen und weniger fehleranfällig.

Ilja

[Kristin]

Hi

das müßte doch langsamer und datenbankintensiver sein, oder?

meine erfahrung ist die, der ruf von unterabfragen ist deutlich schlechter als die wirklichkeit. sicherlich kippt es irgendwann. aber solange die ausführungszeit noch im rahmen des möglichen ist, find ich unterabfragen besser zu lesen und weniger fehleranfällig.

ich ziehe lieber die Joins den x-Unterabfragen vor.

evtl. kann man für die abzufragenden Postleitzahlen eine Unterabfrage machen

Kristin