In this lesson, you will learn how to treat rows with identical values—known as "peers"—as a single unit when calculating window functions.
The Problem of Ties
When you use a value-based frame, such as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the database engine looks at the ordering column to define the window. But what happens if two rows have the exact same value in that ordering column?
If you have a list of sales, and two employees sold exactly 500 row; it will include both rows simultaneously to ensure the result is consistent.
A Practical Example
Imagine you are calculating a running total of sales, but you want to ensure that "peers" receive the same cumulative sum:
SELECT
employee_name,
sale_amount,
SUM(sale_amount) OVER (
ORDER BY sale_amount
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM sales_data;
If two employees both have a sale_amount of 100, the RANGE clause ensures that the running_total for both rows includes the combined sum of both 100s. This prevents the "jitter" that occurs if you were using ROWS (which counts positions) instead of RANGE (which counts values).
Why It Matters for Performance
Because the database must identify all peers to calculate the window correctly, it relies heavily on the Merge Join and Sorting prerequisites you learned earlier. If the engine detects a range-based window, it knows it must "look ahead" or "look behind" to find all rows sharing that same value before finalizing the output for that partition. Treating peers as a block is a mathematical requirement for consistency, but it requires the engine to maintain those rows in memory until the entire peer group is processed.
Key Takeaway: In value-based windows, the RANGE clause groups identical values together, ensuring that all peers share the same aggregate result rather than treating them as distinct, sequential steps.