MySQL Window Functions Tutorial (OVER, PARTITION BY)

Window functions, added in MySQL 8, compute values across a set of related rows without collapsing them like GROUP BY does. They are the single biggest upgrade to analytical SQL — running totals, rankings, and period-over-period comparisons all become one-liners.

Key Points

• Anatomy: function() OVER (PARTITION BY dept ORDER BY salary)
• ROW_NUMBER, RANK, DENSE_RANK create rankings within groups
• LAG/LEAD access previous/next rows — perfect for month-over-month change
• Running totals: SUM(amount) OVER (ORDER BY order_date)
• Moving averages with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
• Unlike GROUP BY, every original row stays in the output