Unbounded Frames in Time Analysis

Perform calculations that span the entire partition regardless of current row position.

Unbounded Frames in Time Analysis

In this lesson, you will learn how to use "Unbounded Frames" to force a window function to consider every single row in a partition, regardless of where your current row sits in the sequence.

The Problem with Default Windows

Usually, when you use a window function like SUM() over an ordered set (like ORDER BY date), SQL defaults to a "cumulative" frame. This means for any given row, it only calculates the sum from the start of the partition up to the current row. But what if you want to compare your current row to the entire dataset in that partition?

The "Unbounded" Solution

An Unbounded Frame removes the restriction that stops the calculation at the current row. By using the keywords BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, you tell the database engine to look at the beginning of the partition and the very end, effectively ignoring the current row's position.

Consider a scenario where you want to calculate the total sales for a store across all time, while still displaying each individual transaction row.

SELECT 
    transaction_date,
    sale_amount,
    SUM(sale_amount) OVER (
        PARTITION BY store_id 
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS total_store_sales
FROM sales;

Why use this?

When you define the frame as UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the SUM (or AVG, MIN, MAX) will yield the exact same value for every single row within that store_id partition. This is incredibly powerful for "relative analysis."

For example, you can calculate the percentage of a total sale for an individual item by dividing the sale_amount by this total:

SELECT 
    transaction_date,
    sale_amount / SUM(sale_amount) OVER (PARTITION BY store_id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) * 100 AS percent_of_total
FROM sales;

By breaking the "current row" constraint, you transform a window function from a tool that tracks progress into a tool that provides global context.

Key Takeaway: An Unbounded Frame tells the database to ignore the current row's position and perform calculations across every row within the partition.