%md

# <span id="uebersicht">Übersicht</span>
---
## 1: Vorbereitung
---
1.1: Anlegen der Daten<br>
1.2: Datenmodell<br>

---
## 2: Einfache Abfragen mit SQL
---
2.1: Spalten auswählen<br>
2.2: Datensätze filtern<br>
2.3: Zählen, Summieren und andere Aggregationen<br>
2.4: Daten gruppieren<br>
2.5: Das Ergebnis sortieren<br>

---
## 3: Erweiterte Abfragen mit SQL
---
3.1: Verhältnisgrößen berechnen mit SQL<br>
3.2: Transformieren von Daten mit SQL<br>
&nbsp;&nbsp;&nbsp;&nbsp;3.2.1: Mehrere Spalten zu einer Spalte zusammenfassen<br>
&nbsp;&nbsp;&nbsp;&nbsp;3.2.2: Eine Spalte zu einer Zeile zusammenfassen<br>
&nbsp;&nbsp;&nbsp;&nbsp;3.2.3: Anreichern der Daten mit weiteren Spalten<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a) Regelbasierte Anreicherung<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b) Verwendung von Mapping-Tabellen<br>

---
## 4: Statistische Analysen mit SQL
---
4.1: Lageparameter bestimmen<br>
4.2: Zusammenhänge ermitteln<br>

---
## 5: Komplexe Abfragen mit SQL (nicht Teil dieser Einheit)
---
5.1: Abfragen über mehrere Tabellen (JOINs)<br>
5.2: Mengenoperationen<br>
5.3: Unterabfragen<br>
5.4: Window-Funktionen<br>

---
## Links
---
### Fragebogen & Daten
[🔗 Datenmodell Orangenlimonade als XLS](https://docs.google.com/spreadsheets/d/1Sq_CWA-oTN90d0EpA6rEDB3C77dyIXv0HZTq3YWuyy8/edit?usp=sharing)<br>
[🔗 Fragebogen Orangenlimonade inklusive Codierung (PDF)](https://drive.google.com/file/d/1-8aEkgxhgUPgRanEoxIu_2vQQybpLeyO/view?usp=sharing)<br>

### SQL
[🔗 SQL Cheat Sheet](https://docs.google.com/presentation/d/1jgeZ3RKLmgDgiES1UpvQwUKYGGBWEmpNpYXFVEQIh6s/export/pdf)<br>
[🔗 Slides zu SQL aus der Veranstaltung "Information Management"](https://docs.google.com/presentation/d/1Ga31SJKo6KTfMq0m2Z5T7eTmGqMPdBn5cLVnzWHWS4k/export/pdf)<br>
[🔗 Spark SQL Funktionsreferenz](https://spark.apache.org/docs/latest/api/sql/)<br>

%md
# 1: Vorbereitung

Bevor wir loslegen können, müssen wir die Daten in eure persönlichern Databricks-Accounts laden und uns mit dem Datenmodell vertraut machen.

%md
## 1.1: Anlegen der Daten
---
Den Block unten müsset ihr nur einmal ausführen. Der Code legt die benötigte Tabelle `limonade` an, mit der wir im Folgenden weiter arbeiten werden.

%scala
import scala.sys.process._
val tables = Array("limonade")

var delimiter = ",";

for(t <- tables) {

  val tableName = t
  dbutils.fs.rm("file:/tmp/" + tableName + ".csv.gz")

  "wget -P /tmp https://s3.amazonaws.com/nicolas.meseth/data+sets/" + tableName + ".csv.gz" !!

  val localpath = "file:/tmp/" + tableName + ".csv.gz"
  dbutils.fs.rm("dbfs:/datasets/" + tableName + ".csv.gz")
  dbutils.fs.mkdirs("dbfs:/datasets/")
  dbutils.fs.cp(localpath, "dbfs:/datasets/")
  display(dbutils.fs.ls("dbfs:/datasets/" +  tableName + ".csv.gz"))

  sqlContext.sql("drop table if exists " + tableName)
  val df = spark.read.option("header", "true") 
                        .option("inferSchema", "true")
                        .option("sep", delimiter)
                        .option("quote", "\"")
                        .option("escape", "\"")
                        .csv("/datasets/" +  tableName + ".csv.gz")
  df.unpersist()
  df.cache()
  df.write.saveAsTable(tableName);  
}

%md
## 1.2: Datenmodell

Eine Liste mit allen Spalten könnt ihr [hier als Spreadsheet](https://docs.google.com/spreadsheets/d/1Sq_CWA-oTN90d0EpA6rEDB3C77dyIXv0HZTq3YWuyy8/edit?usp=sharing) einsehen. Alternativ könnt ihr euch die Spalten einer Tabelle auch mithilfe des `describe` Befehls ausgeben lassen.

Den Fragebogen könnt ihr hier herunterladen (`Strg` gedrückt halten, um in einem neuen Tab zu öffnen): [Fragebogen inklusive Codierung (PDF)](https://drive.google.com/file/d/1-8aEkgxhgUPgRanEoxIu_2vQQybpLeyO/view?usp=sharing)

%md
# 2: Einfache Abfragen mit SQL
SQL ist eine **Abfrage**sprache, die sich gut für strukturierte Daten eignet. Der Begriff "strukturiert" bedeutet in diesem Zusammenhang, dass wir die Daten in Tabellenform vorliegen haben, ähnlich wie in einem Excel-Spreadsheet. Konkret bedeutet das, dass die Daten in **Spalten** und **Zeilen** vorliegen. Jede Spalte besitzt einen Namen und speichert ein Merkmal bezüglich der Daten. Die Daten liegen zeilenweise vor, also z.B. repräsentiert jede Zeile in der Tabelle einen ausgefüllten Fragebogen eines Teilnehmers.

Wir verwenden den SELECT-Befehl, um Daten abzufragen. Dabei können wir mit dem SELECT-Befehl unterschiedliche Methoden anwenden, um das Ergebnis nach unseren Wünschen zu gestalten. Einige dieser Methoden lernen wir anhand der folgenden Aufgabenstellungen kennen.

%md
## 2.1: Spalten auswählen
Eine wichtige Funktion des SELECT-Befehls ist die Einschränkung der Spalten, die im Ergebnis enthalten sind. Datensätze enthalten häufig sehr viele Spalten, der hier vorliegende Datensatz hat 274 (!) Spalten. Aber nur wenige sind für die Beantwortung einer Fragestellung tatsächlich relevant.

%md
### Aufgabe 1
---
**Erstellt eine SQL-Abfrage, die im Ergebnis nur Informationen zum Studium des Teilnehmers beinhaltet!**

%md
### Aufgabe 2
---
**Wählt aus den gesamten Spalten nur die Spalten für die Frage 12 ("Was verbinden Sie spontan mit dem Begriff Orangenlimonade?") aus!**

%md
## 2.2: Datensätze filtern
SQL lässt uns nicht nur bestimmen, welche Spalten wir im Ergebnis sehen wollen, sondern auch, welche **Zeilen**. Um das zu anzugeben, können wir so genannte Bedingungen definieren, die für jede Zeile gelten müssen. Gilt eine der Bedingungen nicht, so ist die Zeile nicht im Ergebnis enthalten.

Diese Filterbedingungen erstellen leiten wir mit dem Schlüsselwort `WHERE` ein, gefolgt von ein oder mehreren Bedingungen. Die folgenden Aufgaben bringen uns die WHERE-Klausel näher.

%md
### Aufgabe 3
---
**Wir wollen uns nur die Antworten von Teilnehmern ansehen, deren Haushalt mindestens 2 Personen angehören. Filtert die Datensätze entsprechend!**

✓ Die Größe des Haushalts wird in Frage 48 erfasst.

%md
### Aufgabe 4
---
**Angenommen es interessieren uns nur Antworten von Teilnehmern, die 1990 oder später geboren wurden. Filtert die Datensätze entsprechend!**

✓ Das Geburtsjahr wird in Frage 39 erfasst.

%md
### Aufgabe 5
---
**Filtert die Daten so, dass nur Teilnehmer mit dem Status "Noch Schüler", "Volks-/ Hauptschulabschluss" und "Ohne Abschluss" enthalten sind!**

✓ Der Schulabschluss wird in Frage 40 erfasst.

%md
### Aufgabe 6
---
**Welche der männlichen Teilnehmer essen mehrmals pro Woche Fast-Food?**

✓ Das Geschlecht wird in Frage 53 erfasst.<br>
✓ Die Ernährungsgewohnheiten werden in Frage 38 erfasst.

%md
## 2.3: Zählen, Summieren und andere Aggregationen
Häufig wollen wir aggregierte Werte aus den Daten ermitteln. Jedes Mal wenn wir fragen "*Wie viele...*" fragen wir nach einer Zahl, die letztlich das Ergebnis einer Aggregation der Daten ist. Es gibt viele andere Möglichkeiten, Daten zu aggregieren. Einige davon lernen wir anhand der folgenden Aufgaben kennen.

%md
### Aufgabe 7
---
**Wie viele Antworten sind im Datensatz insgesamt enthalten?**

✓ Die Funktion `count()` zählt Datensätze!

%md
### Aufgabe 8
---
**Wie groß ist der größte Haushalt unter den Antworten?**

✓ Die Größe des Haushalts wird in Frage 48 erfasst.<br>
✓ `CAST(<col> AS DECIMAL) IS NOT NULL` prüft ob der Wert ind er Spalte einen numerischen Wert enthält<br>
✓ Die Funktionen `max()` und `min()` sprechen für sich.<br>

%md
### Aufgabe 9
---
**Wie wichtig ist den Teilnehmern Orangenlimonade auf Parties im Durchschnitt?**

✓ Diese Info findet ihr in Frage 25.<br>
✓ Die Funktion `avg()` berechnet das arithmetische Mittel.<br>
✓ Vorsicht, ihr müsst etwas beachten. Schaut euch mal das Ergebnis an und überlegt, ob es valide ist.<br>

%md
### Aufgabe 10
---
**Wie viele Personen leben in Summe in den Haushalten der Befragten?**

✓ Diese Info findet ihr in Frage 48.<br>
✓ Die Funktion `sum()` summiert numerische Daten.<br>

%md
### Aufgabe 11
---
**Aus welchem Geburtsjahrgang stammt der älteste Teilnehmer?**

✓ Das Geburtsjahr wird in Frage 39 erhoben.<br>

%md
## 2.4: Daten Gruppieren

Wir haben oben Aggregationsfunktionen kennengelernt, die uns jeweils einen Wert zurückliefern. Beispiele waren die Summe, der Durchschnitt oder der größte Wert der gesamten Daten. Oft reicht aber ein Wert alleine nicht aus, sondern wir brauchen je einen Durchchnittswert pro irgendeiner Untergruppe. Beispielsweise das Durschnittsalter pro Geschlecht.

Um das mit SQL zu erreichen nutzen wir so genannte Gruppierungen, die wir auf Basis von Spalten (oder Ausdrücken) bilden können. Wir verwenden hierfür das Schlüsselwort `group by` und nennen dann die zu gruppierenden Spalten (oder Ausdrücke).

%md
### Aufgabe 12
---
**Wie ist die Verteilung der Postleitzahlen unter den Antworten?**

✓ Die PLZ wird in Frage 51 erhoben.<br>
✓ Erstellt auch eine passende Visualisierung.<br>
✓ Stellt sicher, dass nur gültige PLZ im Ergebnis enthalten sind.

%md
### Aufgabe 13
---
**Wie ist die Verteilung der Geschlechter unter den Teilnehmern der Umfrage?**

✓ Das Geschlecht wird in Frage 53 erhoben.<br>
✓ Zeigt die Verteilung der absoluten Zahlen.<br>
✓ Erstellt auch eine passende Visualisierung.<br>

%md
### Aufgabe 14
---
**Wie sind die Geburtsjahrgänge verteilt?**

✓ Die Info findet ihr in Frage 39.<br>
✓ Zeigt die Verteilung der absoluten Zahlen.<br>
✓ Visualisiert das Ergebnis als Balkendiagramm, sortiert nach dem Jahrgang.<br>

%md
### Aufgabe 15
---
**Wie ist die Geschlechterverteilung pro Studiengang?**

✓ Das Geschlecht wird in Frage 53 erhoben.<br>
✓ Der Studiengang wird in Frage 44 erhoben.<br>
✓ Zeigt die Verteilung der absoluten Zahlen.<br>

%md
## 2.5: Das Ergebnis sortieren

Oft ist es notwendig, das Resultat einer SQL Abfrage zu sortieren. Häufige Szenarien sind Top-Listen sowie die beste oder schlechteste Zeile im Ergebnis. Die folgenden Aufgaben helfen euch dabei, das Sortieren mit SQL anwenden zu lernen.

%md
### Aufgabe 16
---
**Sortiert die Daten nach dem Alter der Befragten, so dass die Ältesten oben stehen!**

%md
### Aufgabe 17
---
**Welche Einkommensklasse nennt im Durchschnitt den höchsten Preis für einen guten Deal (egal welche Limonadensorte)?**

✓ Die Abfrage von Preisgrenzen erfolgt in Frage 21.<br>
✓ Erstellt auch eine passende Visualisierung.<br>