QUALIFY: वह SQL फ़िल्टरिंग स्टेटमेंट जिसकी आपको ज़रूरत थी पर आप जानते नहीं थे

SQL QUALIFY क्लॉज का मूल सिद्धांत और सिंटैक्स

QUALIFY एक क्लॉज है जिसका उपयोग विंडो फ़ंक्शन के परिणामों को फ़िल्टर करने के लिए किया जाता है। इसलिए, QUALIFY क्लॉज का सफलतापूर्वक उपयोग करने के लिए, SELECT सूची या QUALIFY क्लॉज में कम से कम एक WINDOW फ़ंक्शन होना चाहिए – केवल वे पंक्तियाँ लौटाई जाएँगी जहाँ बूलियन अभिव्यक्ति TRUE का मान लेती है।

टिप: SQL विंडो फ़ंक्शंस चीट शीट देखें।

यदि QUALIFY क्लॉज न हो, तो WINDOW फ़ंक्शन के परिणामों पर फ़िल्टर लगाने के लिए नेस्टिंग की आवश्यकता पड़ेगी। SQL डेवलपर्स आमतौर पर नेस्टेड क्वेरीज़ के उपयोग से यथासंभव बचने की सलाह देते हैं क्योंकि वे कोड को कम मानव-पठनीय बनाती हैं और डीबगिंग व सहयोग की जटिलता बढ़ाती हैं।

दूसरे शब्दों में, QUALIFY क्लॉज के पीछे का मुख्य विचार उन क्वेरीज़ को सरल बनाना था जिनमें विंडो फ़ंक्शंस के परिणामों पर फ़िल्टरिंग की आवश्यकता होती है; इसका अर्थ है कि QUALIFY का मूल्यांकन विंडो फ़ंक्शंस की गणना के बाद किया जाता है।

QUALIFY स्टेटमेंट वाली क्वेरी के लिए निष्पादन का सामान्य क्रम इस प्रकार है:

• FROM
• WHERE
• GROUP BY और समूहन
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

QUALIFY स्टेटमेंट का सामान्य सिंटैक्स इस प्रकार है:

QUALIFY <predicate>

इसके सामान्य रूप में, यह कुछ इस तरह दिखेगा:

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]

नोट: <predicate> एक अभिव्यक्ति है जिसका उपयोग समूहन और विंडो फ़ंक्शंस के गणना हो जाने के बाद परिणाम को फ़िल्टर करने के लिए किया जाता है।

SQL QUALIFY का उपयोग कब करें

QUALIFY स्टेटमेंट क्लॉज HAVING क्लॉज की तरह ही है क्योंकि यह फ़िल्टरिंग करने के लिए सबक्वेरी की आवश्यकता से बचाता है। उदाहरण के लिए, आप QUALIFY का उपयोग किसी विश्लेषणात्मक फ़ंक्शन के परिणामों को फ़िल्टर करने के लिए कर सकते हैं – ऐसा फ़ंक्शन जो पंक्तियों के एक समूह के आधार पर एक समग्र मान की गणना करता है।

यह स्पष्ट करने के लिए कि आप ठीक कब QUALIFY का उपयोग करेंगे, यहाँ एक छोटा परिदृश्य है:

मान लीजिए आप एक डेटा वैज्ञानिक के रूप में काम कर रहे हैं, और एक हितधारक आपसे प्रत्येक ग्राहक के अंतिम लॉगिन की जानकारी माँगता है। आपकी प्रारंभिक क्वेरी कुछ इस तरह दिख सकती है:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 

यह एक अच्छा प्रारंभ है, लेकिन यह हितधारक के अनुरोध को पूरा नहीं करता क्योंकि यह सभी लॉगिन लौटाएगा, न कि केवल सबसे हाल का। अंतिम लॉगिन लौटाने के लिए, हमें एक फ़िल्टर जोड़ने की आवश्यकता है।

SQL के नए उपयोगकर्ता निम्नानुसार WHERE स्टेटमेंट क्लॉज का उपयोग करके इसे फ़िल्टर करने के प्रयास के जाल में फँस सकते हैं:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) AS previous_logins
FROM login_logs
WHERE TRUE 
  AND last_login = 1

दुर्भाग्य से, यह कोड निष्पादित नहीं होगा; यदि आप GROUP BY और HAVING का प्रयास करते हैं तो भी यही सच है।

ऐसा इसलिए होता है क्योंकि यह सब उस ऑपरेशंस के क्रम पर निर्भर करता है जिसकी हमने पिछले भाग में चर्चा की थी।

WHERE, GROUP BY और HAVING सभी का मूल्यांकन WINDOW फ़ंक्शंस से पहले होता है; इसका अर्थ है वे WINDOW फ़ंक्शंस को फ़िल्टर नहीं कर सकते क्योंकि उन्हें उनके अस्तित्व का पता ही नहीं होता – उन तीनों क्लॉज़ के चलने के समय तक WINDOW फ़ंक्शन का मूल्यांकन नहीं हुआ होता है।

विंडो फ़ंक्शन के बाद फ़िल्टर लगाने का एक तरीका कॉमन टेबल एक्सप्रेशन (CTE) का उपयोग करना है।

हमारा कोड इस तरह दिखेगा:

WITH 
logins AS (
  SELECT 
      user_id, 
      ip, 
      country_code,
      os,
      RANK() OVER (
        PARTITION BY user_id ORDER BY log_datetime DESC
      ) AS previous_logins
  FROM login_logs
  WHERE TRUE
)

SELECT user_id, ip, country_code, os 
FROM logins
WHERE previous_logins = 1

तकनीकी रूप से, यह कोड मान्य है – यह पूरी तरह ठीक से निष्पादित होगा और वे परिणाम लौटाएगा जो आपका हितधारक चाहता है।

हालाँकि, हमें दो क्वेरीज़ पेश करनी पड़ीं, और कुछ अतिरिक्त कोड की पंक्तियाँ भी हैं, जो यदि आप इस प्रक्रिया को नियमित रूप से दोहराते हैं तो अनावश्यक हो सकती हैं।

इस समस्या का सबसे अच्छा उपाय QUALIFY का उपयोग करना है।

कोड इस तरह दिखेगा:

-- Starter code from @Jiho Choi on StackOverflow 
SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 
QUALIFY previous_logins = 1

यह समाधान काम करता है क्योंकि SQL के ऑपरेशंस के क्रम में QUALIFY क्लॉज़ का मूल्यांकन WINDOW फ़ंक्शंस के बाद किया जाता है, जिसका मतलब है कि उन्हें उनके अस्तित्व की जानकारी होती है और वे उसी क्वेरी में उन्हें फ़िल्टर कर सकते हैं।

आप सोच रहे होंगे कि कम कोड लिखने के अलावा QUALIFY के क्या फायदे हैं, और उत्तर यह है कि वास्तव में कोई खास प्रदर्शन संबंधी लाभ नहीं हैं।

हमारे द्वारा उपयोग किया गया CTE समाधान और QUALIFY समाधान दोनों ही समान समय-सीमा में निष्पादित होते हैं, इसलिए हम यह नहीं कह सकते कि QUALIFY समाधान से कोई खास प्रदर्शन सुधार होता है।

मुख्य लाभ उपयोग में आसानी है; कोड कम है, और पढ़ने में सरल।

अन्य फ़िल्टरिंग विधियों की तुलना में SQL QUALIFY

अब तक, आप समझ चुके हैं कि QUALIFY स्टेटमेंट क्लॉज SQL में उपलब्ध एक और फ़िल्टरिंग विधि है। आप यह भी जानते हैं कि QUALIFY क्लॉज का उपयोग करने का सही समय कब है।

लेकिन QUALIFY और अन्य फ़िल्टरिंग विधियों के बीच अंतर क्या है?

आइए प्रत्येक फ़िल्टरिंग विधि का पुनरावलोकन करें।

WHERE क्लॉज

WHERE का उपयोग किसी तालिका में रिकॉर्ड्स को फ़िल्टर करने के लिए किया जाता है; यानी यह पंक्ति-स्तरीय ऑपरेशन लागू करता है। हम इसका उपयोग तब करते हैं जब हमें किसी विशिष्ट शर्त को पूरा करने वाले रिकॉर्ड्स निकालने होते हैं। उदाहरण के लिए, हम किसी की आयु के आधार पर डेटासेट को फ़िल्टर करना चाह सकते हैं। कोड में यह इस तरह दिखेगा:

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; 

WHERE क्लॉज के बारे में मुख्य बातें:

1. यह किसी विशिष्ट शर्त के आधार पर रिकॉर्ड्स को फ़िल्टर करने के लिए उपयोग किया जाता है।
2. ऑपरेशन पंक्तियों पर लागू होते हैं।
3. आप WHERE क्लॉज का उपयोग GROUP BY क्लॉज के बिना भी कर सकते हैं।
4. WHERE को SELECT, UPDATE, DELETE स्टेटमेंट्स के साथ उपयोग किया जा सकता है।

आइए इसकी तुलना HAVING क्लॉज से करें।

HAVING क्लॉज

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

इसका मुख्य लाभ यह है कि आप HAVING क्लॉज को समेकित समूहों के एक उपसमुच्चय पर लागू कर सकते हैं, जो WHERE ब्लॉक में संभव नहीं है।

उदाहरण:

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 

उपरोक्त कोड उन जेंडर समूहों को लौटाएगा जहाँ औसत कद 170 सेंटीमीटर से अधिक है; दूसरे शब्दों में, HAVING कॉलम-स्तरीय ऑपरेशन लागू करता है।

एक और बात ध्यान देने की है कि HAVING का उपयोग केवल SELECT स्टेटमेंट के साथ किया जा सकता है, जबकि WHERE कई के साथ।

QUALIFY क्लॉज

QUALIFY, HAVING के समकक्ष है, सिवाय इसके कि यह WINDOW फ़ंक्शंस के परिणामों पर फ़िल्टर लगाता है, जबकि HAVING समेकित फ़ंक्शंस और GROUP BY क्लॉज़ पर फ़िल्टर लगाता है – GROUP BY का उपयोग कैसे करें के बारे में और जानें।

यदि हम प्रत्येक आयु समूह के भीतर सबसे लंबे व्यक्ति के बारे में जानकारी जानना चाहें, तो हम गणना करने के लिए WINDOW फ़ंक्शन का उपयोग कर सकते हैं और परिणामों को फ़िल्टर करने के लिए QUALIFY क्लॉज का उपयोग कर सकते हैं ताकि हमें प्रत्येक आयु समूह से केवल सबसे लंबा व्यक्ति ही मिले।

कोड में यह इस प्रकार दिखेगा:

SELECT name, gender, height, weight, age 
RANK() over (
    PARTITION BY age ORDER BY height DESC
  ) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1

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

निष्कर्ष और आगे की पढ़ाई

SQL सामान्य डेटा साइंस वर्कफ़्लो में महत्वपूर्ण भूमिका निभाता है; SQL में QUALIFY क्लॉज जैसी उपयोगी क्रियाओं को लागू करना जानना अत्यंत सहायक है क्योंकि यह आपको साफ-सुथरा कोड लिखने में सक्षम बनाता है, जिससे दूसरों के लिए आपके साथ सहयोग करना सुखद हो जाता है।

यदि आप अपनी SQL कौशल में महारत हासिल करना चाहते हैं, तो हम नीचे दिए गए संसाधनों की जाँच करने की सलाह देते हैं:

• SQL बेसिक्स चीट शीट
• Introduction to SQL कोर्स
• SQL Fundamentals स्किल ट्रैक