Understanding Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row. This makes them invaluable for analytical queries.

Types of Window Functions

Category Functions Use Case
Ranking ROW_NUMBER(), RANK(), DENSE_RANK() Find top N products per category
Value LAG(), LEAD(), FIRST_VALUE() Compare sales with previous month
Aggregate SUM(), AVG() with OVER() Calculate running totals

Practical Example: Sales Analysis

SELECT 
    product_name,
    sale_date,
    amount,
    SUM(amount) OVER(PARTITION BY product_name ORDER BY sale_date) as running_total,
    LAG(amount, 1) OVER(PARTITION BY product_name ORDER BY sale_date) as prev_sale,
    AVG(amount) OVER(PARTITION BY product_name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM sales
WHERE sale_date >= '2024-01-01';

Performance Optimization

Window functions can be optimized by creating appropriate indexes on PARTITION BY and ORDER BY columns. Understanding execution plans is crucial for complex queries.