Window Aggregate Function

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 FOLLOWING

Basic 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