de:modul:m290_guko:learningunits:lu11:theorie:b_groupby

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Nächste Überarbeitung		 Vorhergehende Überarbeitung
de:modul:m290_guko:learningunits:lu11:theorie:b_groupby [2026/11/01 12:11] – angelegt gkochde:modul:m290_guko:learningunits:lu11:theorie:b_groupby [2026/11/01 12:12] (aktuell) – gkoch
Zeile 1: Zeile 1:
-====== LU10a: Aggregatfunktionen Einführung ======+====== LU11b: Gruppieren mit GROUP BY – Kennzahlen pro Kategorie ======
  
-===== 0. Datensätze ===== +{{ de:modul:m290_guko:learningunits:lu10:theorie:group_by-sum.webp?900 |}}
-Wir arbeiten mit **realen Daten** aus unterschiedlichen Bereichen. Das macht die Übungen **praxisnah**: Wir beantworten echte Fragen, erkennen **Trends** und leiten **Kennzahlen** ab.   +
-Die drei Datensätze sind aus unterschiedlichen Sparten: +
-  * **Einbrüche** (Kanton Zürich) – Datenbank: ''zh_einbrueche'', Tabelle: ''einbrueche'' +
-  * **Raumfahrt (1957–2022)** – Datenbank: ''spacemission'', Tabelle: ''missions'' +
-  * **Musiktrends (YouTube Top Songs 2025)** – Datenbank: ''youtube_top_100_songs_2025'', Tabelle: ''youtube_top_100_songs_2025''+
  
- +<WRAP round 80% box center> 
-=== Einbrüche (Kanton Zürich) === +**Warum GROUP BY?**   
- +Ohne ''GROUP BY'' erhalten Sie **eine einzige** Aggregatkennzahl über **alle** Zeilen.   
-<WRAP center> +Mit ''GROUP BY'' erhalten Sie **eine Zeile pro Gruppe** (z. B. pro Jahr, pro Firma, pro Gemeinde).
-{{de:modul:m290_guko:learningunits:lu10:theorie:noun-robbery-6965605.svg?100&nolink|Robbery-Icon – Gofficon (Noun Project)}}+
 </WRAP> </WRAP>
-((Bildquelle: ''Robbery'' von Gofficon, [[https://thenounproject.com/browse/icons/term/robbery/|Noun Project]] – Lizenz: CC BY 3.0)) 
  
- +===== 1. Regeln bei Abfragen mit Aggregatfunktionen ===== 
-  * **Zeitraum:** 2009–2024 +<WRAP round 80% box center> 
-  * **Quelle:** Kantonspolizei ZH +  * In ''SELECT'' dürfen neben Aggregaten **nur** Spalten stehen, die auch im ''GROUP BY'' vorkommen.   
-  * **Lizenz:** CC0 +  * ''WHERE'' filtert **Zeilen vor** dem Gruppieren; ''HAVING'' filtert **Gruppen nach** dem Gruppieren.  
-  * **Granularität:** je **Gemeinde** (Stadt Zürich zusätzlich je **Stadtkreis**) +
- +
-<WRAP box center round 80%> +
-**Was steckt drin? (Auszug Felder)**   +
-^ Feld ^ Bedeutung ^ +
-| ''ausgangsjahr'' | Berichtsjahr | +
-| ''gemeindename'' / ''stadtkreis_name'' | örtliche Zuordnung | +
-| ''straftaten_total'' | Vollendet **+** Versucht | +
-| ''straftaten_vollendet'', ''straftaten_versucht'' | Aufschlüsselung | +
-| ''einwohner'' | Bevölkerungszahl (Ende Vorjahr) | +
-| ''haeufigkeitszahl'' | Fälle **pro 1’000 Einwohner** |+
 </WRAP> </WRAP>
  
-**Beispielfragen:**   +<WRAP alert center round 70%> 
-  * Wie viele Einbrüche gab es **letztes Jahr** in **meiner Gemeinde**?   +**Typischer Fehler:**  
-  * Welche Gemeinden haben im **Durchschnitt** die **höchsten Raten**?   +
- +
-=== Space Missions === +
- +
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:noun-space-7278594.svg?100&nolink|Space-Icon – Zahirulizul (Noun Project)}} +
-</WRAP> +
-((Bildquelle: ''Space'' von Zahirulizul, [[https://thenounproject.com/browse/icons/term/space/|Noun Project]] – Lizenz: CC BY 3.0)) +
- +
- +
-  * **Zeitraum:** 1957–2022 +
-  * **Quelle:** Scrape von nextspaceflight.com +
-  * **Granularität:** **Start-Ereignis** (Mission) +
- +
-<WRAP box center round 80%> +
-**Was steckt drin? (Auszug Felder)**   +
-^ Feld ^ Bedeutung ^ +
-| ''company'' | Betreiber/Organisation (staatlich, militärisch, privat) | +
-| ''location'' | Startplatz (Kosmodrom, Spaceport, Pad) | +
-| ''launch_date'' | Datum/Zeit des Starts | +
-| ''rocket'' / ''status_rocket'' | Träger & Status (aktiv/retired) | +
-| ''mission_status'' | Erfolg/Fehlschlag u. a. | +
-| ''price_usd'' | Missionskosten (falls vorhanden; Mio. USD) | +
-</WRAP> +
- +
-**Beispielfragen:**   +
-  * Wie viele Starts hat **SpaceX** im Vergleich zu **staatlichen Akteuren** bis **2022**? +
-  * Welche **Jahre** waren **startstark**, welche **pannenanfällig**?   +
-  * Welche **aktiven Raketen** haben die **meisten erfolgreichen** Starts? +
- +
-=== YouTube Top 100 (2025) === +
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:noun-song-8043534.svg?100&nolink|Song-Icon – Puspa Kusuma (Noun Project)}} +
-</WRAP> +
-((Bildquelle: ''Song'' von Puspa Kusuma, [[https://thenounproject.com/browse/icons/term/song/|Noun Project]] – Lizenz: CC BY 3.0)) +
- +
- +
-  * **Stichtag:** 22.09.2025 +
-  * **Umfang:** 100 Songs +
-  * **Granularität:** **Video/Track** inkl. Kanal-Metadaten +
- +
-<WRAP box center round 80%> +
-**Was steckt drin? (Auszug Felder)**   +
-^ Feld ^ Bedeutung ^ +
-| ''title'' / ''full_title'' | Videotitel (offiziell) | +
-| ''channel'' | Künstler:in/Kanal | +
-| ''view_count'' | Views (bis > 2 Mrd.) | +
-| ''duration'' | Videolänge (ca. 2–6 Minuten) | +
-| ''tags'' / ''description'' | Metadaten (Genre, Labels, Promo) | +
-| ''category'' | Kategorie (meist **Music**) | +
-</WRAP> +
- +
-**Beispielfragen:**   +
-  * Welche sind die **meistgespielten Songs 2025** und welche **Channels** sind **besonders erfolgreich**?   +
-  * Gibt es einen **Zusammenhang** zwischen **Songdauer** und **Anzahl Views**?   +
- +
-==== Setup (Download & Import) ==== +
- +
-Laden Sie die vorbereiteten Datensätze herunter und importieren Sie sie:   +
- +
-{{ de:modul:m290_guko:learningunits:lu10:theorie:datensaetze_lu10_spacemissions_einbrueche_youtube_top_100.zip | ZIP mit allen drei SQL-Dumps }} +
- +
- +
-{{ de:modul:m290_guko:learningunits:lu10:theorie:load_and_import_sql_files_aggregate.mp4?1040x585 | Screencast, der zeigt wie die SQL-Files in Webstorm ausgeführt werden. Drei Datenbanken mit Tabellen und Datensätzen werden in MySQL erstellt. }} +
-//Screencast, der zeigt wie die SQL-Files in Webstorm ausgeführt werden. Drei Datenbanken mit Tabellen und Datensätzen werden in MySQL erstellt.// +
- +
- +
-<WRAP tip center round 70%> +
-**Schema wählen (wichtig):**   +
-Entweder im Editor-Dropdown das Schema wählen **oder** mit ''USE'' explizit setzen:+
 <code sql> <code sql>
--- Beispiele: +SELECT gemeindename, SUM(straftaten_total) 
-USE spacemission; +FROM einbrueche;  -- ❌ fehlendes GROUP BY → Fehler/Zufallswerte
--- USE zh_einbrueche; +
--- USE youtube_top_100_songs_2025;+
 </code> </code>
 +Immer **alle nicht aggregierten Spalten** ins ''GROUP BY'' aufnehmen.
 </WRAP> </WRAP>
  
- +===== 2. Beispiel: Wie viele Missionen pro Firma (Top 10)? =====
-===== 1. Was sind Aggregatfunktionen – und warum braucht man sie? ===== +
 <WRAP round 80% box center> <WRAP round 80% box center>
-Aggregatfunktionen fassen **viele Zeilen** zu **Kennzahlen** zusammen (z. B. Anzahl, Summe, Durchschnitt, Minimum/Maximum).   +<code sql> 
-Sie sind die Grundlage für **Berichte, Dashboards, BI-Auswertungen((Business Intelligence: systematische, kennzahlenbasierte Datenanalyse zur Entscheidungsunterstützung; typische Artefakte sind Reports, Dashboards, Zeitreihen und Rankings.)) und Web-Backends**((Serverseitige Logik/Services einer Web-App; liest Datenquellen, validiert, rechnet und stellt Ergebnisse über APIs (Schnittstellen) bereit.)).+SELECT company, COUNT(*) AS starts 
 +FROM missions 
 +GROUP BY company 
 +ORDER BY starts DESC 
 +LIMIT 10; 
 +</code> 
 +**Was passiert?** Wir **zählen** pro Firma und sortieren absteigend; ''LIMIT'' begrenzt die Ausgabe. 
 +{{de:modul:m290_guko:learningunits:lu10:theorie:company_starts.png?nolink&300|}} 
 +//Firmen und Starts – Infos zu den Firmen-Kürzel gibts ganz am Schluss dieser Seite.//
 </WRAP> </WRAP>
  
-==== 1.1 Die wichtigsten Aggregatfunktionen ====+===== 3. Beispiel: Total Einbrüche pro Jahr (Kanton ZH) =====
 <WRAP round 80% box center> <WRAP round 80% box center>
-^ Funktion ^ Zweck ^ NULL-Verhalten ^ +<code sql> 
-| ''COUNT(*)'' | Anzahl Zeilen | zählt **alle** Zeilen | +SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle 
-| ''COUNT(spalte)'' | Anzahl **nicht NULL** | **ignoriert** NULL | +FROM einbrueche 
-| ''SUM(spalte)'' | Summe | ignoriert NULL | +GROUP BY ausgangsjahr 
-| ''AVG(spalte)'' | Durchschnitt | ignoriert NULL | +ORDER BY ausgangsjahr DESC 
-| ''MIN(spalte)'' | Kleinster Wert | ignoriert NULL | +LIMIT 5; 
-| ''MAX(spalte)'' | Grösster Wert | ignoriert NULL |+</code> 
 +**Was passiert?** ''SUM(...)'' aggregiert pro Jahr (''GROUP BY ausgangsjahr''); ''ORDER BY ... DESC'' sortiert chronologisch absteigend. ''LIMIT 5'' -> max 5 Ergebnisse. 
 +{{de:modul:m290_guko:learningunits:lu10:theorie:einbrueche_zh.png?nolink&300|}} 
 +//Während den Pandemie-Jahren ging die Zahl der Einbrüche im Kanton Zürich zurück – jetzt steigt sie wieder an.//
 </WRAP> </WRAP>
  
-<WRAP round 80% box center> 
-**Syntax (allgemein)**   
-<code sql>SELECT AGGREGATFUNKTION(ausdruck) AS alias FROM schema.tabelle;</code> 
-</WRAP> 
  
 +===== 4. HAVING – Gruppen nach Kennzahlen filtern =====
  
-===== 2. Einfache Aggregationen ===== +=== 4.1 Warum HAVING? ===
- +
-=== 2.1 COUNT – Wie viele Datensätze sind es? ===+
 <WRAP round 80% box center> <WRAP round 80% box center>
-**Frage:** Wie viele Missionen befinden sich im Datensatz? +''WHERE'' kann **keine** Aggregatfunktionen enthalten.   
-<code sql> +Wenn Sie **Gruppen** nach einer **Kennzahl** (z. B. ''SUM(...)'', ''COUNT(...)'') filtern möchten → **''HAVING''**.
-SELECT COUNT(*) AS anzahl_missionen +
-FROM missions; +
-</code> +
-**Was passiert?** ''COUNT(*)'' zählt alle Zeilen. +
-\\ +
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:anzahlmissionen.png?300&nolink}} +
-</WRAP>+
 </WRAP> </WRAP>
  
-=== 2.2 SUM – Summe bilden ===+ 
 +=== 4.2 Beispiel: Jahre mit über 25'000 Einbruch-Fällen (ZH) ===
 <WRAP round 80% box center> <WRAP round 80% box center>
-**Frage:** Wie viele **Views** haben alle 100 YouTube-Songs zusammen? 
 <code sql> <code sql>
-SELECT SUM(view_count) AS views_total +SELECT ausgangsjahr, SUM(straftaten_total) AS total_faelle 
-FROM youtube_top_100_songs_2025;+FROM einbrueche 
 +GROUP BY ausgangsjahr 
 +HAVING SUM(straftaten_total) > 25000 
 +ORDER BY total_faelle DESC;
 </code> </code>
-**Was passiert?** ''SUM(view_count)'' addiert alle Views über die Tabelle. +**Was passiert?** ''HAVING'' filtert nur jene **Jahre**, deren Summe über dem Schwellwert liegt. 
-\\ +{{de:modul:m290_guko:learningunits:lu10:theorie:total_faelle_having.png?nolink&300|}}
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:total_views.png?300&nolink}} +
-//Ergebnis: 2025 haben die 100 meistgeklickten YouTube-Songs insgesamt über 10 Milliarden Views erzielt.// +
-</WRAP>+
 </WRAP> </WRAP>
  
-=== 2.3 AVG – Durchschnitt berechnen === + 
-<WRAP round 80% box center> +<WRAP tip center round 70%> 
-**Frage:** Wie lang ist ein Song **im Durchschnitt**? +**WHERE vs. HAVING**   
-<code sql> +• ''WHERE'': Zeilen **vor** dem Gruppieren filtern (z. B. Jahre ab 2018).   
-SELECT AVG(duration) AS avg_duration +• ''HAVING'': **Gruppen** nach **Aggregaten** filtern (z. B. ''SUM(...) > 25000'').
-FROM youtube_top_100_songs_2025; +
-</code> +
-**Was passiert?** ''AVG(duration)'' berechnet den Mittelwert; NULLs werden ignoriert. +
-\\ +
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:avg_duration.png?300&nolink}} +
-//Ergebnis: 2025 haben die 100 meistgeklickten YouTube-Songs eine durchschnittliche Song-Dauer von 3 min 24s.// +
-</WRAP>+
 </WRAP> </WRAP>
  
-=== 2.4 MIN / MAX – Kleinster / Grösster Wert === 
  
-<WRAP round 80% box center> 
-**Fragen:** Längster Song? Kürzester Song? 
-<code sql> 
--- Längster Song 
-SELECT MAX(duration) AS max_duration 
-FROM youtube_top_100_songs_2025; 
  
--- Kürzester Song +=== Infos zu den Firmen involviert in die "Space Race" === 
-SELECT MIN(duration) AS min_duration +<WRAP box center round 80%> 
-FROM youtube_top_100_songs_2025; +^ Kürzel in Liste ^ Ausgeschrieben / Organisation ^ Land / Region ^ 
-</code>+| **RVSN USSR** | Strategic Missile Forces of the USSR (Raketentruppen der strategischen Bestimmung) | **Sowjetunion** (historisch) | 
 +| **CASC** | China Aerospace Science and Technology Corporation | **China** | 
 +| **Arianespace** | Arianespace S.A. (europäischer Launch-Dienstleister) | **Europa / Frankreich** | 
 +| **General Dynamics** | General Dynamics Astronautics (u. a. Atlas-Programm; historisch) | **USA** | 
 +| **VKS RF** | Russian Aerospace Forces (WKS / Vozdushno-Kosmicheskiye Sily) | **Russland** | 
 +| **NASA** | National Aeronautics and Space Administration | **USA** | 
 +| **SpaceX** | Space Exploration Technologies Corp. | **USA** | 
 +| **US Air Force** | United States Air Force (historischer Launch-Operator) | **USA** | 
 +| **ULA** | United Launch Alliance (JV von Boeing & Lockheed Martin) | **USA** | 
 +| **Boeing** | The Boeing Company (Boeing Launch Services / Delta-Programm) | **USA** |
  
-**Was passiert?** ''MAX(...)'' gibt den grössten, ''MIN(...)'' den kleinsten Wert zurück. +</WRAP>
-\\ +
-<WRAP center> +
-{{de:modul:m290_guko:learningunits:lu10:theorie:max_duration.png?300&nolink}} {{de:modul:m290_guko:learningunits:lu10:theorie:min_duration.png?300&nolink}}+
  
-//Ergebnis: Der längste Song in der Tabelle ist "Kendrick Lamar - Not Like Us" mit einer Dauer von 5 min 54s und der kürzeste Song "Claudia Valentina - Candy (Official Video)" mit 2 Minuten.// 
-</WRAP> 
-</WRAP> 
  • de/modul/m290_guko/learningunits/lu11/theorie/b_groupby.1793531491.txt.gz
  • Zuletzt geändert: 2026/11/01 12:11
  • von gkoch