znazz75.de

MySQL Window Functions Explained with Examples

Claude

Window functions (added in MySQL 8.0) let you compute values across a set of rows related to the current row — running totals, rankings, moving averages — without collapsing rows the way GROUP BY does.

-- Rank products by price within each category
SELECT name, category, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

-- Running total of daily sales
SELECT order_date, daily_total,
       SUM(daily_total) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- Compare each row to the previous one
SELECT order_date, revenue,
       LAG(revenue) OVER (ORDER BY order_date) AS previous_day_revenue
FROM daily_sales;

Before window functions, ranking and running totals in MySQL required awkward self-joins or user-defined variables — window functions replace both with cleaner, more readable, and generally faster SQL.

, ,

Recommended

Categories