Window Functions in MySQL

Window Functions Keep Detail Rows

A window function calculates over rows related to the current row but does not collapse them. GROUP BY produces one summary row per group; a window aggregate can attach the same group summary to every detail row.

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.

Class Totals and Averages Without Collapsing Rows

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;

All six detail rows remain. Without PARTITION BY, SUM and AVG would use one global six-row partition.

Running Total with an Explicit Frame

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;

The frame begins at the partition's first row and ends at the current row. Explicit ROWS gives physical row-by-row accumulation. Default frames can treat peers with equal window ORDER BY values together, so do not leave frame meaning implicit in financial or marks reports.

Compare with the Previous Row Using LAG

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 reads an earlier row without a self join. LEAD reads a later row. The first row in each partition has no predecessor, so LAG returns NULL unless a supported default argument is supplied.

Reuse a Named Window

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;

A WINDOW clause prevents repeating the same specification. Use meaningful names and confirm whether functions need identical ordering/frame semantics before sharing one definition.

Filter a Window Result in an Outer Query

Window functions are evaluated after WHERE at the same query level, so this two-stage form is required:

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;

Mistakes, Performance and Practice

• Window ORDER BY does not guarantee final display order.
• Use a deterministic ordering key for row-sensitive calculations.
• State frames explicitly when running or moving logic matters.
• Do not confuse window PARTITION BY with physical table partitioning.
• Multiple different window sorts can require extra work; inspect EXPLAIN.

Practice: calculate global and class averages together; produce a two-row moving average; use LEAD to show next marks; filter above-average rows through a CTE; explain how tied ORDER BY values affect a default frame.

Official References

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

Syntax and behavior were checked against the official MySQL 8.4 manual. Verify plans and limits on your own server before production use.