MySQL में Window Functions

Window Functions Detail Rows रखती हैं

Window function current row से related rows पर calculate करती है लेकिन उन्हें collapse नहीं करती। GROUP BY one summary row per group देता; window aggregate same group summary हर detail row से attach कर सकता है।

aggregate_or_window_function(...)
OVER (
  PARTITION BY ...
  ORDER BY ...
  frame_clause
)

Verified Score Lab

X-A total/average 270/90; X-B total/average 243/81।

Rows Collapse किए बिना Class Totals और Averages

SELECT student_id, student_name, class_name, marks,
       SUM(marks) OVER (
         PARTITION BY class_name
       ) AS class_total,
       ROUND(AVG(marks) OVER (
         PARTITION BY class_name
       ), 2) AS class_average
FROM advanced_scores
ORDER BY class_name, student_id;

सभी six detail rows रहती हैं। PARTITION BY omit हो तो SUM/AVG one global six-row partition use करेंगे।

Explicit Frame के साथ Running Total

SELECT student_name, class_name, marks,
       SUM(marks) OVER (
         PARTITION BY class_name
         ORDER BY student_id
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW
       ) AS running_total
FROM advanced_scores
ORDER BY class_name, student_id;

Frame partition की first row से current row तक है। Explicit ROWS physical row-by-row accumulation देता। Default frames equal ORDER BY peers को साथ treat कर सकती हैं, इसलिए financial/marks reports में frame implicit न छोड़ें।

LAG से Previous Row Compare करें

SELECT student_name, class_name, marks,
       LAG(marks) OVER (
         PARTITION BY class_name
         ORDER BY student_id
       ) AS previous_marks,
       marks - LAG(marks) OVER (
         PARTITION BY class_name
         ORDER BY student_id
       ) AS change_from_previous
FROM advanced_scores
ORDER BY class_name, student_id;

LAG self join के बिना earlier row पढ़ता है। LEAD later row पढ़ता है। हर partition की first row का predecessor नहीं, इसलिए LAG NULL देता है जब तक supported default argument न हो।

Named Window Reuse करें

SELECT student_name, class_name, marks,
       SUM(marks) OVER w AS class_total,
       ROUND(AVG(marks) OVER w, 2) AS class_average
FROM advanced_scores
WINDOW w AS (PARTITION BY class_name)
ORDER BY class_name, student_id;

WINDOW clause same specification repeat होने से बचाती है। Meaningful names लें और share करने से पहले confirm करें functions को identical ordering/frame semantics चाहिए।

Outer Query में Window Result Filter करें

Window functions same query level पर WHERE के बाद evaluate होती हैं, इसलिए two-stage form चाहिए:

WITH scored AS (
  SELECT student_id, student_name,
         class_name, marks,
         AVG(marks) OVER (
           PARTITION BY class_name
         ) AS class_average
  FROM advanced_scores
)
SELECT student_name, class_name, marks
FROM scored
WHERE marks > class_average
ORDER BY class_name, student_id;

गलतियाँ, Performance और अभ्यास

• Window ORDER BY final display order guarantee नहीं करता।
• Row-sensitive calculations में deterministic ordering key लें।
• Running/moving logic में frames explicitly लिखें।
• Window PARTITION BY को physical table partitioning न समझें।
• Different window sorts extra work माँग सकती हैं; EXPLAIN देखें।

अभ्यास: global और class averages साथ; two-row moving average; LEAD से next marks; CTE से above-average filter; tied ORDER BY values का default frame effect समझाएँ।

Official संदर्भ

• MySQL 8.4: Window Functions
• MySQL 8.4: Window Concepts and Syntax
• MySQL 8.4: Window Frame Specification

Syntax और behavior official MySQL 8.4 manual से जाँचे गए हैं। Production से पहले अपने server पर plans और limits verify करें।