In this lesson, you will learn how the "Analytical Frame" acts as a virtual window that isolates specific subsets of data for localized calculation without collapsing your result set.
Beyond Grouping
When we use GROUP BY, we collapse rows into a single summary line. The "Analytical Frame" is fundamentally different: it allows you to look at a group of rows—a "window"—while keeping every individual row in your output. Think of the frame as a sliding viewport that moves over your data, capturing a specific range of rows to perform a calculation before sliding to the next.
Defining the Scope
The frame is defined by two boundaries: the Start and the End. By default, if you partition your data, the frame usually looks at all rows within that partition. However, you can narrow this scope. You might define a frame that only considers the "current row and the two rows preceding it," or "everything from the beginning of the partition up to the current row."
Imagine a table of daily sales. To calculate a "3-day moving average," your Analytical Frame is a window of size 3 that slides forward one day at a time:
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg
FROM DailySales;
Why the Frame Matters
By adjusting the frame, you change the "perspective" of the calculation.
- A "Unbounded" Frame: Looks at the entire history of the partition (useful for running totals).
- A "Fixed" Frame: Looks at a specific relative distance from the current row (useful for smoothing out volatility in time-series data).
The Analytical Frame turns your raw dataset into a dynamic workspace. Unlike basic aggregations that discard the underlying grain of your data, the frame allows you to preserve the granularity of your original rows while simultaneously embedding complex, context-aware insights from the surrounding rows in the set.
Key Takeaway: The Analytical Frame is a sliding subset of data that allows you to calculate row-specific insights based on the context of neighboring rows without collapsing the original result set.