SQL 61 🛢️ Defining Window Frames: ROWS and RANGE
A window function computes a value across a set of rows related to the current row. The OVER clause defines that set. In the simplest form, the set is the entire partition, and the function sees every row in the group. But many analytical questions require a more precise definition: “the running total so far,” “the average of the last three rows,” “the difference from the first row in the group.” These questions are about a window frame — a subset of the partition defined relative to the current row. SQL provides two ways to define that subset: ROWS and RANGE.
The distinction between ROWS and RANGE is the distinction between physical position and logical value. ROWS counts rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row and the two rows physically before it. RANGE counts values: RANGE BETWEEN 2 PRECEDING AND CURRENT ROW means all rows whose ordering value is within 2 of the current row’s value. When the ordering column has unique values, the two modes often produce the same result. When it has duplicates — and it often does — they diverge. Understanding the divergence is the key to using window frames correctly.
The ROWS mode is the default in some databases and not in others. PostgreSQL, MySQL, and SQL Server treat the absence of a frame clause differently depending on whether an ORDER BY is present. When ORDER BY is present, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same ordering value as the current row. This is why a running sum over a date column with duplicate dates produces the same value for all rows on the same date — the implicit RANGE frame includes them all. Adding an explicit ROWS frame changes the behavior to physical counting, which is often what the analyst actually wants .
This chapter covers three areas. First, why window frames exist — the difference between a partition and a frame, and the analytical questions that require frame definition. Second, how ROWS and RANGE work — the frame boundary syntax, the PRECEDING/FOLLOWING/CURRENT ROW keywords, and the default frames. Third, how the two modes diverge — the treatment of duplicates, the interaction with ORDER BY, and the performance and portability considerations. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the frame boundary mechanics.
Key point: A window frame is a subset of the partition, defined relative to the current row. ROWS defines the frame by physical row position. RANGE defines it by value range in the ORDER BY column. When the ordering column has duplicates, ROWS and RANGE produce different results. When ORDER BY is present without an explicit frame, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .
Why window frames exist
The partition-versus-frame problem. A window function operates over a partition — a set of rows grouped by the PARTITION BY clause. But within a partition, not every question is about the whole group. A running total is about the rows from the start of the partition up to the current row. A moving average is about a fixed number of rows around the current row. A difference-from-previous is about the current row and the one before it. These questions require a frame — a subset of the partition defined relative to the current row. Without a frame, the function sees the entire partition, and the answer is the same for every row .
The running-total problem. The classic example is a running total. A query that sums sales by day, partitioned by region and ordered by date, needs the sum to accumulate. The frame for the first day is just that day; for the second day, it is the first and second days; for the third, the first three. This is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or more commonly, ROWS UNBOUNDED PRECEDING. The frame grows as the current row advances through the partition .
The moving-average problem. A moving average smooths a series by averaging a fixed window around each point. A 7-day moving average uses the current row and the six rows before it. The frame is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. When the current row is at the start of the partition, there are fewer than six preceding rows, and the average is computed over whatever rows exist. The frame boundary is clamped to the partition edges .
The duplicate-value problem. When the ordering column has duplicate values, ROWS and RANGE diverge. Consider a table with sales on the same date. With ORDER BY date and the implicit RANGE frame, a running sum for a row on January 1 includes all rows on January 1, because the RANGE frame includes all rows with the same ordering value. With ROWS UNBOUNDED PRECEDING, the running sum includes only the rows physically before the current row, so the first row on January 1 has a smaller sum than the last row on January 1. Both are valid; they answer different questions .
The default-frame problem. The default frame depends on whether ORDER BY is present. Without ORDER BY, the frame is the entire partition. With ORDER BY, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default is often surprising: a running sum without an explicit frame behaves like a RANGE frame, which means duplicate ordering values are all included. Adding an explicit ROWS frame is the standard fix when physical row order is what matters .
The trade-off. ROWS is simpler and more predictable. It counts rows, and its behavior does not depend on the values in the ordering column. RANGE is more expressive for value-based questions — “all rows within 10 units of the current row” — but it requires the ordering column to be sortable and comparable. RANGE also has restrictions: it supports only UNBOUNDED, CURRENT ROW, and numeric offsets with PRECEDING/FOLLOWING, and the offset must be a constant. ROWS supports any expression that evaluates to a non-negative integer. The trade-off is between physical precision and logical range.
a. The frame clause syntax
The frame clause appears inside the OVER clause, after PARTITION BY and ORDER BY. The syntax is:
OVER (
PARTITION BY ...
ORDER BY ...
{ ROWS | RANGE } BETWEEN <start> AND <end>
)The <start> and <end> boundaries can be:
UNBOUNDED PRECEDING— the first row of the partition.N PRECEDING— N rows (forROWS) or N units (forRANGE) before the current row.CURRENT ROW— the current row (forROWS) or the current value (forRANGE).N FOLLOWING— N rows or units after the current row.UNBOUNDED FOLLOWING— the last row of the partition.
The shorthand ROWS UNBOUNDED PRECEDING is equivalent to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The shorthand is common for running totals .
The frame must satisfy two rules: the start boundary cannot be after the end boundary, and UNBOUNDED FOLLOWING cannot be a start boundary, UNBOUNDED PRECEDING cannot be an end boundary. The frame is always relative to the current row, and it is clamped to the partition boundaries. A frame of 3 PRECEDING for the second row of a partition includes only the first row, because there is no third row before it .
A frame without BETWEEN uses the shorthand form:
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)This is equivalent to:
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)b. ROWS — physical row position
ROWS defines the frame by counting rows. The boundaries are row offsets, and the ordering column’s values do not affect the frame.
SELECT
date,
amount,
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_sum
FROM sales;This computes, for each row, the sum of the current row and the two rows before it. The frame is always three rows (or fewer at the partition edges). If the ordering column has duplicate values, the frame still counts three physical rows; the duplicates are treated as separate rows .
ROWS is the mode to use when the question is about physical order. “The last three transactions” is a ROWS question. “The running total” is also a ROWS question when the order is unambiguous. “The previous row’s value” is ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING, or LAG(value, 1).
The frame can also be forward-looking:
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
) AS forward_sumThis sums the current row and the next two rows. Forward-looking frames are useful for lead-time analysis and for computing values that depend on future rows.
c. RANGE — logical value range
RANGE defines the frame by value. The boundaries are offsets in the ordering column’s units, and all rows whose ordering value falls within the range are included.
SELECT
date,
amount,
SUM(amount) OVER (
ORDER BY date
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
) AS sum_last_2_days
FROM sales;This sums all rows whose date is within the last two days of the current row’s date. If multiple rows share the same date, all of them are included. The frame is defined by the date value, not by the row position .
RANGE requires the ordering column to be numeric, date, or timestamp — a type that supports addition and subtraction. The offset must be a constant, and the PRECEDING/FOLLOWING keywords only work with numeric offsets. UNBOUNDED and CURRENT ROW work with any ordering column.
The most common use of RANGE is the implicit frame. When ORDER BY is present without an explicit frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means all rows with the same ordering value as the current row are included. For a running sum over a date column with duplicates, every row on the same date gets the same sum — the sum of all rows up to and including that date .
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;If two rows share date = '2024-01-01', both get the same running_total, which includes both rows. This is the RANGE behavior. To get a true physical running total, add ROWS UNBOUNDED PRECEDING.
RANGE does not support N PRECEDING or N FOLLOWING with a non-numeric offset. It also does not support ROWS-style offsets when the ordering column is a string or a type without arithmetic. This makes RANGE less general than ROWS, but more precise for value-based questions.
Complete Example Session
-- ============================================
-- PART 1: THE SAMPLE TABLE
-- ============================================
CREATE TABLE sales (
id INT,
sale_date DATE,
region TEXT,
amount NUMERIC
);
INSERT INTO sales VALUES
(1, '2024-01-01', 'North', 100),
(2, '2024-01-01', 'North', 150),
(3, '2024-01-02', 'North', 200),
(4, '2024-01-03', 'North', 50),
(5, '2024-01-01', 'South', 300),
(6, '2024-01-02', 'South', 100);
-- ============================================
-- PART 2: RUNNING TOTAL WITH ROWS
-- ============================================
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales
WHERE region = 'North';
-- Each row gets the sum of all rows up to and including it.
-- The frame is physical: row 1 gets 100, row 2 gets 250, row 3 gets 450.
-- ============================================
-- PART 3: RUNNING TOTAL WITH RANGE
-- ============================================
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales
WHERE region = 'North';
-- Rows with the same sale_date get the same total.
-- Both 2024-01-01 rows get 250 (100 + 150).
-- The 2024-01-02 row gets 450 (100 + 150 + 200).
-- ============================================
-- PART 4: THE IMPLICIT RANGE FRAME
-- ============================================
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
WHERE region = 'North';
-- The implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
-- Same result as PART 3.
-- ============================================
-- PART 5: MOVING AVERAGE WITH ROWS
-- ============================================
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg
FROM sales
WHERE region = 'North';
-- Each row gets the average of itself and its immediate neighbors.
-- Row 1: avg(100, 150) = 125
-- Row 2: avg(100, 150, 200) = 150
-- Row 3: avg(150, 200, 50) = 133.33
-- ============================================
-- PART 6: RANGE WITH A NUMERIC OFFSET
-- ============================================
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW
) AS sum_last_day
FROM sales
WHERE region = 'North';
-- Sums all rows within 1 day of the current row's date.
-- Row 2024-01-02: includes 2024-01-01 (within 1 day) and itself.
-- ============================================
-- PART 7: LAG AND LEAD AS FRAME ALTERNATIVES
-- ============================================
SELECT
sale_date,
amount,
LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM sales
WHERE region = 'North';
-- LAG and LEAD are specialized window functions that
-- access a specific row relative to the current row.
-- They are often simpler than a ROWS frame for
-- "previous row" and "next row" questions.
-- ============================================
-- PART 8: FIRST AND LAST VALUE IN A FRAME
-- ============================================
SELECT
sale_date,
amount,
FIRST_VALUE(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS first_amount,
LAST_VALUE(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS last_amount
FROM sales
WHERE region = 'North';
-- FIRST_VALUE returns the first row in the frame.
-- LAST_VALUE returns the last row in the frame.
-- The frame definition determines which row is "first" or "last."
-- ============================================
-- PART 9: FRAME WITH PARTITION BY
-- ============================================
SELECT
region,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_by_region
FROM sales;
-- The partition restarts the frame for each region.
-- The running total is computed independently for North and South.
-- ============================================
-- PART 10: THE COMPLETE COMPARISON
-- ============================================
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_running_total,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS range_running_total
FROM sales
WHERE region = 'North';
-- The two columns differ when there are duplicate dates.
-- ROWS counts physical rows; RANGE includes all rows with
-- the same ordering value.The ten parts show the sample table, running total with ROWS, running total with RANGE, the implicit RANGE frame, moving average with ROWS, RANGE with a numeric offset, LAG and LEAD as alternatives, FIRST_VALUE and LAST_VALUE, frame with PARTITION BY, and the complete comparison.
Quick Reference
Frame Boundary Keywords
| Keyword | Meaning |
|---|---|
UNBOUNDED PRECEDING | First row of the partition |
N PRECEDING | N rows/units before the current row |
CURRENT ROW | The current row (ROWS) or value (RANGE) |
N FOLLOWING | N rows/units after the current row |
UNBOUNDED FOLLOWING | Last row of the partition |
Frame Syntax
| Form | Meaning |
|---|---|
ROWS BETWEEN ... AND ... | Physical row offsets |
RANGE BETWEEN ... AND ... | Value-range offsets |
ROWS UNBOUNDED PRECEDING | Shorthand for running total |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | Moving window of 3 rows |
Default Frames
| Situation | Default frame |
|---|---|
No ORDER BY | Entire partition |
ORDER BY present | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
Explicit ROWS | As specified |
ROWS vs RANGE
| Aspect | ROWS | RANGE |
|---|---|---|
| Definition | Physical row position | Value range |
| Duplicates | Treated as separate rows | Included together |
| Offset type | Integer (rows) | Numeric (units) |
| Requires ORDER BY | No (but recommended) | Yes |
| Common use | Running total, moving average | Value-based windows |
Common Frame Patterns
| Pattern | Syntax |
|---|---|
| Running total | ROWS UNBOUNDED PRECEDING |
| Moving average (3 rows) | ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| Last 7 rows | ROWS BETWEEN 6 PRECEDING AND CURRENT ROW |
| First row to current | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| Current to last row | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
Best Practices
✅ Do This:
-- Use ROWS for physical row order
SUM(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) -- ✅
-- Use RANGE for value-based windows
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) -- ✅
-- Be explicit about the frame
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- ✅
-- Use LAG/LEAD for previous/next row
LAG(amount, 1) OVER (ORDER BY sale_date) -- ✅
-- Use PARTITION BY to restart the frame
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS UNBOUNDED PRECEDING) -- ✅❌ Don’t Do This:
-- Don't assume the default frame is ROWS
SUM(amount) OVER (ORDER BY sale_date) -- implicit RANGE frame -- ❌
-- Don't use RANGE with a string ordering column
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW -- requires numeric/date -- ❌
-- Don't use non-constant offsets in RANGE
RANGE BETWEEN n PRECEDING AND CURRENT ROW -- n must be constant -- ❌
-- Don't forget that LAST_VALUE needs an explicit frame
LAST_VALUE(amount) OVER (ORDER BY sale_date) -- default frame stops at current row -- ❌
-- Don't use ROWS when the question is value-based
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW -- if duplicates matter -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Running total wrong with duplicates | Default RANGE frame includes all duplicates | Use ROWS UNBOUNDED PRECEDING |
RANGE with non-numeric offset | Offset must be constant and numeric | Use ROWS or a numeric column |
LAST_VALUE returns current row | Default frame ends at current row | Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| Moving average includes wrong rows | Frame offset off by one | Check N PRECEDING and N FOLLOWING |
| Frame not clamped at partition edge | Expecting N rows when fewer exist | Frames are clamped to the partition |
RANGE requires ORDER BY | No ordering column to compare | Add ORDER BY |
| Performance issues with large frames | Large frame scans many rows | Narrow the frame or use an index |
Real-World Examples
1. Running Total
SUM(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING)2. Moving Average
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)3. Difference from First
amount - FIRST_VALUE(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING)4. Previous Row
LAG(amount, 1) OVER (ORDER BY sale_date)5. Next Row
LEAD(amount, 1) OVER (ORDER BY sale_date)6. Value-Based Window
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)7. Partitioned Running Total
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS UNBOUNDED PRECEDING)8. Full Partition Sum
SUM(amount) OVER (PARTITION BY region)9. Last Value in Partition
LAST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)10. Centered Moving Average
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)Visual
The Frame Concept
┌──────────────────────────────────────────────────────────────┐
│ THE FRAME CONCEPT │
│ │
│ Partition (ORDER BY sale_date): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Row 1: 2024-01-01 100 │ │
│ │ Row 2: 2024-01-01 150 │ │
│ │ Row 3: 2024-01-02 200 │ │
│ │ Row 4: 2024-01-03 50 │ │
│ │ Row 5: 2024-01-04 300 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ For Row 3, the frame depends on the mode: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW │ │
│ │ → Rows 1, 2, 3 │ │
│ │ │ │
│ │ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW │ │
│ │ → Rows 1, 2, 3 (same as ROWS here, no duplicates │ │
│ │ in the ordering column for these rows) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ For Row 2 (duplicate date with Row 1): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW │ │
│ │ → Rows 1, 2 (sum = 250) │ │
│ │ │ │
│ │ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW │ │
│ │ → Rows 1, 2 (sum = 250) │ │
│ │ │ │
│ │ For Row 1: │ │
│ │ ROWS: Row 1 only (sum = 100) │ │
│ │ RANGE: Rows 1, 2 (sum = 250) ← includes duplicate│ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘ROWS vs RANGE with Duplicates
┌──────────────────────────────────────────────────────────────┐
│ ROWS vs RANGE WITH DUPLICATES │
│ │
│ Data: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ sale_date amount │ │
│ │ 2024-01-01 100 │ │
│ │ 2024-01-01 150 │ │
│ │ 2024-01-02 200 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ROWS UNBOUNDED PRECEDING: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ 2024-01-01 100 → 100 │ │
│ │ 2024-01-01 150 → 250 │ │
│ │ 2024-01-02 200 → 450 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ RANGE UNBOUNDED PRECEDING (or implicit): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ 2024-01-01 100 → 250 ← includes the duplicate │ │
│ │ 2024-01-01 150 → 250 ← same value │ │
│ │ 2024-01-02 200 → 450 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The difference: RANGE includes all rows with the same │
│ ordering value; ROWS counts physical rows. │
│ │
└──────────────────────────────────────────────────────────────┘Frame Boundaries
┌──────────────────────────────────────────────────────────────┐
│ FRAME BOUNDARIES │
│ │
│ ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING │
│ │
│ Partition: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Row 1 │ │
│ │ Row 2 │ │
│ │ Row 3 ◄── CURRENT ROW │ │
│ │ Row 4 │ │
│ │ Row 5 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Frame for Row 3: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Row 1 ─┐ │ │
│ │ Row 2 ─┼── 2 PRECEDING │ │
│ │ Row 3 ─┤── CURRENT ROW │ │
│ │ Row 4 ─┘── 1 FOLLOWING │ │
│ │ Row 5 │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The frame includes Rows 1, 2, 3, 4. │
│ At the partition edges, the frame is clamped: │
│ for Row 1, "2 PRECEDING" does not exist. │
│ │
└──────────────────────────────────────────────────────────────┘Default Frame Behavior
┌──────────────────────────────────────────────────────────────┐
│ DEFAULT FRAME BEHAVIOR │
│ │
│ No ORDER BY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SUM(amount) OVER (PARTITION BY region) │ │
│ │ → frame is the ENTIRE partition │ │
│ │ → every row gets the same sum │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ With ORDER BY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SUM(amount) OVER (ORDER BY sale_date) │ │
│ │ → frame is RANGE BETWEEN UNBOUNDED PRECEDING │ │
│ │ AND CURRENT ROW │ │
│ │ → duplicate dates are included together │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Explicit ROWS: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SUM(amount) OVER (ORDER BY sale_date │ │
│ │ ROWS UNBOUNDED PRECEDING) │ │
│ │ → physical row order, duplicates separate │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Window frame | Subset of the partition relative to the current row |
ROWS | Physical row position |
RANGE | Value range in the ordering column |
UNBOUNDED PRECEDING | First row of the partition |
CURRENT ROW | The current row |
UNBOUNDED FOLLOWING | Last row of the partition |
Default with ORDER BY | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
Default without ORDER BY | Entire partition |
| Duplicates | ROWS treats separately; RANGE includes together |
| Running total | ROWS UNBOUNDED PRECEDING |
Key takeaways:
- A window frame is a subset of the partition. It is defined relative to the current row, and it determines which rows the window function sees. Without a frame, the function sees the entire partition, and the answer is the same for every row .
ROWScounts physical rows;RANGEcounts values.ROWS BETWEEN 2 PRECEDING AND CURRENT ROWincludes the current row and the two physical rows before it.RANGE BETWEEN 2 PRECEDING AND CURRENT ROWincludes all rows whose ordering value is within 2 of the current row’s value .- The two modes diverge when the ordering column has duplicates. With
ROWS, duplicate values are separate rows. WithRANGE, they are included together. This is why a running sum over a date column with duplicate dates produces the same value for all rows on the same date underRANGE, but different values underROWS. - The default frame depends on
ORDER BY. WithoutORDER BY, the frame is the entire partition. WithORDER BY, the frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default is often surprising, and the explicitROWS UNBOUNDED PRECEDINGis the standard fix when physical order is what matters . - The frame is clamped to the partition boundaries. A frame of
3 PRECEDINGfor the second row of a partition includes only the first row, because there is no third row before it. The frame never extends outside the partition . RANGErequires a numeric, date, or timestamp ordering column. The offset must be a constant, and thePRECEDING/FOLLOWINGkeywords only work with numeric offsets.ROWSis more general and supports any expression that evaluates to a non-negative integer .LAGandLEADare specialized alternatives for previous and next row. They are often simpler than aROWSframe for “the previous row’s value” and “the next row’s value.”FIRST_VALUEandLAST_VALUEare specialized for the frame’s first and last rows .- The frame is a performance consideration. A large frame scans many rows, and a frame with a wide value range can be expensive. Narrow frames and indexed ordering columns are the standard optimizations .
Remember: A window frame is the subset of the partition that a window function sees. ROWS defines it by physical position; RANGE defines it by value. The two modes produce the same result when the ordering column has unique values, and diverge when it has duplicates. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present, which is often surprising and often not what the analyst wants. The explicit ROWS UNBOUNDED PRECEDING is the standard pattern for a running total, and ROWS BETWEEN N PRECEDING AND M FOLLOWING is the standard pattern for a moving window. The frame is always relative to the current row, and it is always clamped to the partition boundaries.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!