Status
Done
Text
Quick reference notes and key concepts for MySQL window functions implementation.
SQL Window Functions Study Guide - Sakila Database
Aggregate Window Functions
These functions perform calculations across a set of rows and return a single value for each row.
- SUM()
- Calculates the sum of values in a window.
- Example:
SUM(column) OVER (PARTITION BY group ORDER BY order_column) - AVG()
- Calculates the average of values in a window.
- Example:
AVG(column) OVER (PARTITION BY group ORDER BY order_column) - MIN()
- Finds the minimum value in a window.
- Example:
MIN(column) OVER (PARTITION BY group ORDER BY order_column) - MAX()
- Finds the maximum value in a window.
- Example:
MAX(column) OVER (PARTITION BY group ORDER BY order_column) - COUNT()
- Counts the number of rows in a window.
- Example:
COUNT(column) OVER (PARTITION BY group ORDER BY order_column)
What Are Window Functions?
Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions with GROUP BY (which collapse rows), window functions retain all individual rows while adding calculated values.
Key Components of Window Function Syntax:
<window_function>() OVER (
PARTITION BY column_name
ORDER BY column_name
frame_clause
)
Where the frame_clause
frame_clause ::=
frame_unit { frame_start or BETWEEN frame_boundary_1 AND frame_boundary_2 }
frame_unit ::= ROWS or RANGE
frame_start ::= -- single-boundary (shorthand) form
UNBOUNDED PRECEDING
or N PRECEDING
or CURRENT ROW
frame_boundary_1 ::= -- start boundary (beginning of frame)
UNBOUNDED PRECEDING
or N PRECEDING
or CURRENT ROW
or N FOLLOWING
frame_boundary_2 ::= -- end boundary (end of frame)
N PRECEDING
or CURRENT ROW
or N FOLLOWING
or UNBOUNDED FOLLOWINGBasic Aggregates
- SUM()
- Calculates sum of values
- Example: Running totals, cumulative sums
SUM(amount) OVER (PARTITION BY customer_id ORDER BY date)- AVG()
- Calculates average of values
- Example: Moving averages, mean calculations
AVG(salary) OVER (PARTITION BY department ORDER BY hire_date)
- MIN()
- Finds minimum value
- Example: Lowest price in category, earliest date
MIN(price) OVER (PARTITION BY category)- MAX()
- Finds maximum value
- Example: Highest score, latest date, peak values
MAX(revenue) OVER (PARTITION BY region ORDER BY month)
- COUNT()
- Counts rows (or non-NULL values)
- Example: Running count, rows in partition
COUNT(*) OVER (PARTITION BY status ORDER BY created_date)
‣
1. SUM() Window Function
‣
2. AVG() Window Function
‣
3. MIN() Window Function
‣
4. MAX() Window Function
‣
5. COUNT() Window Function
Common Patterns
- Running Total:
SUM(...) OVER (PARTITION BY ... ORDER BY ...) - Percentage of Total:
value / SUM(value) OVER (PARTITION BY ...) * 100 - Moving Average:
AVG(...) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW) - Rank Within Group: Compare individual values to MIN/MAX/AVG of partition
- Sequential Numbering:
COUNT(*) OVER (PARTITION BY ... ORDER BY ...)
Window Functions vs GROUP BY
Aspect | GROUP BY | Window Functions |
Row output | Collapses to aggregates | Retains all detail rows |
Calculation scope | Entire group | Flexible window |
Multiple aggregates | Limited options | Unlimited combinations |
Access to details | Lost | Preserved |
Practice Tips
- Start Simple: Begin with no ORDER BY to see partition totals
- Add ORDER BY: Observe how running calculations work
- Experiment with Frames: Try different ROWS/RANGE specifications
- Combine Functions: Use multiple window functions in same query
- Compare Results: Run with and without window functions to understand differences
Common Use Cases
- Financial: Running totals, moving averages, period-over-period comparisons
- Marketing: Customer segmentation, cohort analysis, funnel metrics
- Operations: Inventory levels, capacity utilization, queue lengths
- Analytics: Trend analysis, anomaly detection, forecasting inputs