WINDOW FUNCTION (OVER Clause)

आइए आज window function के बारे मे समझते हैं जिसमे की हम Over clause का उपयोग करेंगे ।

Database मे किसी भी Query पर जब हमे एक से अधिक Row पर कोई Operation करना हो और उसके Result को किसी भी एक Row पर या एक से अधिक row पर दिखाना हो तब , और जब एक Row की value किसी ओर Row पर लाना हो तब हम Over() Clause का उपयोग करते हैं ।

Over Clause का उपयोग हम जिस भी Function के साथ करते हैं वह Window Function बन जाता हैं, वैसे aggregate function ही ऐसे होते हैं जिन्हे हम बिना Over के उपयोग कर लेते हैं लेकिन बिना Over के वह केवल Aggregate Function होते हैं और Over लगने के बाद वह Window Aggregate Function बन जाते हैं ।

वैसे तो window function बहुत से हैं , मेने आपको समझने के लिए अलग अलग Category से 5 -5 function लिए हैं ताकि मे आपको Window Function का Concept बहुत अच्छे से समझा सकु

यदि आपको Aggregate Function के बारे मे जानकारी नहीं हैं तो आप यंहा क्लिक करे ।

Table Of Content

• Over Clause
  • Over Clause Parameter
    • Partition By Parameter in Over Cluase
    • Order By Parameter in Over Cluase
• Difference Between Simple Aggregate Function And Window Aggregate Function
• Window Function List
  • 1. Window Aggregate Function
    • COUNT() OVER()
    • SUM() OVER()
    • AVG() OVER()
    • MIN() OVER()
    • MAX() OVER()
  • 2. Window Rank Function or Non Aggregate Function
    • ROW_NUMBER() OVER()
    • RANK() OVER()
    • DENSE_RANK() OVER()
    • PERCENT_RANK() OVER()
    • NTILE() OVER()
  • 3. Window Analytical Function / Non aggregate function
    • FIRST_VALUE(ColumnName) OVER()
    • LAST_VALUE(ColumnName) OVER()
    • NTH_VALUE(ColumnName, Position) OVER()
    • LAG(ColumnName) OVER()
    • LEAD(ColumnName) OVER()

---

OVER() Clause

जैसा की हमने समझ की Over Clause का उपयोग करने से केवल aggreage function ही window aggregate function बनते हैं अन्य कोई भी function window function नहीं बनता हैं कुछ अलग से ही function हैं जिनके साथ हमे Over() clause लगाना होता हैं तब ही वह कार्य करते हैं , बिना Over() के वह Error देते हैं ।

Over Clause Parameter

Over Clause को आप बिना Parameter के भी उपयोग कर सकते हैं । और parameter लगा कर भी उपयोग कर सकते है, मुख्य रूप से अधिक उपयोग होने वाले दो Parameter निम्न हैं ।

1. Partition By
2. Order By

Partition By Parameter in Over Cluase

<WindowFunctionName> OVER (PARTITION BY <ColumnName>)

जब हमे window function के कार्य को सभी Rows पर न करते हुवे Column Value के एक Group पर करना हो , यानि की एक से अधिक Row के एक group पर करना हो तब हम partition by <ColumnName> का उपयोग करते हैं। एक तरह से यह group by जैसा ही हैं, जो भी हम Operation Perform करना चाहते हैं वह Group of Rows पर ही Applicable होता है उसके बाद value Reset हो जाती है ।

SELECT *,  
SUM(qty) OVER(PARTITION BY CUST_NM) as SUM_A , 
SUM(qty) OVER(PARTITION BY ITEM_NM) as SUM_B
FROM `citemmst` ORDER BY ID

Order By Parameter in Over Cluase

<WindowFunctionName> OVER ORDER BY <ColumnName> ASC/DESC)

जो भी Operation हम Perform कर रहे हैं उसमे Rows की Sequence क्या रखना हैं वह भी हम Over() Function के parameter मे बता देते हैं । ताकि Rows की Sequence बदल जाए , Order By Clause समझने के लिए क्लिक करे ।

Parameter Type

1. Partition By और Order By यह दोनों ही Optional Parameter हैं, यानि की इनके उपयोग नहीं भी करे तो कोई इशू नहीं हैं ।
2. आप किसी एक का भी उपयोग कर सकते है.
3. दोनों का एक साथ भी उपयोग कर सकते हैं।
4. उपयोग करते समय Partitiion by का उपयोग पहले करे उसके बाद Order By का उपयोग करे।
5. यदि आपकी requirement मे कोई चेंजेस हैं तो आप पहले Order by का भी उपयोग कर सकते हैं ।

आइए अब हम Function के बारे मे समझते हैं ।

SELECT *, sum(qty) over(ORDER by ID) as SUM_A FROM `citemmst`

---

Learn MySQL Android App Download करे और हिन्दी मे MySQL सीखे

---

Difference Between Simple Aggregate Function And Window Aggregate Function

Simple Aggregate function एक से अधिक rows के डाटा को Sum कर single Row मे value Return करता हैं जबकि over function एक से अधिक rows को Sum तों करता हैं परंतु Value को सभी row पर display कर देता हैं ।

किसी भी SOFTWARE PROJECT PLANNING कैसे की जाती हैं ?

---

1. Window Aggregate Function

जो भी हमने Aggregate Function मे Read किया हैं उन्ही Function मे यदि Over Clause का और उपयोग कर लिया जाए तब वही सारे function Window Aggregate Function बन जाते है । इसमे हमे Group By करने की जरूरत नहीं होती हैं ।

1. COUNT() OVER() : गिनने के लिए
2. SUM() OVER() : Sum या टोटल लगाने के लिए
3. AVG() OVER() : Average निकालने के लिए
4. MIN() OVER(): Minimum Value Find करने के लिए ।
5. MAX() OVER() : Maximum Value Find करने के लिए ।

SELECT *,  
COUNT(qty) over() as C_COUNT , 
SUM(qty) over() as C_SUM,  
AVG(qty) over() as C_AVG,
MIN(qty) over() as C_MIN,  
MAX(qty) over() as C_MAX 
FROM `citemmst`

Web Development ke liye Programming Language की लिंकिंग समझे

Related Video : MySQL Sum Window Function | Cumulative Sum Example

---

2. Window Rank Function or non Aggregate Function

1. ROW_NUMBER() OVER()
2. RANK() OVER()
3. DENSE_RANK() OVER()
4. PERCENT_RANK() OVER()
5. NTILE() OVER()

SELECT *, ROW_NUMBER() OVER(ORDER BY RATE) AS A, 
RANK() OVER(ORDER BY RATE) AS B, 
DENSE_RANK() OVER(ORDER BY RATE) AS C, 
PERCENT_RANK() OVER(ORDER BY RATE) AS D, 
NTILE(5) OVER(ORDER BY RATE) AS E 
FROM CITEMMST

ROW_NUMBER() OVER(ORDER BY RATE) AS A : ऐसा लिखने से आप देखेंगे की Rate के अनुसार Record Sort हुवे हैं और एक Number Provide कर दिए हैं 1 से 8 तक , Row_Number का उपयोग करने से जीतने भी records होते हैं उनको Number Allot कर दिए जाते हैं ।

Related Video : MySQL Row Number Window Function in Hindi

RANK() OVER(ORDER BY RATE) AS B : ऐसा लिखने से आप देखेंगे की जो सबसे पहले हैं उसे 1 number दिया गया , जिनके Rate Same हैं उन्हे भी मिलती जुलती ही Rank दी, जब एक Rank दो Record को दी हैं तब 2 रैंक को हटा दी उसके बाद 3 Rank दी हैं , हमने यहा rate से Example लिया हैं यदि आप इसे Student की rank से समझेंगे तो और आसानी से समझ मे आ जाएगा । ऐसा ही आगे 3 रैंक भी दो record को दी हैं तो 4 रैंक को हटा दिया next 5 रैंक दी हैं ।

DENSE_RANK() OVER(ORDER BY RATE) AS C : Dense Rank मे एक तरह से Rank ही हैं इसमे केवल जो रैंक मे एक जैसे Record होने पर हम next number को हटा रहे थे इसमे उसे हटाते नहीं हैं , जैसे पहले दो record same हैं तब हमे तीसरे record को दूसरी रैंक दी हैं वैसे ही आगे के दो record भी same हैं उनको भी एक जैसे rank देने के बाद आगे की रैंक को 3 दिया हैं , इस तरह से टोटल 8 Records मे 6 Rank दी गई हैं जो की Rank Function मे 8 दी गई थी ।

Related Video : MySQL Rank & Dense Rank in Detail in Hindi

PERCENT_RANK() OVER(ORDER BY RATE) AS D : इसका Calculation अलग तरह से ही होत हैं , आइए मे आपको समझता हु ।

• सबसे छोटी वैल्यू को 0 माना जाता हैं ।
• सबसे बड़ी वैल्यू को 1 माना जाता हैं ।
• टोटल जितनी Rows होती हैं उसको हमे 0 से लेकर 1 तक मे बाटना होता हैं ।

Diff = 1 / (No. Of Rows -1)

No. Of Rows = 8 तब 1 / (8-1) होगा 0.14285 के लगभग

अब हम पहली Row को Zero और उसके बाद की हर Row पर 0.1428571429 जोड़ते जाएंगे ।

अब हमे इस अनुसार Ranking देना हैं केवल जिनके Rate एक जैसे हैं उनकी रैंक भी एक जैसी हो जाएगी

NTILE(5) OVER(ORDER BY RATE) AS E : NTILE Function मे आप जो भी Parameter Pass करते हैं । वह सभी Record को उतने भागों मे बाट देता हैं जैसा की आप उपरोक्त उदाहरण मे देख रहे होंगे की पहले दो को 1 दूसरे दो को 2 तीसरे 2 को 3 इस तरह से करने के बाद आखरी वाले को 5 दिया हैं ।

• हमारे द्वारा दी गई संख्या 5 हैं, और टोटल रिकार्ड 8 हैं अब यदि 8 को 5 से भाग पर शेष 3 बचेगा और एक बार पूरा भाग जाएगा ,
• ऐसे केस मे जो Extra 3 वैल्यू हैं उन्हे पहले के तीन नंबर मे Allot कर दिया जाएगा ।

यदि हम समझे की हमारे पास 8 सेब हैं और उसे हमे 5 बॉक्स मे रखना हैं तो कैसे रखेंगे । एक एक सेब को एक एक box मे रखने पर 5 सेब तो रखा जाएंगे। अब जो 3 सेब बचे हैं उनको पहले की इन बॉक्स मे एक एक रख देंगे इस अनुसार पहले की तीन बॉक्स मे 2 -2 सेब होंगे और आखिरी के दो box मे 1-1 ऐसा ही उपरोक्त Example मे हो रहा हैं ।

---

3. Window Analytical Function / non aggregate function

1. FIRST_VALUE(ColumnName) OVER()
2. LAST_VALUE(ColumnName) OVER()
3. NTH_VALUE(ColumnName, Position) OVER()
4. LAG(ColumnName) OVER()
5. LEAD(ColumnName) OVER()

SELECT *, 
FIRST_VALUE(CUST_NM) OVER() AS FIRST,
LAST_VALUE(ITEM_NM) OVER() AS LAST,
NTH_VALUE(CUST_NM, 5) OVER() AS 5TH,
LAG(CUST_NM) OVER()  AS PRV,
LEAD(ITEM_NM) OVER() AS NEXT
FROM CITEMMST

FIRST_VALUE(CUST_NM) OVER() AS FIRST : दिए गए Column Name पर जो पहला Record हैं वह सभी Record पर दिखाने के लिए First Value Function का उपयोग करते हैं। यदि आप किसी Group बना कर उनकी First value लाना चाहते हैं तब आप Over Clause मे Partition By का उपयोग करना होगा ।

LAST_VALUE(ITEM_NM) OVER() AS LAST : दिए गए Column Name पर जो आखरी Record हैं वह सभी Record पर दिखाने के लिए Last_Value Function का उपयोग करते हैं। यदि आप किसी Group बना कर उनकी Last value लाना चाहते हैं तब आप Over Clause मे Partition By का उपयोग करना होगा ।

Related Video : How to Get FIRST Value & LAST Value in MySQL in Hindi

NTH_VALUE(CUST_NM, 5) OVER() AS 5TH : Nth_Value Function मे हम Position दे सकते हैं ताकि हम दी गई Value के अनुसार Data देख सके । first_value aur last_value के उपयोग से आप पहली और आखिरी वैल्यू ही प्राप्त कर पा रहे थे लेकिन आप NTH_VALUE का उपयोग कर दिए गए नंबर से Value देख सकते हैं ।

Related Video : MySQL Nth Value Function in Hindi

LAG(CUST_NM) OVER() AS PRV : जब भी आपको Current Row पर होने पर उससे पहली Row की Value Get करना हो तब आ LAG Function का उपयोग कर सकते हैं ।

LEAD(ITEM_NM) OVER() AS NEXT : जब भी आप Current Row पर उससे नीचे वाली Value को Get करना चाहते हैं तब आपको LEAD Function का उपयोग करना होता है ।

---

सीखे VIDEO के माध्यम से

1. सरल भाषा मे Programming सीखने के लिए Imagination Code Channel को Subscribe करे।
2. MySQL Telegram Channel से जुड़े और पाए Article & Video प्रतिदीन
3. MySQL Beginner & Advance Playlist in Hindi
4. MySQL Overview in One Video
5. MySQL Group By Clause

---

हमारे अन्य आर्टिकल

• MySQL Function
• MySQL Stored Procedure
• PHP Get /Post Method के बारे में जाने
• PHP Math Function के बारे में जाने
• C से C ++ सीखे सरल शब्दों में
• PHP Variable के बारे में जाने
• Arduino Control Structure 
• Union in C
• Declaration of Pointer in C
• jQuery Search Filter
• MySQL Create Table | Alter Table | Drop Table
• API Using NODE REACT EXPRESS MYSQL SEQUELIZE (For Backend OR Server Side)
• CPANEL In Hindi

आशा हैं मुझे की आपको Over Clause का उपयोग कर Window Function समझ मे आ गए होंगे, फिर भी यदि आपको कोई भी doubt हो तो आप मुझे Comment कर सकते हैं।

अपना कीमती समय देने के लिए धन्यवाद ।