SQL - ROWS vs RANGE Frame Specification
• ROWS defines window frames by physical row positions, while RANGE groups logically equivalent rows based on value proximity within the ORDER BY column
Key Insights
• ROWS defines window frames by physical row positions, while RANGE groups logically equivalent rows based on value proximity within the ORDER BY column • RANGE with numeric columns requires BETWEEN syntax and creates frames including all rows within the specified value distance, not row count • ROWS provides predictable performance and simpler semantics for most analytical queries, while RANGE handles tied values and value-based windows differently
Understanding Window Frame Specifications
Window functions in SQL operate over a subset of rows called a frame. The frame specification determines which rows are included in calculations for each row’s window function result. Two primary frame types exist: ROWS and RANGE, each defining frames fundamentally differently.
ROWS counts physical rows relative to the current row. RANGE groups rows by logical value equivalence within the ORDER BY column. This distinction becomes critical when dealing with duplicate values, calculating rolling aggregates, or implementing business logic that depends on value proximity rather than row position.
Basic ROWS Frame Behavior
ROWS frames count physical row positions. When you specify ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, you get exactly three rows: two before the current row and the current row itself.
CREATE TABLE sales (
sale_date DATE,
amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 150),
('2024-01-04', 200),
('2024-01-05', 175);
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3day_rows
FROM sales
ORDER BY sale_date;
Results:
sale_date | amount | rolling_3day_rows
2024-01-01 | 100 | 100
2024-01-02 | 150 | 250
2024-01-03 | 150 | 400
2024-01-04 | 200 | 500
2024-01-05 | 175 | 525
Each row’s frame contains exactly the specified number of physical rows. The first row has only itself, the second has two rows, and from the third onward, each frame contains three rows.
Basic RANGE Frame Behavior
RANGE frames group rows by value equivalence. Without explicit bounds, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows with ORDER BY values less than or equal to the current row’s value.
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY amount
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_range
FROM sales
ORDER BY amount;
Results:
sale_date | amount | cumulative_range
2024-01-01 | 100 | 100
2024-01-02 | 150 | 400
2024-01-03 | 150 | 400
2024-01-05 | 175 | 575
2024-01-04 | 200 | 775
Notice both rows with amount 150 show 400. RANGE treats them as peers—both frames include all rows with values ≤ 150, resulting in identical calculations.
RANGE With Value-Based Offsets
RANGE becomes powerful when specifying value-based offsets rather than row counts. This requires numeric or date ORDER BY columns and explicit BETWEEN syntax.
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY amount
RANGE BETWEEN 25 PRECEDING AND 25 FOLLOWING
) AS avg_nearby_amounts
FROM sales
ORDER BY amount;
Results:
sale_date | amount | avg_nearby_amounts
2024-01-01 | 100 | 125.00
2024-01-02 | 150 | 158.33
2024-01-03 | 150 | 158.33
2024-01-05 | 175 | 175.00
2024-01-04 | 200 | 187.50
For the row with amount 100, the frame includes amounts between 75 and 125 (only itself). For amount 150, the frame includes amounts 125-175 (both 150s and 175). This creates value-proximity windows impossible with ROWS.
Date-Based RANGE Windows
RANGE excels with temporal data when you need windows based on time intervals rather than row counts.
CREATE TABLE metrics (
metric_time TIMESTAMP,
value INTEGER
);
INSERT INTO metrics VALUES
('2024-01-01 10:00:00', 10),
('2024-01-01 10:05:00', 15),
('2024-01-01 10:12:00', 20),
('2024-01-01 10:18:00', 25),
('2024-01-01 10:25:00', 30);
SELECT
metric_time,
value,
AVG(value) OVER (
ORDER BY metric_time
RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
) AS avg_last_10min
FROM metrics
ORDER BY metric_time;
Results:
metric_time | value | avg_last_10min
2024-01-01 10:00:00 | 10 | 10.00
2024-01-01 10:05:00 | 15 | 12.50
2024-01-01 10:12:00 | 20 | 17.50
2024-01-01 10:18:00 | 25 | 22.50
2024-01-01 10:25:00 | 30 | 27.50
Each frame includes all rows within 10 minutes of the current row’s timestamp. This automatically adjusts for irregular time intervals—something ROWS cannot accomplish without complex subqueries.
Handling Duplicates and Ties
The ROWS vs RANGE distinction becomes critical with duplicate ORDER BY values.
CREATE TABLE scores (
player VARCHAR(50),
score INTEGER
);
INSERT INTO scores VALUES
('Alice', 100),
('Bob', 150),
('Carol', 150),
('Dave', 150),
('Eve', 200);
SELECT
player,
score,
ROW_NUMBER() OVER (ORDER BY score) AS row_num_rows,
RANK() OVER (ORDER BY score) AS rank_range,
COUNT(*) OVER (
ORDER BY score
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS count_rows,
COUNT(*) OVER (
ORDER BY score
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS count_range
FROM scores
ORDER BY score, player;
Results:
player | score | row_num_rows | rank_range | count_rows | count_range
Alice | 100 | 1 | 1 | 1 | 1
Bob | 150 | 2 | 2 | 2 | 4
Carol | 150 | 3 | 2 | 3 | 4
Dave | 150 | 4 | 2 | 4 | 4
Eve | 200 | 5 | 5 | 5 | 5
With ROWS, each physical row gets a unique count. With RANGE, all rows with score 150 show count 4 because their frames include all rows with scores ≤ 150.
Performance Considerations
ROWS frames generally perform better because they operate on fixed physical positions. Database optimizers can predict frame sizes and memory requirements.
-- More efficient: fixed frame size
SELECT
sale_date,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS weekly_total_rows
FROM sales;
-- Potentially less efficient: variable frame size
SELECT
sale_date,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) AS weekly_total_range
FROM sales;
RANGE with value-based offsets requires value comparisons for every row, potentially scanning more data. However, RANGE provides correct semantics when you need value-based windows—performance shouldn’t override correctness.
Practical Application: Moving Averages
For time-series analysis, choose based on your requirements:
-- Stock prices with irregular timestamps
CREATE TABLE stock_prices (
price_time TIMESTAMP,
price DECIMAL(10,2)
);
-- Last 5 trades (ROWS)
SELECT
price_time,
price,
AVG(price) OVER (
ORDER BY price_time
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS ma_5_trades
FROM stock_prices;
-- Last 5 minutes (RANGE)
SELECT
price_time,
price,
AVG(price) OVER (
ORDER BY price_time
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
) AS ma_5_minutes
FROM stock_prices;
Use ROWS when you want exactly N observations. Use RANGE when you need a specific time window regardless of how many observations fall within it.
Default Behavior
Without explicit frame specification, the default depends on whether you include ORDER BY:
-- Default: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SELECT SUM(amount) OVER (ORDER BY sale_date) FROM sales;
-- Equivalent explicit form
SELECT SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) FROM sales;
This default causes unexpected behavior with duplicate ORDER BY values. Always specify frame bounds explicitly for production code to avoid ambiguity and ensure consistent results across database systems.