ROW_NUMBER, RANK और DENSE_RANK: बराबर स्कोर और हर समूह के Top N

ROW_NUMBER हर पंक्ति को अलग स्थान देता है। RANK बराबर स्कोर को समान रैंक देता है और आगे स्थान छोड़ता है। DENSE_RANK समान रैंक देता है, लेकिन स्थान नहीं छोड़ता। बराबर स्कोर होने पर “टॉप दो” का अर्थ चुने गए फ़ंक्शन से बदलता है।

फ़ंक्शन	95, 95, 80 स्कोर के स्थान	उपयोग
ROW_NUMBER	स्पष्ट tie-breaker के साथ 1, 2, 3	हर समूह से अधिकतम N पंक्तियाँ
RANK	1, 1, 3	प्रतियोगिता की रैंकिंग
DENSE_RANK	1, 1, 2	सबसे ऊँचे N अलग स्कोर स्तर

PARTITION BY team हर टीम की गिनती फिर से शुरू करता है। GROUP BY के विपरीत window functions अलग-अलग पंक्तियाँ बनाए रखते हैं। एक ही नतीजे में व्यक्ति का स्कोर और उसका स्थान दिख सकता है।

SQLite में खुद चलाकर देखें

SQL Lab खोलें, यह सेटअप पेस्ट करें और एक बार चलाएँ। फिर हर क्वेरी अलग चलाएँ। टेबल और कॉलम के नाम अंग्रेज़ी में रखे गए हैं ताकि हर भाषा में वही SQL चले। लैब और लिंक किए गए पाठ के निर्देश अभी अंग्रेज़ी में हैं।

CREATE TABLE blog_scores (
  id INTEGER PRIMARY KEY, team TEXT, name TEXT, score INTEGER
);
INSERT INTO blog_scores VALUES
  (1, 'A', 'Asha', 95), (2, 'A', 'Luis', 95), (3, 'A', 'Mei', 80),
  (4, 'B', 'Noor', 90), (5, 'B', 'Omar', 70), (6, 'B', 'Pia', 70);

1. बराबर स्कोर पर तीनों फ़ंक्शन देखें

ROW_NUMBER के क्रम में id भी है, इसलिए बराबर स्कोर का क्रम स्थिर है। RANK और DENSE_RANK में केवल score है, इसलिए बराबर स्कोर समान रैंक पाते हैं। उनके window ORDER BY में id जोड़ने से बराबरी टूट जाएगी। अंतिम ORDER BY केवल नतीजे दिखाने का क्रम तय करता है; यह window की गणना से अलग है।

SELECT team, name, score,
  ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS row_num,
  RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_num
FROM blog_scores
ORDER BY team, score DESC, id;

अपेक्षित नतीजा

team	name	score	row_num	rank_num	dense_num
A	Asha	95	1	1	1
A	Luis	95	2	1	1
A	Mei	80	3	3	2
B	Noor	90	1	1	1
B	Omar	70	2	2	2
B	Pia	70	3	2	2

2. हर टीम से अधिकतम दो लोग निकालें

ROW_NUMBER लगाएँ और निकले हुए position को बाहरी क्वेरी में फ़िल्टर करें। टीम B से Noor और Omar आते हैं; id से बराबरी तय होने पर Pia हट जाती है। हर टीम की अधिकतम दो पंक्तियाँ आती हैं। SQLite में उसी स्तर के WHERE में window result नहीं लगा सकते, इसलिए CTE बीच का नतीजा बनाता है।

WITH ranked AS (
  SELECT team, name, score, id,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE position <= 2
ORDER BY team, position;

अपेक्षित नतीजा

team	name	score
A	Asha	95
A	Luis	95
B	Noor	90
B	Omar	70

3. सबसे ऊँचे दो अलग स्कोर स्तर निकालें

DENSE_RANK सबसे ऊँचे दो स्कोर स्तरों के सभी लोगों को रखता है। इस डेटा में दोनों टीमों से तीन-तीन लोग आते हैं। जब बराबर योग्य स्कोर वाले सभी लोगों को रखना हो, तब यह सही है; यह ठीक दो लोगों की सीमा नहीं है।

WITH ranked AS (
  SELECT team, name, score, id,
    DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS score_band
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE score_band <= 2
ORDER BY team, score DESC, id;

अपेक्षित नतीजा

team	name	score
A	Asha	95
A	Luis	95
A	Mei	80
B	Noor	90
B	Omar	70
B	Pia	70

अभ्यास और उत्तर की जाँच

आखिरी क्वेरी में DENSE_RANK की जगह RANK करें और score_band <= 2 रहने दें। टीम A में अब केवल Asha और Luis आते हैं: दो बराबर शीर्ष स्कोर के बाद Mei की रैंक 3 है। टीम B में Noor, Omar और Pia अभी भी आते हैं।

फ़ंक्शन चुनने से पहले साफ करें: “दो लोग”, “प्रतियोगिता रैंक दो तक” या “दो अलग स्कोर”? ये तीन अलग नियम हैं। बराबर स्कोर वाला डेटा ज़रूर जाँचें; सभी स्कोर अलग हों तो अंतर नहीं दिखेगा।

ROW_NUMBER में दोहराए जा सकने वाले नतीजों के लिए स्थिर और unique tie-breaker रखें। उसके बिना डेटाबेस बराबर स्कोर का क्रम बदल सकता है। NULL स्कोर हो सकते हों तो तय करें कि वे योग्य हैं या नहीं और उनका क्रम स्पष्ट करें या उन्हें हटाएँ। NULL का क्रम डेटाबेस के अनुसार बदलता है।

दोहराव हटाने के लिए duplicate key पर PARTITION BY और पसंदीदा timestamp के साथ unique ID पर ORDER BY लगा सकते हैं। बाहरी क्वेरी में position 1 रखें, लेकिन हटने वाली पंक्तियाँ पहले देखें। रैंकिंग चयन की तकनीक है; इससे डेटा मिटाना अपने आप सुरक्षित नहीं हो जाता।

दस्तावेज़ और आगे की पढ़ाई

रैंकिंग का पाठ और OVER clause का पाठ देखें। क्वेरी SQLite पर चलती हैं; PostgreSQL और MySQL 8 में भी ये फ़ंक्शन हैं। यहाँ QUALIFY जैसे किसी खास dialect के clause पर निर्भरता नहीं है।

• SQLite के आधिकारिक दस्तावेज़
• डेटाबेस के आधिकारिक दस्तावेज़