अंतिम उदाहरण

SQL में डेटा-ड्रिवन निर्णय-निर्माण

Tim Verdonck

Professor Statistics and Data Science

बिज़नेस केस

MovieNow नए फिल्मों में निवेश पर विचार कर रहा है.
हाल ही में बनी फिल्मों को उपलब्ध कराना MovieNow के लिए पुरानी फिल्मों से ज्यादा महंगा है.
डेटा विश्लेषण का पहला चरण:
- क्या ग्राहक हाल ही में बनी फिल्मों को पुरानी फिल्मों से बेहतर रेटिंग देते हैं?
- क्या देशों के बीच फर्क है?

1. डेटा जॉइन करें

ज़रूरी जानकारी:
- renting रेंटल रिकॉर्ड्स जिनमें रेटिंग्स हैं
- customers में ग्राहक के देश की जानकारी
- movies में फिल्म का रिलीज़ वर्ष
```
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. प्रासंगिक रिकॉर्ड चुनें

सिर्फ उन्हीं फिल्मों के रिकॉर्ड लें जिनकी कम से कम 4 रेटिंग्स हैं
सिर्फ 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. एग्रीगेशन

एग्रीगेशन के प्रकार:

मूवी रेंटल्स की संख्या गिनें
अलग-अलग फिल्मों की संख्या गिनें
औसत रेटिंग निकालें

एग्रीगेशन के स्तर:

कुल एग्रीगेशन
रिलीज़ वर्ष के अनुसार फिल्मों के लिए
रिलीज़ वर्ष के अनुसार, ग्राहकों के देश के हिसाब से अलग-अलग

3. एग्रीगेशन

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;

प्राप्त तालिका

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

अभ्यास करते हैं!

SQL में डेटा-ड्रिवन निर्णय-निर्माण