Filme gruppieren

Datengestützte Entscheidungsfindung in SQL

Bart Baesens

Professor Data Science and Analytics

Anwendung von GROUP BY

Präferenzen der Kundschaft nach Land oder Geschlecht
Beliebtheit von Filmen nach Genre oder Erscheinungsjahr
Durchschnittspreis von Filmen nach Genre

Tabelle movies_selected

GROUP BY

SELECT genre
FROM movies_selected
GROUP BY genre;

| genre       |
|-------------|
| Drama       |
| Fantasy     |
| Sci-Fiction |
| Animation   |
| Romance     |

Durchschnittliche Leihgebühr

SELECT genre, 
       AVG(renting_price) AS avg_price
FROM movies_selected
GROUP BY genre;

| genre       | avg_price   |
|-------------|-------------|
| Drama       | 2.865       |
| Fantasy     | 2.69        |
| Sci-Fiction | 2.87        |
| Animation   | 2.923333333 |
| Romance     | 2.99        |

Tabelle movies_selected

Tabelle movies_selected aufgeteilt

Durchschnittliche Leihgebühr und Anzahl der Filme

SELECT genre, 
       AVG(renting_price) AS avg_price, 
       COUNT(*) AS number_movies
FROM movies_selected
GROUP BY genre

| genre       | avg_price     | number_movies |
|-------------|---------------|---------------|
| Drama       | 2.865         | 4             |
| Fantasy     | 2.69          | 3             |
| Sci-Fiction | 2.87          | 2             |
| Animation   | 2.923333333   | 3             |
| Romance     | 2.99          | 1             |

HAVING

SELECT genre, 
       AVG(renting_price) avg_price, 
       COUNT(*) number_movies
FROM movies
GROUP BY genre
HAVING COUNT(*) > 2;

| genre     | avg_price     | number_movies |
|-----------|---------------|---------------|
| Drama     | 2.865         | 4             |
| Fantasy   | 2.69          | 3             |
| Animation | 2.923333333   | 3             |

Lass uns üben!

Datengestützte Entscheidungsfindung in SQL