Understanding MySQL Window Functions: From Basics to Row Number, Rank, and Dense Rank

Introduction

Window functions in MySQL let you perform calculations across a set of rows (a window) while keeping each row’s original data intact. Unlike GROUP BY, which collapses rows into a single summary row, window functions preserve the row‑level detail and add computed columns alongside it.

Comparing GROUP BY and Window Functions

• GROUP BY aggregates rows, returning one row per group (e.g., average salary per gender).
• Window function (AVG(salary) OVER ()) computes the same aggregate but repeats the result on every row, so you still see each employee’s name, gender, etc.
• Adding PARTITION BY gender makes the window function behave like a grouped aggregate without collapsing rows, giving you the average salary per gender while still showing each employee.

Practical Examples

1. Average Salary per Gendersql SELECT gender, AVG(salary) OVER (PARTITION BY gender) AS avg_salary FROM demographics d JOIN salary s ON d.employee_id = s.employee_id;
2. Returns each employee with the average salary of their gender.
3. Sum of Salaries per Gendersql SELECT gender, SUM(salary) OVER (PARTITION BY gender) AS total_salary FROM ...;
4. Rolling Total (Cumulative Sum)sql SELECT gender, employee_id, salary, SUM(salary) OVER (PARTITION BY gender ORDER BY employee_id) AS rolling_total FROM ...;
5. Starts with the first employee in each gender partition and adds each subsequent salary, producing a running total.

Row‑Based Window Functions

Example: Ranking Salaries Within Each Gender

SELECT gender,
       first_name,
       salary,
       ROW_NUMBER() OVER (PARTITION BY gender ORDER BY salary DESC) AS row_num,
       RANK()        OVER (PARTITION BY gender ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (PARTITION BY gender ORDER BY salary DESC) AS dense_rank_num
FROM demographics d
JOIN salary s ON d.employee_id = s.employee_id;

• ROW_NUMBER gives a unique position (1,2,3…) regardless of ties.
• RANK assigns the same number to equal salaries and leaves a gap after the tie.
• DENSE_RANK assigns the same number to ties but continues with the next integer.

Why Window Functions Matter

• Preserve Detail: Keep original columns while adding analytics.
• Flexibility: Combine multiple calculations (average, sum, cumulative) in a single query.
• Performance: Often more efficient than multiple sub‑queries or temporary tables.
• Advanced Analytics: Essential for financial reporting, time‑series analysis, and ranking scenarios.

Quick Recap of Syntax

SELECT <columns>,
       <aggregate_function>(<expression>) OVER (
           [PARTITION BY <col1>, <col2>, ...]
           [ORDER BY <col> [ASC|DESC]]
           [ROWS BETWEEN ...]
       ) AS <alias>
FROM <tables>;

• PARTITION BY works like a virtual GROUP BY.
• ORDER BY defines the order for ranking or cumulative calculations.
• Optional ROWS BETWEEN lets you define sliding windows (e.g., last 3 rows).

Conclusion

Window functions give you the power of aggregation and row‑level visibility in a single, elegant query. By mastering OVER, PARTITION BY, and ordering clauses, you can replace many cumbersome sub‑queries with clear, maintainable SQL that supports advanced analytics such as rolling totals, ranking, and dense ranking.

Window functions let you compute aggregates, running totals, and rankings while preserving each row’s original data, making them indispensable for modern data analysis in MySQL.