Window functions, added in MySQL 8.0, enable powerful analytical queries that previously required complex self-joins or application-level processing.
What Are Window Functions?
Window functions compute a value for each row based on a set of related rows (the window), without collapsing rows like GROUP BY does.
ROW_NUMBER, RANK, DENSE_RANK
sql
-- Rank customers by total spend within each region
SELECT
customer_id, region, total_spend,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_spend DESC) AS row_num,
RANK() OVER (PARTITION BY region ORDER BY total_spend DESC) AS rank_pos,
DENSE_RANK() OVER (PARTITION BY region ORDER BY total_spend DESC) AS dense_rank
FROM customer_summary
ORDER BY region, total_spend DESC;Running Totals with SUM() OVER
sql
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM daily_sales
ORDER BY order_date;LAG and LEAD: Compare Rows
sql
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 2
) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;NTILE: Percentile Buckets
sql
-- Divide customers into quartiles by spend
SELECT
customer_id,
total_spend,
NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customers
ORDER BY total_spend DESC;FIRST_VALUE / LAST_VALUE
sql
-- For each order, show the customer's first and latest order amount
SELECT
customer_id, order_date, amount,
FIRST_VALUE(amount) OVER w AS first_order_amount,
LAST_VALUE(amount) OVER w AS latest_order_amount
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY customer_id, order_date;Key Takeaways
- Window functions do not collapse rows — each row retains its identity plus the computed window value
- Use
PARTITION BYto reset the window per group (like GROUP BY but without collapsing) - Named windows (
WINDOW w AS (...)) avoid repeating the same window definition - Window functions run after WHERE and GROUP BY — filter first to reduce the working set
JusDB Can Help
Window functions can replace complex self-joins and application-level aggregations. JusDB can rewrite your most expensive analytical queries using window functions.