Films groeperen

Data-driven beslissingen nemen met SQL

Bart Baesens

Professor Data Science and Analytics

Toepassingen van GROUP BY

Voorkeuren van klanten per land of geslacht.
Populariteit van films per genre of releasejaar.
Gemiddelde prijs van films per genre.

Tabel: movies_selected

GROUP BY

SELECT genre
FROM movies_selected
GROUP BY genre;

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

Gemiddelde huurprijs

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        |

Tabel movies_selected

Tabel movies_selected gesplitst

Gemiddelde huurprijs en aantal films

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             |

Laten we oefenen!

Data-driven beslissingen nemen met SQL