Vergleich mit Haushalt
Mit dem letzten SQL-Befehl hast du schon die Nährwerte aller Käufe einer Karte in einem Monat ausgewertet und damit den linken Teil des Datenbankschemas miteinander verbunden:
Der folgende SQL-Befehl wertet nun die rechte Seite aus. Kannst du die einzelnen Teile erklären - besonders: Was wird mit 9000*30 wohl berechnet?
SELECT h.HNR, COUNT(*) AS "Anzahl Personen",
COUNT(*)*9000*30 "Naehrwert erwartet"
FROM m_haushalt h, m_person p
WHERE h.HNR = p.HNR
GROUP BY h.HNR
oder noch einfacher über den View, den wir im Kapitel 1 erzeugt haben:
SELECT h.HNR, h.AnzPers,
h.AnzPers*9000*30 "Naehrwert erwartet"
FROM v_haushaltperson h
Verbinden der Daten
Jetzt ist es eigentlich nur noch ein kleiner Schritt, den erwarteten Nährwert eines Haushalts mit den Kaufdaten
der Karten in einem Monat zu vergleichen. Dazu ist nun eine Verbindung der Personen eines Haushalts zu ihren Kartendaten
notwendig - das haben wir aber schon geschafft mit der Tabelle oder dem View znrpnr
.
Der folgende recht komplexe SQL-Befehl leistet diesen Vergleich - probiere es aus!
SELECT h.HNR, h.AnzPers,
h.AnzPers*9000*30 "erwartet",
SUM(a.Naehrwert) "Summe",
SUM(a.Naehrwert) - h.AnzPers*9000*30 "diff",
(SUM(a.Naehrwert) - h.AnzPers*9000*30)/SUM(a.Naehrwert)*100 "diff Proz."
FROM z_kauf k, z_belegzeile b, z_artikel a, v_znrpnr z, v_haushaltperson h, m_person p
WHERE k.KAUFNR = b.KAUFNR
AND k.ZNR = z.ZNR
AND z.PNR = p.PNR
AND p.HNR = h.HNR
AND b.EAN = a.EAN
AND k.Jahr = 2021
AND k.Monat = 4
GROUP BY h.HNR
ORDER BY diff DESC
Viele Zeilen des Befehls kennst du eigentlich schon - versuche, den Rest nachzuvollziehen mit folgenden Hilfen:
- Die Zeilen [1] und [2] kennst du von oben - der erwartete Nährwert eines Haushalts in einem Monat.
- Zeile [3] berechnet zunächst wie im letzten Kapitel die Summe der Nährwerte aller Einkäufe - hier aber nicht nur einer Karte, sondern aller Karten der Bewohner des jeweiligen Haushalts. Dazu dienen die Joins in Zeile [9] (Karte an Person) und [10] (Person an Haushalt).
- Die Zeilen [4] und [5] sind nur "Kosmetik" - hier wird zur Verdeutlichung die Differenz zwischen erwartetem und tatsächlichen Nährwert als absolute Zahl und in Prozent berechnet.
Schlussfolgerung
Es ist jetzt schon recht klar, dass wohl in den Haushalten Nr. 7682902, 5645701, 1075001
mehr Personen wohnen, als
im Melderegister angegeben. Suche doch einmal, wer dort gemeldet ist und wo jeweils der Haushalt liegt! Du kannst bestimmt einen SQL-Befehl dazu verwenden...
HNR IN (7682902, 5645701, 1075001)
.
Ein Ort ist "Prinsengracht 263, Amsterdam". Vielleicht sagt dir der Ort schon etwas? Wenn nicht, dann suche doch danach und nach der Geschichte, die dahinter steckt.
Wenn ihr das im Kurs getan habt, versucht einmal zusammen zu bewerten, welche Nutzen und Gefahren aus dem Verbinden von verschiedenen Datenquellen erwachsen können.
Aufgabe - für Experten
Finde heraus, ab wann der jeweilige Haushalt wahrscheinlich schon mehr Personen beherbergt als angegeben.