Aggregate Functions with Window Frames

Aggregate functions with window frames allow you to compute aggregate values (such as sums, averages, and counts) over a specific range of rows, called a frame, within a window of data. This feature is part of window functions in SQL and provides fine-grained control for complex analytical tasks.

Introduction to Aggregate Functions with Window Frames

What are Aggregate Functions?

Aggregate functions perform calculations across a set of rows, returning a single result. Common aggregate functions include:

  • SUM: Calculates the sum of values.
  • AVG: Computes the average of values.
  • COUNT: Counts rows.
  • MAX: Finds the maximum value.
  • MIN: Finds the minimum value.

What are Window Frames?

Window frames define a subset of rows within a window (partitioned dataset) over which an aggregate function operates. A window frame can be dynamic, based on conditions like the current row’s position, making it powerful for advanced analytics.

Window Frames Basics

What is a Window Frame?

A window frame is a range of rows relative to the current row. It can be bounded by specific positions or conditions.

Types of Window Frames

  1. ROWS Frame: Defines rows in terms of physical positions.
    • Example: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  2. RANGE Frame: Defines rows based on the value of the ordering column.
    • Example: RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
  3. GROUPS Frame: Defines rows based on peer groups (introduced in SQL:2016).
    • Example: GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

Syntax of Window Frames

The general syntax for using aggregate functions with window frames:

				
					aggregate_function(column_name) 
OVER (
    [PARTITION BY column_name]
    [ORDER BY column_name]
    [ROWS | RANGE | GROUPS BETWEEN frame_start AND frame_end]
)

Explanation:

  • PARTITION BY: Divides data into subsets for independent computation.
  • ORDER BY: Orders rows within each partition.
  • ROWS/RANGE/GROUPS: Specifies the frame type and bounds.

Supported Aggregate Functions

Common aggregate functions that support window frames:

  1. SUM()
  2. AVG()
  3. COUNT()
  4. MIN()
  5. MAX()

Detailed Examples

Example 1: Computing Running Totals

Query:

				
					SELECT EmployeeID, Name, Salary,
       SUM(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees;

Output:

EmployeeIDNameSalaryRunningTotal
1Alice50005000
2Bob600011000
3Charlie700018000

Explanation:

  • SUM(Salary) computes the running total.
  • The frame includes all rows from the start (UNBOUNDED PRECEDING) to the current row.

Example 2: Calculating Moving Averages

Query:

				
					SELECT EmployeeID, Name, Salary,
       AVG(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;

Output:

EmployeeIDNameSalaryMovingAvg
1Alice50005000.00
2Bob60005500.00
3Charlie70006500.00

Explanation:

  • The frame includes the current row and one row before (1 PRECEDING).
  • The AVG function calculates the moving average over the frame.

Example 3: Finding Maximum Value in Frame

Query:

				
					SELECT EmployeeID, Name, Salary,
       MAX(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MaxSalary
FROM Employees;

Output:

EmployeeIDNameSalaryMaxSalary
1Alice50005000
2Bob60006000
3Charlie70007000

Explanation:

  • The frame includes the current row and the two rows preceding it.
  • The MAX function computes the maximum salary in this range.

Advanced Window Frame Features

Exclusion Clauses

SQL allows excluding rows explicitly with the EXCLUDE clause

				
					SUM(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)

This excludes the current row from the frame.

Dynamic Frame Sizing

Frame bounds can use dynamic expressions for more flexibility:

				
					ROWS BETWEEN N PRECEDING AND M FOLLOWING

Here, N and M can be dynamic values.

Performance Considerations

  • Indexes: Ensure indexes exist on columns used in PARTITION BY and ORDER BY.
  • Frame Size: Larger frames can impact query performance, especially on large datasets.
  • Optimization: Use ROWS instead of RANGE for better performance in most cases.

Aggregate functions with window frames extend SQL's analytical capabilities by enabling fine-grained row-wise calculations. Whether you're calculating running totals, moving averages, or identifying ranges of values, window frames provide unmatched flexibility. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources