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.