डेटा वैज्ञानिकों के लिए SQL कमांड्स

एक डेटा वैज्ञानिक या डेटा विश्लेषक के रूप में, स्वाभाविक रूप से डेटा आपके काम का केंद्र होता है। आप अपना डेटा कई स्रोतों से एकत्र कर सकते हैं। अक्सर डेटा SQL डेटाबेस में होता है, ऐसी स्थिति में SQL क्वेरी कमांड्स की समझ इस भूमिका को सफलतापूर्वक निभाने के लिए आवश्यक हो सकती है। यह लेख आपको कुछ बुनियादी कमांड्स से लेकर उन्नत ऑपरेशन्स तक से परिचित कराएगा, जो डेटा विश्लेषक या डेटा वैज्ञानिक के रूप में काम करते समय आपके काम आएंगे।

ऊपर सूचीबद्ध कमांड्स को समझने के लिए, हम आपको दिखाने के लिए ओपन-सोर्स DVD रेंटल डेटाबेस का उपयोग करेंगे कि इन कमांड्स का उपयोग कैसे किया जा सकता है। एंटिटी रिलेशनशिप डायग्राम (ERD) आपको यहाँ मिल जाएगा।

इस लेख के उद्देश्य से, हमने डेटाबेस को लोकली सेट किया है और सभी क्वेरीज़ pgAdmin4 का उपयोग करके पूरी की हैं, इसलिए हम postgreSQL का उपयोग कर रहे हैं। अधिकतर, यदि सभी नहीं, तो ये कमांड्स अन्य SQL सर्वरों में भी काम करने चाहिए। postgreSQL की इंस्टॉलेशन और स्वयं डेटाबेस सेटअप इस लेख के दायरे से बाहर है; हम आपको DataCamp के Beginner's Guide to PostgreSQL जैसे अन्य संसाधन देखने के लिए प्रोत्साहित करते हैं।

सरल डेटा प्राप्ति

SELECT FROM

प्राप्ति का सबसे सरल उदाहरण डेटाबेस में किसी विशिष्ट टेबल से सारा कंटेंट निकालना है। यदि हम 'Category' टेबल में मौजूद सभी फ़िल्म श्रेणियों को जानना चाहें, तो आप यह कमांड दे सकते हैं:

SELECT *
FROM category
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

यहाँ हमने FROM के बाद टेबल का नाम दिया है। चूँकि हमें टेबल का सारा कंटेंट चाहिए, हमने SELECT के बाद * का उपयोग किया है ताकि हम सब कुछ चाहें यह दर्शाया जा सके।

DISTINCT

ऐसे मामले होते हैं जहाँ हम केवल अद्वितीय मानों में रुचि रखते हैं। उदाहरण के लिए, यदि हम फ़िल्म टेबल से सभी MPAA रेटिंग्स जानना चाहते हैं? DISTINCT का उपयोग करके हम केवल यूनिक मान चुन सकते हैं:

SELECT DISTINCT(rating)
FROM film
संचालित द्वारा

जो लौटाता है:

सरल शर्तों के साथ डेटा प्राप्ति

WHERE

ऐसी परिस्थितियाँ भी होती हैं जब हम केवल विशिष्ट शर्तों को पूरी करने वाली प्रविष्टियाँ ही प्राप्त करना चाहते हैं। हम WHERE क्लॉज़ का उपयोग करके वे शर्तें प्रदान कर सकते हैं जिन्हें डेटा लौटाने से पहले पूरा होना चाहिए। मूल रूप से, हम शर्त के आधार पर प्रविष्टियाँ फ़िल्टर कर रहे हैं। उदाहरण के लिए, यदि हम केवल वे फ़िल्में जानना चाहें जिनकी रेटिंग PG-13 है?

SELECT title
FROM film
WHERE rating = 'PG-13'
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

इसके अतिरिक्त, हम उन फ़िल्मों की सूची भी निकाल सकते हैं जिनका रेंटल रेट 4.5 या उससे अधिक है:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

ORDER BY

लौटे हुए परिणामों को क्रमबद्ध करना डेटा को बेहतर समझने में अक्सर सहायक होता है। मान लीजिए हमें ऊपर वाली PG-13 फ़िल्मों की ही सूची चाहिए, लेकिन फ़िल्म की लंबाई के अनुसार सबसे लंबी से सबसे छोटी तक क्रमबद्ध, और साथ में संबंधित फ़िल्म की लंबाई भी। ORDER BY का उपयोग करके यह आसानी से किया जा सकता है:

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

LIMIT

इसके अलावा, कभी-कभी हम केवल सीमित संख्या में प्रविष्टियों में ही रुचि रखते हैं। यहाँ, हमें वास्तव में केवल सबसे लंबी 10 PG-13 फ़िल्में चाहिए, इसलिए हम LIMIT क्लॉज़ का उपयोग कर सकते हैं।

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
संचालित द्वारा

जो लौटाता है:

एग्रीगेशन्स

GROUP BY & COUNT( )

एग्रीगेशन्स का उपयोग अक्सर डेटा सेट का सार पाने के लिए किया जाता है ताकि अंतर्दृष्टि मिल सके। एग्रीगेशन्स आम तौर पर GROUP BY क्लॉज़ के साथ उपयोग किए जाते हैं। उदाहरण के लिए, यदि हमें जानना है कि अब तक प्रत्येक ग्राहक ने कितनी रेंटल्स की हैं, तो हम रेंटल्स की संख्या गिन सकते हैं और परिणामों को सबसे अधिक रेंटल्स वाले शीर्ष पर रखते हुए क्रमबद्ध कर सकते हैं।

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

SUM( )

प्रत्येक ग्राहक ने कितने पैसे खर्च किए हैं, यह कैसा रहेगा? हम SUM एग्रीगेटर फ़ंक्शन का उपयोग करके इसे कर सकते हैं। यहाँ, हम सबसे अधिक खर्च करने वाले ग्राहक को सबसे ऊपर चाहते हैं।

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

AVG( ) & HAVING

आप ग्रूप्ड एग्रीगेशन की गणना करने के बाद HAVING का उपयोग करके अतिरिक्त शर्तें भी जोड़ सकते हैं। उदाहरण के लिए, हम वे MPAA रेटिंग श्रेणियाँ जानना चाहते हैं जिनमें औसतन 115 मिनट से अधिक लंबी फ़िल्में हैं?

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
संचालित द्वारा

जो लौटाता है:

MIN( ) & Alias

एग्रीगेशन का उपयोग करते समय हमेशा ग्रूपिंग आवश्यक नहीं होती। यदि आप सबसे छोटी फ़िल्म की लंबाई जानना चाहते हैं, तो हम सरलता से MIN फ़ंक्शन का उपयोग कर सकते हैं। हम यह भी चाहते हैं कि कॉलम का नाम 'shortest_movie_length' दिखे, जिसे 'AS' का उपयोग करके एक उपनाम (alias) असाइन करके किया जा सकता है।

SELECT MIN(length) AS shortest_movie_length
FROM film
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

आप टेबल नामों के लिए भी उपनाम (alias) का उपयोग कर सकते हैं। यह तब उपयोगी होता है जब आप लंबे टेबल नामों के साथ काम कर रहे होते हैं, खासकर जब टेबल्स को जोड़ा जा रहा हो (नीचे देखें)। हमें यह भी ध्यान रखना चाहिए कि हम किस प्रकार का alias उपयोग करते हैं क्योंकि यह कोड की पठनीयता को काफी प्रभावित कर सकता है।

अन्य उपयोगी एग्रीगेशन फ़ंक्शन्स भी हैं, और आप उनके बारे में अधिक जान सकते हैं Introduction to SQL कोर्स में।

Joins

INNER JOIN (JOIN)

आपने शायद देखा होगा कि केवल एक टेबल को देखकर हम सीमित ही काम कर सकते हैं। हम अक्सर कई टेबल्स के डेटा को साथ में देखना चाहते हैं। यही वह जगह है जहाँ हम JOINs का उपयोग करते हैं। हम किसी कॉमन की के आधार पर टेबल्स को जोड़ सकते हैं। उदाहरण के लिए, यदि हम ऐसी फ़िल्में ढूँढ रहे हों जो अंग्रेज़ी में नहीं हैं:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'
संचालित द्वारा

(कृपया ध्यान दें कि इस डेटाबेस में अंग्रेज़ी के अलावा अन्य भाषाओं में कोई फ़िल्म नहीं है। ऊपर दी गई क्वेरी से आपको एक खाली टेबल मिलेगा।)

आप कई टेबल्स को भी जोड़ सकते हैं। उदाहरण के लिए, नीचे हम श्रेणियों और उनकी संबंधित औसत रेटिंग जानना चाहते हैं। हमने औसत रेटिंग के अनुसार मानों को क्रमबद्ध भी किया है, जिसमें सबसे अधिक पहले हैं।

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

Joins के कई प्रकार होते हैं: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN। यहाँ, हमने JOIN का उपयोग किया है, जो डिफ़ॉल्ट रूप से INNER JOIN होता है, जिसमें प्रविष्टियाँ तभी मिलती हैं जब वे दोनों टेबल्स में मौजूद हों। यदि आप इन joins के बीच के अंतर के बारे में अधिक जानना चाहते हैं, तो आप Joining Data in SQL कोर्स देख सकते हैं।

डेटा प्रकार बदलना

CAST( )

ऊपर उपयोग किए गए उदाहरण में, जहाँ हमने डॉलर राशि की गणना की, आपने ध्यान दिया होगा कि SQL केवल मानों को संख्याओं के रूप में देखता है। हम CAST फ़ंक्शन का उपयोग करके इन्हें डॉलर राशि में बदल सकते हैं:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

Money एकमात्र चीज़ नहीं है जिसमें हम मानों को बदल सकते हैं। हम संख्याओं को फ़्लोट, टेक्स्ट, यहाँ तक कि दिनांक और समय में भी बदल सकते हैं।

ROUND( )

इसी तरह, हम संख्याओं को राउंड भी कर सकते हैं। ऊपर वाले उदाहरण में जहाँ हमने औसत रेंटल रेट की गणना की, गणना किए गए मानों में कई दशमलव अंक हैं। हम दो दशमलव तक राउंड करने के लिए ROUND फ़ंक्शन का उपयोग कर सकते हैं:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

जटिल शर्तें

CASE स्टेटमेंट

आप कल्पना कर सकते हैं कि ऊपर दिए गए कई फ़ंक्शन्स और क्लॉज़ को मिलाकर आप काफ़ी जटिल क्वेरी कर सकते हैं। हालाँकि, ऐसी परिस्थितियाँ भी होती हैं जहाँ बुनियादी बातें पर्याप्त नहीं होतीं। उदाहरण के लिए, यदि हम फ़िल्म-लंबाई की श्रेणियों वाला एक कॉलम जोड़ना चाहें। हम फ़िल्मों को 'Short Film' लेबल कर सकते हैं यदि वे 50 मिनट से कम हों, 'Long Film' यदि 150 मिनट से अधिक हों, और शेष के लिए 'Medium Film'। हम इसे CASE स्टेटमेंट का उपयोग करके कर सकते हैं:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

जैसा कि आप देख सकते हैं, निर्दिष्ट शर्त पूरी होने पर परिणाम वापस आता है। यह प्रोग्रामिंग भाषाओं में 'if-else' कथनों के समान है।

सबक्वेरीज़

अब हम यह जानना चाह सकते हैं कि लंबाई में सबसे लंबी फ़िल्में कौन-सी हैं और उनके शीर्षक क्या हैं। इसके लिए, हम ऊपर पेश किया गया WHERE क्लॉज़ इस्तेमाल कर सकते हैं, बस एक अधिक जटिल शर्त के साथ:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)
संचालित द्वारा

जो लौटाता है:

सबक्वेरीज़ का उपयोग करके जटिल क्वेरी संभव हैं। इस उदाहरण में, हम उन फ़िल्म शीर्षकों की सूची जानना चाहते हैं जिनकी रेंटल रेटिंग सभी कॉमेडी फ़िल्मों की औसत रेंटल रेटिंग से अधिक है:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

इस उदाहरण में, हम पहले कॉमेडी फ़िल्मों की औसत फ़िल्म रेंटल रेटिंग निकालते हैं। इस क्वेरी में (ऊपर कोष्ठकों के अंदर) प्राप्त मान को फिर WHERE क्लॉज़ में एक शर्त के रूप में उपयोग किया जाता है, जिसमें हम फ़िल्म शीर्षक पा सकते हैं।

कॉमन टेबल एक्सप्रेशन्स (CTEs)

एक अधिक जटिल उदाहरण में, हम कॉमन टेबल एक्सप्रेशन (CTE) का उपयोग करके एक अस्थायी टेबल सेव कर सकते हैं, जिससे हम आवश्यक जानकारी निकालते हैं। इस उदाहरण में, हम जानना चाहते हैं कि प्रत्येक स्टाफ़ औसतन प्रति सप्ताह कितनी रेंटल प्रोसेस करता है।

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
संचालित द्वारा

जो लौटाता है:

ऊपर के मामले में, हम प्रति सप्ताह रेंटल की संख्या को 'weekly_rentals' नामक अस्थायी टेबल में सेव कर रहे हैं, जिस पर हम अंतिम परिणाम पाने के लिए एक और क्वेरी चला सकते हैं।

विंडो फ़ंक्शन्स

विंडो फ़ंक्शन्स SQL कौशल का एक और समूह हैं जो महत्वपूर्ण हैं और आपको अगले स्तर तक ले जा सकते हैं। भले ही विंडो फ़ंक्शन्स को इंटरमीडिएट से एडवांस्ड स्तर के कौशल माना जाता है, कई डेटा विश्लेषक और वैज्ञानिक इन्हें अपने काम के लिए अनिवार्य मानते हैं। इसलिए इनके साथ परिचित होने में समय लगाना सार्थक है।

OVER( ) और PARTITION BY यहां, मान लें कि हम प्रत्येक स्टाफ़ के साप्ताहिक रेंटल की संख्या और समग्र कुल रेंटल की संख्या, साथ ही स्टाफ़ आईडी और सप्ताह संख्या जानना चाहते हैं। हम ऊपर के उदाहरण में बनाई गई CTE को पुन: उपयोग कर सकते हैं, जैसा कि नीचे दिखाया गया है:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

कोड में OVER होना यह निर्दिष्ट करता है कि यह एक विंडो फ़ंक्शन है। यहां, हम कह रहे हैं कि विंडो फ़ंक्शन (यहां SUM) हर पंक्ति पर लागू होता है। हम विंडो फ़ंक्शन को PARTITION BY के साथ भी उपयोग कर सकते हैं ताकि इसे निर्दिष्ट 'पार्टिशन' पर लागू किया जा सके। मान लें कि अब हम समग्र रेंटल संख्या के बजाय प्रति सप्ताह कुल रेंटल संख्या जानना चाहते हैं (अन्य कॉलमों के अलावा)। फिर से, नीचे दिए उदाहरण में हम पहले से जेनरेट की गई CTE का उपयोग कर रहे हैं, जैसा कि पहले किया था।

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
संचालित द्वारा

जो लौटाता है (केवल पहली 10 पंक्तियाँ दिखाई गई हैं):

अतिरिक्त कॉलम में रनिंग टोटल के बजाय कुल साप्ताहिक रेंटल संख्या होती है।

यहां हमने विंडो फ़ंक्शन्स के केवल कुछ सरल उदाहरण पेश किए हैं जो कई कंपनियों में डेटा विश्लेषक या वैज्ञानिक होने के लिए महत्वपूर्ण हैं। हम आपको दिए गए लिंक का उपयोग करके इन कमांड्स के बारे में और सीखने के लिए प्रोत्साहित करते हैं। आप अपनी SQL कौशल सीखने या ताज़ा करने के लिए Intermediate SQL कोर्स भी देख सकते हैं। इसके अतिरिक्त, आप किसी ओपन सोर्स डेटाबेस को लेकर इन अलग-अलग कमांड्स को आज़माते हुए, उत्तर देने योग्य प्रासंगिक प्रश्नों के बारे में सोचते हुए अपने कौशल का अभ्यास कर सकते हैं।