विंडो फ़ंक्शन: ROW_NUMBER, RANK, SUM OVER

OVER और PARTITION BY की बदौलत पंक्तियों को समेटे बिना रैंक, रनिंग टोटल और समूह-भीतर तुलनाओं की गणना करें।

10 मिनट · 3 प्रश्न

GROUP BY पंक्तियों को समूहों में समेट देता है; एक विंडो फ़ंक्शन एक एग्रीगेट या रैंक की गणना हर पंक्ति को बनाए रखते हुए करता है। यह प्रति समूह टॉप N, रनिंग टोटल और अपनी ही टीम के औसत से तुलना का उपकरण है। SQLite में संस्करण 3.25 (2018) से उपलब्ध। डेटासेट लोड करें।

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

तीन रैंकिंग फ़ंक्शन, बराबरी (ties) का सामना करने पर तीन व्यवहार: ROW_NUMBER बिना दोहराव के क्रमांकन करता है (मनमाना टाई-ब्रेक), RANK बराबरी वालों को एक ही रैंक देता है फिर छोड़ देता है (1, 2, 2, 4), DENSE_RANK नहीं छोड़ता (1, 2, 2, 3)। WINDOW क्लॉज़ विंडो परिभाषा को दोहराने से बचाता है (SQLite और PostgreSQL द्वारा समर्थित, SQL Server द्वारा नहीं)।

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

PARTITION BY हर उपसमुच्चय के लिए गणना फिर से शुरू करता है: प्रति विभाग एक विंडो, कुछ भी समेटे बिना। किसी CTE के साथ मिलकर, यह वह इंटरव्यू पैटर्न देता है: हर विभाग में सबसे अधिक वेतन पाने वाले N लोग। CTE अनिवार्य है क्योंकि विंडो फ़ंक्शन का मूल्यांकन WHERE के बाद होता है: आप सीधे rn पर फ़िल्टर नहीं कर सकते।

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

पारंपरिक एग्रीगेट OVER के साथ विंडो बन जाते हैं: SUM(salary) OVER (ORDER BY ...) पंक्ति-दर-पंक्ति एक रनिंग टोटल उत्पन्न करता है। सख्ती से पंक्ति-दर-पंक्ति रनिंग टोटल के लिए फ़्रेम को ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW से निर्दिष्ट करें।

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

ज्ञान जांच

सुनिश्चित करें कि आपको इस पाठ के मुख्य बिंदु याद हैं।

1. वेतन 5200, 4800, 4800, 4100 के साथ, RANK() 4100 को कौन सी रैंक देता है?
   • 3
   • 4
   • 2
2. ROW_NUMBER() पर फ़िल्टर करने के लिए आपको किसी CTE की ज़रूरत क्यों होती है?
   • OVER का मूल्यांकन WHERE के बाद होता है, इसलिए यह उसके अंदर निषिद्ध है
   • CTE हमेशा अधिक कुशल होता है
   • ROW_NUMBER केवल किसी WITH के अंदर काम करता है
3. किसी OVER क्लॉज़ में PARTITION BY dept क्या करता है?
   • यह अंतिम परिणाम को विभाग के अनुसार क्रमबद्ध करता है
   • यह हर विभाग के लिए विंडो गणना फिर से शुरू करता है
   • यह पंक्तियों को प्रति विभाग एक ही पंक्ति में मिला देता है