Exemple final

Prise de décision fondée sur les données avec SQL

Tim Verdonck

Professor Statistics and Data Science

Étude de cas

MovieNow envisage d'investir dans de nouveaux films.
Il coûte plus cher à MovieNow de proposer des films récemment produits que des plus anciens.
Première étape de l'analyse de données :
- Les clientes et clients attribuent-ils de meilleures notes aux films récents qu'aux anciens ?
- Y a-t-il des écarts selon les pays ?

1. Joindre les données

Information requise :

renting : locations de films avec notes
customers : pays du ou de la cliente

movies : année de sortie du film

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. Sélectionner les bons enregistrements

Conserver seulement les films ayant au moins 4 notes
Conserver seulement les locations depuis 2018-04-01

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. Agréger

Type d'agrégation :

Compter le nombre de locations
Compter le nombre de films distincts
Calculer la note moyenne

Niveaux d'agrégation :

Agrégation totale
Par année de sortie
Par année de sortie et par pays du ou de la cliente

3. Agréger

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;

Table résultante

| 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 |

Passons à la pratique !

Prise de décision fondée sur les données avec SQL