modul:m290_guko:learningunits:lu10:theorie:a_einfuehrung

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290_guko:learningunits:lu10:theorie:a_einfuehrung [2025/11/05 18:26] – gkochmodul:m290_guko:learningunits:lu10:theorie:a_einfuehrung [2025/11/06 00:18] (aktuell) – gkoch
Zeile 1: Zeile 1:
-====== LU10: Aggregatfunktionen (MySQL) – mit Beispielen aus «Einbrüche Kanton Zürich» ======+====== LU10: Aggregatfunktionen ======
  
 **Datensatz**: Anzahl Einbrüche nach Gemeinden und Stadtkreisen des Kantons Zürich **Datensatz**: Anzahl Einbrüche nach Gemeinden und Stadtkreisen des Kantons Zürich
Zeile 24: Zeile 24:
 === Wozu braucht man das? === === Wozu braucht man das? ===
  
-  * **Datenanalyse & Reporting** (z.B. Lageberichte für Polizei/Medien) +  * **Datenanalyse & Reporting** – Beispiel (Einbrüche ZH): Wie viele Fälle gab es pro Jahr? 
-  * **Business Intelligence** (Trends, Hotspots je Gemeinde/Jahr) +    * **Nutzen:** Klare Jahreskurven für **Berichte**, **Dashboards** und **Kurzstatements** (z. B. für Polizei/Medien). 
-  * **Produkt-/Web‑Integration** (APIs liefern Rohdaten → Aggregation im Backend/SQL spart App‑Logik) +  * **Business Intelligence (BI)** – Beispiel (Einbrüche ZH): Welche Gemeinden haben im Schnitt höhere Raten? 
-  * **Entscheidungsgrundlagen** (Ressourcen planen, Präventionsmassnahmen priorisieren)+    * **Nutzen:** **Prioritäten** setzen (Präventionskampagnen, Ressourceneinsatz, Fokus-Orte). 
 +  * **Produkt-/Web-Integration** – //APIs liefern Rohdaten, Apps brauchen Kennzahlen// Beispiel (YouTube Top 100): Gesamt-Views je Channel → SUM(view_count) pro Channel; durchschnittliche Videolänge → AVG(duration) pro Channel. 
 +    * **Nutzen:** Das **Backend** liefert fertige Kennzahlen; die **Frontend-Logik** bleibt schlank und schnell.
  
  
Zeile 47: Zeile 49:
 </WRAP> </WRAP>
  
-==== 1.2 Einstiegsbeispiele (ohne GROUP BY) ====+==== 1.2 Einstiegsbeispiele ====
  
 === Anzahl unterschiedlicher Gemeinden im Datensatz === === Anzahl unterschiedlicher Gemeinden im Datensatz ===
Zeile 70: Zeile 72:
  
 ===== 2. GROUP BY – Daten zu Gruppen zusammenfassen ===== ===== 2. GROUP BY – Daten zu Gruppen zusammenfassen =====
 +
 +{{ :modul:m290_guko:learningunits:lu10:theorie:group_by-sum.webp?900 |}}
  
 <WRAP round 80% box center> <WRAP round 80% box center>
Zeile 77: Zeile 81:
 </WRAP> </WRAP>
  
-1) Was darf in SELECT stehen?+=== Was darf in SELECT stehen? ===
 Nur Aggregatfunktionen (z.B. SUM(...), AVG(...)) oder Spalten, die im GROUP BY aufgeführt sind. Nur Aggregatfunktionen (z.B. SUM(...), AVG(...)) oder Spalten, die im GROUP BY aufgeführt sind.
  
-Gültig: ''SELECT ausgangsjahr, SUM(straftaten_total) FROM zh_einbrueche.einbrueche GROUP BY ausgangsjahr;''+Gültig: 
 +<code sql> 
 +SELECT ausgangsjahr, SUM(straftaten_total) 
 +FROM zh_einbrueche.einbrueche 
 +GROUP BY ausgangsjahr; 
 +</code>
  
-Ungültig (fehlendes GROUP BY für gemeindename): ''SELECT gemeindename, SUM(straftaten_total) FROM zh_einbrueche.einbrueche; -- führt zu Fehler/Zufallswerten''+Ungültig (fehlendes GROUP BY für gemeindename): 
 +<code sql> 
 +SELECT gemeindename, SUM(straftaten_total) 
 +FROM zh_einbrueche.einbrueche; -- führt zu Fehler/Zufallswerten 
 +</code> 
 + 
 +=== WHERE vs. HAVING === 
 +  * **WHERE** filtert einzelne Zeilen vor dem Gruppieren. 
 +  * **HAVING** filtert fertige Gruppen nach dem Gruppieren.
  
  
-2) WHERE vs. HAVING 
-WHERE filtert einzelne Zeilen vor dem Gruppieren. 
-HAVING filtert fertige Gruppen nach dem Gruppieren. 
 <WRAP round 80% box center> <WRAP round 80% box center>
 Beispiel: Nur Jahre ab 2018 berücksichtigen (WHERE), und nur Gruppen mit Total > 1'000 zeigen (HAVING) Beispiel: Nur Jahre ab 2018 berücksichtigen (WHERE), und nur Gruppen mit Total > 1'000 zeigen (HAVING)
 <code sql> <code sql>
-SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle FROM zh_einbrueche.einbrueche WHERE ausgangsjahr >= 2018 -- Zeilenfilter GROUP BY ausgangsjahr HAVING SUM(straftaten_total) > 1000; -- Gruppenfilter+SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle  
 +FROM zh_einbrueche.einbrueche 
 +WHERE ausgangsjahr >= 2018 -- Zeilenfilter 
 +GROUP BY ausgangsjahr 
 +HAVING SUM(straftaten_total) > 15000; -- Gruppenfilter
 </code> </code>
 +//Ergebnis: In den Jahren 2018, 2019 und 2024 haben über 15000 Einbrüche total stattgefunden.//
 </WRAP> </WRAP>
  
- 
-3) Alias in GROUP BY (MySQL erlaubt es, Standard nicht) 
-MySQL: Alias kann verwendet werden. Für Portabilität (z.B. andere SQL‑Dialekte): Ausdruck wiederholen. 
  
 ==== 2.1 Beispiele: Gruppieren nach einem Kriterium ==== ==== 2.1 Beispiele: Gruppieren nach einem Kriterium ====
  
 <WRAP round 80% box center> <WRAP round 80% box center>
-**a) Total Einbrüche pro Jahr** <code sql>+**Total Einbrüche pro Jahr** <code sql>
 SELECT ausgangsjahr, SELECT ausgangsjahr,
 SUM(straftaten_total) AS total_faelle SUM(straftaten_total) AS total_faelle
-FROM   zh_einbrueche.einbrueche +FROM zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr +GROUP BY ausgangsjahr 
-ORDER  BY ausgangsjahr; </code>+ORDER BY ausgangsjahr; </code>
 **Ergebnis (Auszug)** **Ergebnis (Auszug)**
 ^ ausgangsjahr ^ total_faelle ^ ^ ausgangsjahr ^ total_faelle ^
-| 2019 | 5080 | +| 2009 | 29972 | 
-| 2020 | 4525 | +| 2010 | 26770 | 
-| 2021 | 4789 |+| 2011 | 24394 | 
 +... etc.
 </WRAP> </WRAP>
  
-<WRAP round 80% box center> 
-**b) Anzahl erfasste Gemeinden pro Jahr** 
-<code sql> 
-SELECT ausgangsjahr, 
-COUNT(DISTINCT gemeindename) AS anzahl_gemeinden 
-FROM   zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr 
-ORDER  BY ausgangsjahr; </code> 
-**Ergebnis (Auszug)** 
-^ ausgangsjahr ^ anzahl_gemeinden ^ 
-| 2019 | 171 | 
-| 2020 | 171 | 
-| 2021 | 171 | 
-</WRAP> 
  
 ==== 2.2 Beispiele: Gruppieren nach mehreren Kriterien ==== ==== 2.2 Beispiele: Gruppieren nach mehreren Kriterien ====
Zeile 137: Zeile 140:
 tatbestand, tatbestand,
 SUM(straftaten_total) AS total_faelle SUM(straftaten_total) AS total_faelle
-FROM   zh_einbrueche.einbrueche +FROM zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr, tatbestand +GROUP BY ausgangsjahr, tatbestand 
-ORDER  BY ausgangsjahr, tatbestand; </code>+ORDER BY ausgangsjahr, tatbestand; </code>
 **Ergebnis (Auszug)** **Ergebnis (Auszug)**
 ^ ausgangsjahr ^ tatbestand ^ total_faelle ^ ^ ausgangsjahr ^ tatbestand ^ total_faelle ^
-| 2020 | Einbruch | 3100 | +| 2023 | Einschleichdiebstahl | 1961 | 
-| 2020 | Einschleichen | 1425 | +| 2024 | Einbruchdiebstahl | 5956 | 
-| 2021 | Einbruch | 3250 |+| 2024 | Einbrüche insgesamt | 7998 | 
 +... etc.
 </WRAP> </WRAP>
  
  
-===== 3. HAVING – Gruppen gezielt filtern =====+===== 3. HAVING – Gruppen filtern =====
  
 WHERE kann **keine** Aggregatfunktionen enthalten. Wenn Sie **Ergebnisgruppen** (nach GROUP BY) filtern wollen, verwenden Sie **HAVING**. WHERE kann **keine** Aggregatfunktionen enthalten. Wenn Sie **Ergebnisgruppen** (nach GROUP BY) filtern wollen, verwenden Sie **HAVING**.
  
 <WRAP round 80% box center> <WRAP round 80% box center>
-**Jahre mit über 5'000 Fällen insgesamt** <code sql>+**Jahre mit über 25'000 Fällen insgesamt** <code sql>
 SELECT ausgangsjahr, SELECT ausgangsjahr,
 SUM(straftaten_total) AS total_faelle SUM(straftaten_total) AS total_faelle
-FROM   zh_einbrueche.einbrueche +FROM zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr +GROUP BY ausgangsjahr 
-HAVING SUM(straftaten_total) > 5000 +HAVING SUM(straftaten_total) > 25000 
-ORDER  BY total_faelle DESC; </code>+ORDER BY total_faelle DESC; </code>
 **Ergebnis (Auszug)** **Ergebnis (Auszug)**
 ^ ausgangsjahr ^ total_faelle ^ ^ ausgangsjahr ^ total_faelle ^
-| 2013 | 6240 | +| 2009 | 29972 | 
-| 2012 | 6115 |+| 2012 | 28730 | 
 +| 2010 | 26770 |
 </WRAP> </WRAP>
  
-<WRAP round 80% box center> 
-**Abarbeitungsreihenfolge (vereinfacht)** 
-FROM → WHERE → GROUP BY → **Aggregatfunktionen** → HAVING → SELECT → ORDER BY. 
-</WRAP> 
- 
-===== 4. Häufige Stolpersteine (und wie man sie vermeidet) ===== 
- 
-* SELECT enthält **ungegruppte Spalten** → Fehlermeldung oder Zufallswerte. 
-  → Nur Spalten in GROUP BY **oder** in Aggregaten verwenden. 
-* **Division durch 0** bei Anteilen/Quoten. 
-  → NULLIF(denominator,0) verwenden. 
-* **MySQL‑Spezialität**: Alias in GROUP BY ist erlaubt, aber **nicht portabel**. 
-  → Ausdruck wiederholen, wenn Portabilität wichtig ist. 
-* **NULLs**: COUNT(spalte) ignoriert NULL → **bewusste Spaltenwahl!** 
- 
-====== LU10: Aggregatfunktionen (MySQL) – mit Beispielen aus «Einbrüche Kanton Zürich» ====== 
- 
-**Datensatz**: *Anzahl Einbrüche nach Gemeinden und Stadtkreisen des Kantons Zürich* 
- 
-<WRAP round 80% box center> 
-**Tabellen- & Feldübersicht (vereinfacht)** 
-**Datenbank**: zh_einbrueche  •  **Tabelle**: einbrueche 
- 
-^ Feld ^ Typ (MySQL) ^ Pflicht ^ Bedeutung ^ 
-| id | BIGINT UNSIGNED | ja (PK) | künstlicher Primärschlüssel | 
-| ausgangsjahr | YEAR | ja | Berichtsjahr der PKS (nicht Tatzeitpunkt) | 
-| gemeinde_bfs_nr | INT | ja | BFS-Nummer der Gemeinde | 
-| gemeindename | VARCHAR(100) | ja | Gemeindename | 
-| stadtkreis_bfs_nr | INT | nein | Nur Stadt Zürich: BFS-Nr. des Stadtkreises | 
-| stadtkreis_name | VARCHAR(100) | nein | Nur Stadt Zürich: Name des Stadtkreises | 
-| gesetz_nummer | DECIMAL(6,1) | ja | Gesetzesnummer des Tatbestands | 
-| gesetz_abk | VARCHAR(16) | ja | Gesetzesabkürzung (z.B. StGB) | 
-| tatbestand | VARCHAR(100) | ja | z.B. Einbruch, Einschleichen | 
-| straftaten_total | INT | ja | Vollendet **+** Versucht | 
-| straftaten_vollendet | INT | ja | Vollendete Taten | 
-| straftaten_versucht | INT | ja | Versuch | 
-| einwohner | INT | ja | Bevölkerungszahl (Ende Vorjahr) | 
-| haeufigkeitszahl | DECIMAL(10,2) | ja | Taten **pro 1'000 Einwohner** | 
-</WRAP> 
- 
-===== 1. Aggregatfunktionen – Theorie & Motivation ===== 
- 
-<WRAP round 80% box center> 
-**Was sind Aggregatfunktionen?** 
-Aggregatfunktionen berechnen **einen zusammengefassten Wert** über mehrere Zeilen (Records). Typische Aufgaben: 
- 
-* **Zählen** (Wie viele Einbrüche?) 
-* **Summieren** (Wie viele Fälle insgesamt?) 
-* **Durchschnitt** (Ø‑Häufigkeitszahl pro Jahr) 
-* **Minimum/Maximum** (geringste/höchste Zahl pro Gemeinde) 
- 
-**Wozu braucht man das?** 
- 
-* **Datenanalyse & Reporting** (z.B. Lageberichte für Polizei/Medien) 
-* **Business Intelligence** (Trends, Hotspots je Gemeinde/Jahr) 
-* **Produkt-/Web‑Integration** (APIs liefern Rohdaten → Aggregation im Backend/SQL spart App‑Logik) 
-* **Entscheidungsgrundlagen** (Ressourcen planen, Präventionsmassnahmen priorisieren) 
- 
-</WRAP> 
- 
-==== 1.1 Wichtigste Funktionen (NULL-Verhalten) ==== 
- 
-^ Funktion ^ Zweck ^ NULLs ^ Beispiel ^ 
-| COUNT(*) | Zeilen zählen | zählt **alle** Zeilen | Anzahl Zeilen im Jahr | 
-| COUNT(spalte) | Nicht‑NULL‑Werte zählen | **ignoriert** NULL | z.B. vorhandene Werte in einer Spalte | 
-| SUM(spalte) | Summe | ignoriert NULL | Total aller straftaten_total | 
-| AVG(spalte) | Durchschnitt | ignoriert NULL | Ø haeufigkeitszahl pro Jahr | 
-| MIN(spalte) | Minimum | ignoriert NULL | tiefste straftaten_total pro Gemeinde | 
-| MAX(spalte) | Maximum | ignoriert NULL | höchste straftaten_total pro Gemeinde | 
- 
-<WRAP round 80% box center> 
-**Syntax** <code sql>SELECT AGGREGATFUNKTION([DISTINCT|ALL] ausdruck) AS alias 
-FROM zh_einbrueche.einbrueche;</code> 
-**MySQL‑Standard** ist ALL (= Duplikate werden mitgerechnet). DISTINCT rechnet nur mit **einzigartigen** Werten. 
-</WRAP> 
- 
-==== 1.2 Einstiegsbeispiele (ohne GROUP BY) ==== 
- 
-**Anzahl unterschiedlicher Gemeinden im Datensatz** <code sql> 
-SELECT COUNT(DISTINCT gemeindename) AS anzahl_gemeinden 
-FROM zh_einbrueche.einbrueche; </code> 
-**Ergebnis (Auszug)** 
-^ anzahl_gemeinden ^ 
-| 171 | 
- 
-**Höchster Einzelwert pro Zeile (Totalfälle)** <code sql> 
-SELECT MAX(straftaten_total) AS max_faelle_in_einer_zeile 
-FROM zh_einbrueche.einbrueche; </code> 
-**Ergebnis (Auszug)** 
-^ max_faelle_in_einer_zeile ^ 
-| 420 | 
- 
- 
-===== 2. GROUP BY – Daten zu Gruppen zusammenfassen ===== 
- 
-<WRAP round 80% box center> 
-**Warum ist GROUP BY wichtig?** 
-Ohne GROUP BY erhalten Sie **einen** Aggregatwert über **alle** Zeilen. Mit GROUP BY erhalten Sie **je Gruppe eine Zeile** (z.B. pro Jahr, pro Gemeinde oder pro Kombination aus Jahr+Gemeinde). Das ist zentral für **Trends, Rankings und Vergleiche**. 
-</WRAP> 
- 
-**Regeln (MySQL‑kompatibel, Standard‑konform formuliert):** 
- 
-* In SELECT dürfen neben Aggregaten **nur** Spalten stehen, die auch im GROUP BY vorkommen. 
-* WHERE filtert **Zeilen vor** dem Gruppieren. HAVING filtert **Gruppen nach** dem Gruppieren. 
-* MySQL erlaubt teils Aliasse in GROUP BY (z.B. GROUP BY jahr nach SELECT YEAR(...) AS jahr). Für bessere Portabilität: **Ausdruck wiederholen**. 
- 
-==== 2.1 Beispiele: Gruppieren nach einem Kriterium ==== 
- 
-<WRAP round 80% box center> 
-**a) Total Einbrüche pro Jahr** <code sql> 
-SELECT ausgangsjahr, 
-SUM(straftaten_total) AS total_faelle 
-FROM   zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr 
-ORDER  BY ausgangsjahr; </code> 
-**Ergebnis (Auszug)** 
-^ ausgangsjahr ^ total_faelle ^ 
-| 2019 | 5080 | 
-| 2020 | 4525 | 
-| 2021 | 4789 | 
-</WRAP> 
- 
-<WRAP round 80% box center> 
-**b) Anzahl erfasste Gemeinden pro Jahr** <code sql> 
-SELECT ausgangsjahr, 
-COUNT(DISTINCT gemeindename) AS anzahl_gemeinden 
-FROM   zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr 
-ORDER  BY ausgangsjahr; </code> 
-**Ergebnis (Auszug)** 
-^ ausgangsjahr ^ anzahl_gemeinden ^ 
-| 2019 | 171 | 
-| 2020 | 171 | 
-| 2021 | 171 | 
-</WRAP> 
- 
-==== 2.2 Beispiele: Gruppieren nach mehreren Kriterien ==== 
- 
-<WRAP round 80% box center> 
-**Total pro Jahr und Tatbestand (z.B. Einbruch, Einschleichen)** <code sql> 
-SELECT ausgangsjahr, 
-tatbestand, 
-SUM(straftaten_total) AS total_faelle 
-FROM   zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr, tatbestand 
-ORDER  BY ausgangsjahr, tatbestand; </code> 
-**Ergebnis (Auszug)** 
-^ ausgangsjahr ^ tatbestand ^ total_faelle ^ 
-| 2020 | Einbruch | 3100 | 
-| 2020 | Einschleichen | 1425 | 
-| 2021 | Einbruch | 3250 | </WRAP> 
- 
-===== 3. HAVING – Gruppen gezielt filtern ===== 
- 
-WHERE kann **keine** Aggregatfunktionen enthalten. Wenn Sie **Ergebnisgruppen** (nach GROUP BY) filtern wollen, verwenden Sie **HAVING**. 
- 
-<WRAP round 80% box center> 
-**Jahre mit über 5'000 Fällen insgesamt** <code sql> 
-SELECT ausgangsjahr, 
-SUM(straftaten_total) AS total_faelle 
-FROM   zh_einbrueche.einbrueche 
-GROUP  BY ausgangsjahr 
-HAVING SUM(straftaten_total) > 5000 
-ORDER  BY total_faelle DESC; </code> 
-**Ergebnis (Auszug)** 
-^ ausgangsjahr ^ total_faelle ^ 
-| 2013 | 6240 | 
-| 2012 | 6115 | </WRAP> 
- 
-<WRAP round 80% box center> 
-**Abarbeitungsreihenfolge (vereinfacht)** 
-FROM → WHERE → GROUP BY → **Aggregatfunktionen** → HAVING → SELECT → ORDER BY. 
-</WRAP> 
- 
- 
-===== 4. Häufige Stolpersteine (und wie man sie vermeidet) ===== 
  
-* SELECT enthält **ungegruppte Spalten** → Fehlermeldung oder Zufallswerte. 
-  → Nur Spalten in GROUP BY **oder** in Aggregaten verwenden. 
-* **Division durch 0** bei Anteilen/Quoten. 
-  → NULLIF(denominator,0) verwenden. 
-* **MySQL‑Spezialität**: Alias in GROUP BY ist erlaubt, aber **nicht portabel**. 
-  → Ausdruck wiederholen, wenn Portabilität wichtig ist. 
-* **NULLs**: COUNT(spalte) ignoriert NULL → **bewusste Spaltenwahl!** 
  
  • modul/m290_guko/learningunits/lu10/theorie/a_einfuehrung.1762363605.txt.gz
  • Zuletzt geändert: 2025/11/05 18:26
  • von gkoch