Letztes Beispiel

Datengestützte Entscheidungsfindung in SQL

Tim Verdonck

Professor Statistics and Data Science

Geschäftsszenario

MovieNow möchte in neue Filme investieren
Für MovieNow ist es teurer, neuere Filme anzubieten als ältere
Erster Schritt der Datenanalyse:
- Geben Kundinnen und Kunden neuen Filmen bessere Bewertungen als älteren?
- Gibt es Unterschiede zwischen den Ländern?

1. Daten verknüpfen

Benötigte Informationen:
- renting – ausgeliehene Filme mit Bewertungen
- customers – Land der Kundinnen und Kunden
- movies – Erscheinungsjahr eines Films
```
SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id;
```

2. Passenden Datensätze auswählen

Nur Filme mit mindestens 4 Bewertungen
Nur Daten von Filmausleihen seit dem 01.04.2018

SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01';

3. Aggregation

Art der Aggregation:

Anzahl der Filmausleihen zählen
Anzahl unterschiedlicher Filme zählen
Durchschnittliche Bewertung berechnen

Aggregationsebenen:

Gesamtaggregation
Filme nach Erscheinungsjahr
Filme nach Erscheinungsjahr, getrennt nach Land der Kundinnen und Kunden

3. Aggregation

SELECT c.country,
       m.year_of_release,
       COUNT(*) AS n_rentals,
       COUNT(DISTINCT r.movie_id) AS n_movies,
       AVG(rating) AS avg_rating
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01'
GROUP BY ROLLUP (m.year_of_release, c.country)
ORDER BY c.country, m.year_of_release;

Ergebnistabelle

| year_of_release | country | n_rentals | n_movies | avg_rating         |
|-----------------|---------|-----------|----------|--------------------|
| 2009            | null    | 10        | 1        | 8.7500000000000000 | 
| 2010            | null    | 41        | 5        | 7.9629629629629630 | 
| 2011            | null    | 14        | 2        | 8.2222222222222222 | 
| 2012            | null    | 28        | 5        | 8.1111111111111111 | 
| 2013            | null    | 10        | 2        | 7.6000000000000000 | 
| 2014            | null    | 5         | 1        | 8.0000000000000000 | 
| null            | null    | 333       | 50       | 7.9024390243902439 |

Lass uns üben!

Datengestützte Entscheidungsfindung in SQL