Basic Frame Types - ROWS vs RANGE Comparison

intermediate

Understanding the fundamental difference between ROWS and RANGE frame types

-- Sample data with duplicate dates to show ROWS vs RANGE difference

WITH daily_sales AS (

SELECT * FROM VALUES

('2024-01-01'::date, 'Store A', 100),

('2024-01-01'::date, 'Store B', 150), -- Same date as above

('2024-01-02'::date, 'Store A', 200),

('2024-01-03'::date, 'Store A', 120),

('2024-01-03'::date, 'Store B', 180) -- Same date as above

AS t(sale_date, store, amount)

)

SELECT

sale_date,

store,

amount,

-- ROWS: Physical row counting (most common)

SUM(amount) OVER (

ORDER BY sale_date, store

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) as rows_2row_sum,

-- RANGE: Logical value-based (includes ties)

SUM(amount) OVER (

ORDER BY sale_date

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as range_running_sum,

-- Show the difference clearly

COUNT(*) OVER (

ORDER BY sale_date, store

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) as rows_count,

COUNT(*) OVER (

ORDER BY sale_date

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as range_count,

-- Practical example: Moving average with ROWS

ROUND(AVG(amount) OVER (

ORDER BY sale_date, store

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW