Dernier exemple

Prise de décision basée sur des données en SQL

Tim Verdonck

Professor Statistics and Data Science

Analyse de rentabilité

MovieNow envisage d'investir dans de nouveaux films.
Il est plus coûteux pour MovieNow de proposer des films récemment produits que des films plus anciens.
Première étape de l'analyse des données :
- Les clients attribuent-ils de meilleures évaluations aux films récemment produits qu'aux films plus anciens ?
- Existe-t-il des différences entre les pays ?

1. Joindre des données

Informations requises :
- renting enregistrements des locations de films avec évaluations
- customers informations concernant le pays du client
- 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 enregistrements pertinents

Utiliser uniquement les enregistrements de films ayant obtenu au moins 4 évaluations
Utiliser uniquement les enregistrements des locations de films depuis le 1er avril 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. Agrégation

Type d'agrégation :

Compter le nombre de locations de films
Compter le nombre de films différents
Calculer la moyenne des évaluations

Niveaux d'agrégation :

Agrégation totale
Pour les films classés par année de sortie
Pour les films classés par année de sortie, séparément pour chaque pays des clients.

3. Agrégation

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 obtenue

| 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 basée sur des données en SQL