Summierung

[Ralph Staudte]

Hallo beisammen,

ich habe ein ähnliches Problem wie im Thread "Summierung von Zeilen in Spalten mit self-join" beschrieben. Leider kann ich Gesines Lösung nicht so recht nachvollziehen und bitte in dieser Sache nochmals um Hilfe.

Ich habe (vereinfacht) folgende Daten:
<PRE>
Key Datum Ma-Nr Menge Typ
===================================

1 01.01.2002 1000 4.25 1
2 01.01.2002 1000 3.50 3
3 01.01.2002 1000 4.00 3
4 31.01.2002 1000 8.00 2
</PRE>

und benötige folgende Auswertung (Stunden-Übersicht pro Mitarbeiter):
<PRE>
Datum Typ1 Typ2 Typ3 .......

01.01.2002 4.25 7.50
31.01.2002 8.00
</PRE>

Es soll also (gruppiert für die einzelnen Arbeitstage) jeweils die Summe für die einzelnen Typen ausgegeben werden.

Ich hoffe, das war einigermaßen verständlich. Für Rückfragen stehe ich natürlich jederzeit gerne zur Verfügung.

Ach ja: Das Ganze soll mit Delphi4 C/S unter PostgreSQL laufen.

Danke für jede Hilfe

Ralph

[Gesine Kuhlmann]

Hi,

Wo drückt denn der Schuh ??

Gruß
Gesin

[Ralph Staudte]

Gesine,

da ich in SQL (gelinde ausgedrückt) noch ganz am Anfang stehe, habe ich leider überhaupt keinen Schimmer, wie ich diese Auswertung zu programmieren habe.

Ich bin allerdings zu allen Schandtaten bereit - Stored Procedures, Views und was weiß ich noch nicht alles - wenn ich nür wüsste, wie.

Im Thread "Summierung von Zeilen in Spalten mit self-join" hast Du ja bereits eine Lösung vorgeschlagen - das Problem dort war fast identisch. Nur verstehe ich nicht, was Du da gezaubert hast ;-)

Ob Du evtl. zu einer kleinen Erklärung bereit wärst?

Danke

Ralp

[Gesine Kuhlmann]

1) Gegeben sei Tabelle ARTICLES, Damit wir wissen worüber wir überhaupt sprechen, die Definition, deiner auszuwertenden Tabelle

<pre>
CREATE TABLE "STUNDENZETTEL"
(
"PKEY" "PRIMARYID",
"DATUM" INTEGER,
"MANR" INTEGER,
"MENGE" INTEGER,
"TYP" INTEGER,
CONSTRAINT "STUNDENZETTEL" PRIMARY KEY ("PKEY")
);

</pre>
2) View erzeugen, der alle vorhandenen Tage listet. Ein View ist eine Sichtweise auf eine physikalische Tabelle, verhält sich im Prinzip aber so, also ob es eine echte Tabelle wäre. Man kann damit festlegen, welche Spalten und welche Zeilen zurückgegeben werden. In deinem Fall wird eine einspaltige 'Tabelle' geliefert, die jedes vorhandene Datum in deiner echten Tabelle Stundenzettel <b>genau einmal</b> enthält. Im Prinzip das gleiche, was der SQL-Schlüsselausdruck <i>distinct</i> erledigen würde. Diese Option ist aber nicht überall verfügbar.

<pre>
CREATE VIEW "TAGESLISTE" (
"TAGE"
) AS select DATUM from STUNDENZETTEL group by DATUM
;
//
//
// In deinem Beispiel würde folgendes geliefert:
//
// TAGE
// ==========
// 01.01.2002
// 31.01.2002

</pre>

3) View erzeugen, der die Summen bildet. Du erzeugst dir jetzt eine Sichtweise auf deine Tabelle, die im Prinzip schon dein Ergebnis ist. Sie enthält halt drei Spalten.

<pre>
CREATE VIEW "MENGEN" (
"VDATUM",
"VTYP",
"VMENGE"
) AS select DATUM, TYP, SUM(MENGE) from STUNDENZETTEL group by DATUM, TYP
;
//
//
// Dein Beispiel liefert:
//
// VDATUM VTYP VMENGE
// ===============================
// 01.01.2002 1 4.25
// 01.01.2002 3 7.50
// 31.01.2002 2 8.00

</pre>
4) Fürs Resultat, dann folgenden Join. Möchtest Du nun der Übersichtlichkeit wegen, dass jeder Typ der in deiner Tabelle auftaucht in einer eigenen Spalte steht, dann benötigst eben folgendes:

<pre>
select TAGE, M1.VMENGE AS TYP1, M2.VMENGE As TYP2, M3.VMENGE As TYP3 from TAGESLISTE
left outer join MENGEN M1 on TAGE=VDATUM and VTYP=1
left outer join MENGEN M2 on TAGE=VDATUM and VTYP=2
left outer join MENGEN M3 on TAGE=VDATUM and VTYP=3
//
//
// Dein Beispiel liefert:
//
// TAGE TYP1 TYP2 TYP3
// =======================================
// 01.01.2002 4.25 NIL 7.50
// 31.01.2002 NIL 8.00 NIL

</pre>
Wobei M1, M2, M3 nur Aliasbezeichungen für den View Mengen sind, <i>M1.VMENGE AS TYP1</i> liefert das Feld MENGEN.VMENGE, die Spalte in der resultierenden Datenmenge heißt aber TYP1.

mit den joins 'klebst du' praktisch die Mengenfelder an die Basistabelle/-view ( hier TAGESLISTE ) dran. Die on-Angabe legt fest, wann welches Feld angeklebt werden soll. Es handelt sich um einen OUTER join, da es ja vokommen kann, das ein anzuklebendes Mengenfeld nicht vorhanden ist ( Du hast in deinem Beispiel z.B. keine Daten am 31.01.2002 des Typs 1 und 3 ) . In diesem Fall 'simuliert' die DB diese eben und liefert stattdessen einen leeres, imaginäres Feld.

5)
Die SQL-Formulierungen sollten eigentlich so 1:1 auch unter Postgre SQL funktionieren. Ein Blick ins Handbuch, ob die Syntax tatsächlich passt, kann aber nicht schaden ;-)


Gruß Gesin

[Ralph Staudte]

Gesine,

zunächst herzlichsten Dank für Deine Mühe!!

Ich habe Deine Lösung ausprobiert und im Prinzip funktioniert es. Ein Problem habe ich aber leider noch:

Da in der Tabelle "Stundenzettel" (in Wirklichkeit heißt sie "propos" für "Projekt-Positionen") Einträge für alle Mitarbeiter hinterlegt sind, die Auswertung aber für jeden Mitarbeiter einzeln erfolgen muss, weiß ich nicht, ob und wie ich das in Deinem View "Mengen" (bei mir als "Lohnmengen" bezeichnet) umsetzen muss.

Derzeit sieht es bei mir wie folgt aus:

<PRE>
CREATE VIEW lohnmengen
(VDatum, VPersonal, VLohntyp, VMenge)
AS
SELECT lieferdatum, artikelnr, lohntyp, SUM(menge)
FROM propos
WHERE typ = 9
GROUP BY lieferdatum, artikelnr, lohntyp;
</PRE>

und

<PRE>
SELECT DISTINCT
lieferdatum,
m1.vmenge AS typ1,
m2.vmenge AS typ2,
m3.vmenge AS typ3,
m4.vmenge AS typ4,
m5.vmenge AS typ8,
m6.vmenge AS typ9,
m7.vmenge AS typ10,
m8.vmenge AS typ11,
m9.vmenge AS typ12,
m10.vmenge AS typ13,
m11.vmenge AS typ20,
m12.vmenge AS typ21,
m13.vmenge AS typ22,
m14.vmenge AS typ23,
m15.vmenge AS typ24
FROM propos
LEFT OUTER JOIN lohnmengen m1 ON lieferdatum=m1.vdatum AND m1.vlohntyp=1
LEFT OUTER JOIN lohnmengen m2 ON lieferdatum=m2.vdatum AND m2.vlohntyp=2
LEFT OUTER JOIN lohnmengen m3 ON lieferdatum=m3.vdatum AND m3.vlohntyp=3
LEFT OUTER JOIN lohnmengen m4 ON lieferdatum=m4.vdatum AND m4.vlohntyp=4
LEFT OUTER JOIN lohnmengen m5 ON lieferdatum=m5.vdatum AND m5.vlohntyp=8
LEFT OUTER JOIN lohnmengen m6 ON lieferdatum=m6.vdatum AND m6.vlohntyp=9
LEFT OUTER JOIN lohnmengen m7 ON lieferdatum=m7.vdatum AND m7.vlohntyp=10
LEFT OUTER JOIN lohnmengen m8 ON lieferdatum=m8.vdatum AND m8.vlohntyp=11
LEFT OUTER JOIN lohnmengen m9 ON lieferdatum=m9.vdatum AND m9.vlohntyp=12
LEFT OUTER JOIN lohnmengen m10 ON lieferdatum=m10.vdatum AND m10.vlohntyp=13
LEFT OUTER JOIN lohnmengen m11 ON lieferdatum=m11.vdatum AND m11.vlohntyp=20
LEFT OUTER JOIN lohnmengen m12 ON lieferdatum=m12.vdatum AND m12.vlohntyp=21
LEFT OUTER JOIN lohnmengen m13 ON lieferdatum=m13.vdatum AND m13.vlohntyp=22
LEFT OUTER JOIN lohnmengen m14 ON lieferdatum=m14.vdatum AND m14.vlohntyp=23
LEFT OUTER JOIN lohnmengen m15 ON lieferdatum=m15.vdatum AND m15.vlohntyp=24
WHERE typ=9
AND artikelnr = :PersNum
AND EXTRACT(MONTH FROM lieferdatum) = :Monat
AND EXTRACT(YEAR FROM lieferdatum) = :Jahr
ORDER BY lieferdatum
</PRE>

Zur Erklärung:

Ich habe versucht, auf den von Dir vorgeschlagenen View "Tagesliste" zu verzichten, weil PostgreSQL DISTINCT kennt. Hoffentlich habe ich es richtig gemacht.

"Typ = 9" ist der Schlüssel für Arbeitszeit (in der Tabelle propos stehen auch noch andere Positionstypen). Nicht zu verwechseln mit dem Wert "Lohntyp".

"Lohntyp" bezeichnet die Lohnart und somit die Spalten in der Auswertung.

Die Personalnummer ist im Feld "Artikelnr" hinterlegt (das hat historische Gründe).

Das "Lieferdatum" ist das Datumsfeld, nach dem selektiert werden soll.

Die Auswertung läuft ohne Fehlermeldung durch, ich bekomme jedoch neben den korrekten Werten auch noch "Phantom-Werte" angezeigt. Das schiebe ich im Moment mal auf die evtl. unrichtige DISTINCT-Anweisung bzw. auf den View "Lohnmengen".

Hast Du eine Idee, woher die "Phantom-Ergebisse" in der Auswertung kommen könnten und wie ich das abstellen kann?

Schon jetzt nochmals herzlichen Dank!!

Grüße
Ralp

[Gesine Kuhlmann]

Hi Ralph,

1)
Dein View Lohnmengen ist so korrekt.

2)
Deine Phantom-Daten liegen am 'falschen' Einsatz von distinct und dem fehlenden View TAGESLISTE. Distinct bezieht sich nämlich immer auf alle Felder eines Satzes. D.h. alle Felder zweier Datensätze müssen identisch sein, damit Distinct Wirkung zeigt.

3)
Ich meinte mit dem Hinweis nicht, dass Du auf TAGESLISTE verzichten kannst. Ich meinte damit nur der View könnte statt der <i>group by</i> Klausel <i>Select distinct</i> benutzen s.u.;

<pre>
CREATE VIEW "TAGESLISTE" (
"LIEFERDATUM",
"MA_NR"
) AS select distinct LIEFERDATUM, ARTIKELNR from PROPOS
</pre>


4)
Mit folgenden Änderungen und dem View TAGESLISTE sollte deine Auswertung korrekt funktionieren ( TL.MA_NR muss nicht unbedingt in der Liste auftauchen, deshalb habe ich den Feldbezeichner in der Select-Anweisung in eckige Klammern gesetzt.) :

<pre>
SELECT
TL.lieferdatum, [TL.MA_NR]
m1.vmenge AS typ1,
m2.vmenge AS typ2,
m3.vmenge AS typ3,
m4.vmenge AS typ4,
m5.vmenge AS typ8,
m6.vmenge AS typ9,
m7.vmenge AS typ10,
m8.vmenge AS typ11,
m9.vmenge AS typ12,
m10.vmenge AS typ13,
m11.vmenge AS typ20,
m12.vmenge AS typ21,
m13.vmenge AS typ22,
m14.vmenge AS typ23,
m15.vmenge AS typ24
FROM TAGESLISTE TL
LEFT OUTER JOIN lohnmengen m1 ON TL.lieferdatum=m1.vdatum AND TL.MA_NR = m1.Artikelnr AND m1.vlohntyp=1
LEFT OUTER JOIN lohnmengen m2 ON TL.lieferdatum=m2.vdatum AND TL.MA_NR = m2.Artikelnr AND m2.vlohntyp=2
LEFT OUTER JOIN lohnmengen m3 ON TL.lieferdatum=m3.vdatum AND TL.MA_NR = m3.Artikelnr AND m3.vlohntyp=3
LEFT OUTER JOIN lohnmengen m4 ON TL.lieferdatum=m4.vdatum AND TL.MA_NR = m4.Artikelnr AND m4.vlohntyp=4
LEFT OUTER JOIN lohnmengen m5 ON TL.lieferdatum=m5.vdatum AND TL.MA_NR = m8.Artikelnr AND m5.vlohntyp=8
LEFT OUTER JOIN lohnmengen m6 ON TL.lieferdatum=m6.vdatum AND TL.MA_NR = m9.Artikelnr AND m6.vlohntyp=9
LEFT OUTER JOIN lohnmengen m7 ON TL.lieferdatum=m7.vdatum AND TL.MA_NR = m10.Artikelnr AND m7.vlohntyp=10
LEFT OUTER JOIN lohnmengen m8 ON TL.lieferdatum=m8.vdatum AND TL.MA_NR = m11.Artikelnr AND m8.vlohntyp=11
LEFT OUTER JOIN lohnmengen m9 ON TL.lieferdatum=m9.vdatum AND TL.MA_NR = m12.Artikelnr AND m9.vlohntyp=12
LEFT OUTER JOIN lohnmengen m10 ON TL.lieferdatum=m10.vdatum AND TL.MA_NR = m13.Artikelnr AND m10.vlohntyp=13
LEFT OUTER JOIN lohnmengen m11 ON TL.lieferdatum=m11.vdatum AND TL.MA_NR = m20.Artikelnr AND m11.vlohntyp=20
LEFT OUTER JOIN lohnmengen m12 ON TL.lieferdatum=m12.vdatum AND TL.MA_NR = m21.Artikelnr AND m12.vlohntyp=21
LEFT OUTER JOIN lohnmengen m13 ON TL.lieferdatum=m13.vdatum AND TL.MA_NR = m22.Artikelnr AND m13.vlohntyp=22
LEFT OUTER JOIN lohnmengen m14 ON TL.lieferdatum=m14.vdatum AND TL.MA_NR = m23.Artikelnr AND m14.vlohntyp=23
LEFT OUTER JOIN lohnmengen m15 ON TL.lieferdatum=m15.vdatum AND TL.MA_NR = m24.Artikelnr AND m15.vlohntyp=24
WHERE typ=9
AND TL.MA_NR = :PersNum
AND EXTRACT(MONTH FROM lieferdatum) = :Monat
AND EXTRACT(YEAR FROM lieferdatum) = :Jahr
ORDER BY TL.lieferdatum
</pre>

5)
OUTER JOINS sind für Ihre Lahmarschigkeit berühmt berüchtigt. Bring also 'nen schnellen Server und viel Zeit mit ;-)

Gruß
Gesin

[Ralph Staudte]

Gesine,

suuuper!!!

Es funktioniert (so weit ich das mit einigen Stichproben jetzt schon feststellen kann) einwandfrei. Vielen Dank!

Eines kommt allerdings noch auf uns zu: Ist es möglich, die Summen auch noch per SQL zu bestimmen? Zum einen benötige ich eigentlich noch die "Tagesarbeitszeit", also die Gesamtmenge aller Lohntypen pro Tag und die "Monatsarbeitszeit", also die Summe aller "Tagesarbeitszeiten".

Und wieder habe ich eigentlich keine Ahnung, wie das gehen könnte...

Wenn Du aber nicht mehr magst, gönne ich Dir - wie großzügig von mir ;-) eine Pause: Die Summe muss nicht in den nächsten Tagen verfügbar sein. (Aber schön wäre es schon...)

Grüße aus Stuttgart

Ralp

[Gesine Kuhlmann]

Hi Ralph,

Dazu benötigst Du folgende zusätzliche Views, auf die Du dann parallel zugreifst.

<pre>
CREATE VIEW "TAGESARBEITSZEIT" (
"TZDATUM",
"TZMA_NR",
"TZSUM"
) AS select LIEFERDATUMDATUM, ARTIKELNR, SUM(MENGE) from PROPOS group by DATUM, ARTIKELNR
;
</pre>

<pre>
CREATE VIEW "MONATSARBEITSZEIT" (
"MZMONAT",
"MZJAHR",
"MZMA_NR",
"MZSUM"
) AS select EXTRACT( MONAT FROM LIEFERDATUM ), EXTRACT( JAHR FROM LIEFERDATUM ), ARTIKELNR, SUM(MENGE) from PROPOS group by EXTRACT( MONAT FROM LIEFERDATUMDATUM ), EXTRACT( JAHR FROM LIEFERDATUMDATUM ), ARTIKELNR
;
</pre>
Gruß
Gesin

[Ralph Staudte]

Gesine,

vielen herzlichen Dank für Deine Hilfe! Die Auswertung läuft wie gewünscht... und trotz der vielen Outer Joins in aktzeptabler Geschwindigkeit.

Ich hoffe, ich kann Dir irgendwann auch mal so qualifiziert helfen - wenn auch nicht in Sachen SQL, da bist Du unschlagbar!

Grüße

Ralp