Repérer les acteurs favoris selon des groupes de clients
Prise de décision fondée sur les données avec SQL
Irene Ortner
Data Scientist at Applied Statistics
Combiner des instructions SQL en une requête
- LEFT JOIN
- WHERE
- GROUP BY
- HAVING
- ORDER BY
Des locations aux infos client et acteur
Notre question : quel est l'acteur favori d'un certain groupe de clients ?
Joindre la table renting avec
customersactsinactors
SELECT *
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id;
Clients masculins
- Acteurs les plus présents dans les films vus par des clients masculins.
SELECT a.name,
COUNT(*)
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id
WHERE c.gender = 'male'
GROUP BY a.name;
Qui est l'acteur favori ?
- Acteur le plus visionné.
- Meilleure note moyenne lors du visionnement.
SELECT a.name,
COUNT(*) AS number_views,
AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id
WHERE c.gender = 'male'
GROUP BY a.name;
Ajouter HAVING et ORDER BY
SELECT a.name,
COUNT(*) AS number_views,
AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id
WHERE c.gender = 'male'
GROUP BY a.name
HAVING AVG(r.rating) IS NOT NULL
ORDER BY avg_rating DESC, number_views DESC;
Ajouter HAVING et ORDER BY
| name | number_views | avg_rating |
|--------------------|--------------|------------|
| Ray Romano | 3 | 10.00 |
| Sean Bean | 2 | 10.00 |
| Leonardo DiCaprio | 3 | 9.33 |
| Christoph Waltz | 3 | 9.33 |
Passons à la pratique !
Prise de décision fondée sur les données avec SQL
