"Just in Time", also typisch für mich, gibts hier die Dokumentation zur 2. Hausübung in IV2. Dieses Mal sollte es ja nicht so leicht werden und bereits am Ende der letzten Woche, als die Aufgabe ins Netz gestellt wurde, hab ich mir die Angabe durchgelesen um mir einen ersten Überblick zu verschaffen ohne andererseits schon einen Lösungsversuch zu starten.

Aufgabe war die schrittweise Entwicklung einer komplexen Abfrage mit betriebswirtschaftlichen Sinn. Die Basisabfrage wurde vorgegeben und lautete:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Man erhält also eine Kundenliste und wieviel Aufträge jeder Kunde bereits durchgeführt hat.

In einem ersten Schritt soll die Abfrage um den Gesamtumsatz pro Kunde erweitert werden. Man würde also zuerst folgende Abfrage versuchen:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr), SUM(Auftragspos.Menge*Buch.Preis)
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Vergleicht man aber jetzt die Gesamtanzahl der Aufträge je Kunde, ergeben sich Differenzen. Jetzt sind mehr Aufträge pro Kunde angegeben, da Aufträge jetzt wohl mit der Anzahl der jeweiligen Bücherliste des Auftrages einfließen.

Somit darf jeder Auftrag trotzdem nur einmal gezählt werden. Dies erreicht man mit der DISTINCT-Klausel bei der COUNT-Funktion. (Anm.: auf die Lösung kamen ein Kollege und ich beim Diskutieren der Hausübung)

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr), SUM(Auftragspos.Menge*Buch.Preis)
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Oben sieht man also die Lösung des Problems des ersten Teils.


Im zweiten Teil sollte ermittelt werden, wann denn nun überhaupt Aufträge der verschiedenen Kunden eingetroffen sind. Deswegen sehen wir uns zuerst an, wann unser erster Auftrag und wann unser letzter Auftrag eingegangen ist. (Anm.: Hier gab es Hilfestellungen im Kurs am Mittwoch, den 09.04.08)

Wir stellen also zwei kleine Abfragen:

SELECT MIN(Auftrag.Datum)
FROM Auftrag

SELECT MAX(Auftrag.Datum)
FROM Auftrag

Wir stellen fest: Aufträge gingen vom 01.03.2000 bis 19.01.2001 ein!

Für mich stellte sich auch die Frage, ob es sich bei der Anzahl der Aufträge im Jahr 2001 überhaupt noch um wesentliche Mengen handelte, weswegen ich die Anzahl der Aufträge 2000 und 2001 mit folgenden kleinen Abfragen bestimmte:

SELECT COUNT(Auftrag.Nr)
FROM Auftrag
WHERE Auftrag.Datum >= '2001-01-01'

... was nur 24 Aufträge vom 01.01.2001 bis zum 19.01.2001 ergab (betriebswirtschaftlich noch kein Vergleich möglich)

SELECT COUNT(Auftrag.Nr)
FROM Auftrag
WHERE Auftrag.Datum < '01.01.2001'

Hier waren 975 Aufträge verzeichnet.

Alternativ könnte man hier auch die in der Lehrveranstaltung bezeichnete YEAR-Funktion anwenden, wobei man die Tabelle dann eben noch zusätzlich auswerten muss:

SELECT Kunde.Nr, Kunde.Nachname, YEAR (Datum)
FROM Auftrag, Kunde
WHERE Auftrag.Kundennummer = Kunde.Nr
ORDER BY 3 DESC


Kommen wir also zum 3. Teil der Hausübung:

Die Ergebnisse des ersten Teils sollte nun in geeignete sinnvolle Zeitabschnitte eingeteilt werden, um sie dann vergleichen zu können.

Ich wählte hier Quartale, wobei ich nur die 4 Quartale von 2000 miteinander vergleichen wollte, da das halbe Monat in 2001 für mich als unwesentlich ausschied. Ich erweiterte also lediglich die in der Teilaufgabe 1 entwickelte Abfrage um eine Bedingung:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS 'Anzahl Aufträge', SUM(Auftragspos.Menge*Buch.Preis) AS Gesamtumsatz
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
AND Auftrag.Datum BETWEEN '2000-01-01' AND '2000-03-31'
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Dies stellt die Gesamtaufzahl der Aufträge pro Kunde mit dem jeweiligen Gesamtumsatz im ersten Quartal dar. Indem man jetzt die Bedingung ändert, erhält man vergleichbare Tabellen der anderen 3 Quartale.


Im vierten und letzten Teil der Hausübung soll nun irgendwie ein Filter bezüglich des Durchschnittes der eingegangenen Aufträge pro Kunde mit einbezogen werden. Überlegt habe ich mir folgendes Szenario:

Der Buchhandel will nachträglich seinen "fleißig" bestellenden Kunden eine Gutschrift als nachträglicher Rabatt zukommen lassen, damit sie auch "fleißige" Kunden bleiben ;). Alle, die überdurchschnittlich viele Aufträge durchgeführt haben sollen 1,5 % ihres Umsatzes/Quartal als Gutschrift erhalten. (Anm.: sinnvoller wäre eventuell eher die Bindung an den Gesamtumsatz!)

Ich brauche also eine weitere Bedingung, die die AVG-Funktion enthält und ich werde das Feld des Gesamtumsatzes gegen ein Feld "Gutschrift" tauschen. Ich schaffte es allerdings nicht, das in eine funktionierende einzelne Abfrage zu packen und so machte ich zwei Abfragen, um den Durchschnitt pro Quartal an Auftragseingängen/Kunde zu ermitteln und dann diese als absolute Zahl in der Bedingung der Hauptabfrage einzufügen.

Die erste Abfrage stellt sich also wie folgt dar:

SELECT COUNT(a.Nr)/COUNT(DISTINCT k.Nr) AS 'Durchschnittliche Aufträge pro Kunde'
FROM Kunde=k, Auftrag=a
WHERE k.Nr=a.Kundennummer
AND a.Datum BETWEEN '2000-01-01' AND '2000-03-31'

Dabei ergab sich eine durchschnittliche Auftragsanzahl/Kunde von 1,3978. Die Gutschrift sollten also alle erhalten, die mehr als einen Auftrag hatten. Das führte mich dann zur finalen Abfrage:


SELECT k.Nr, k.Nachname, COUNT(DISTINCT a.Nr) AS "Anzahl Aufträge", SUM(ap.Menge*b.Preis*0.015) AS Gutschrift
FROM Kunde=k, Auftrag=a, Auftragspos=ap, Buch=b
WHERE k.Nr = a.Kundennummer
AND a.Nr=ap.Auftragsnummer
AND b.Nr=ap.Buchnummer
AND a.Datum BETWEEN '2000-01-01' AND '2000-03-31'
GROUP BY 1
HAVING COUNT(DISTINCT a.Nr) > 1
ORDER BY 1

Bin nun wirklich JIT fertig um 23:59!!