Introduction
ROWS
RANGE
13. RANGE explained
Default window frame
Revision

Instruction

Excellent! It's time to look at another type of window frame: RANGE.

The difference between ROWS and RANGE is that RANGE will take into account all rows that have the same value in the column which we order by. This might be helpful with dates. Consider the following problem: we want to calculate the running sum from all orders sorted by date. We could write something like this:

SELECT
  id,
  placed,
  total_price,
  SUM(total_price) OVER (ORDER BY placed ROWS UNBOUNDED PRECEDING)
FROM single_order;
Diagram A

And it works fine. But our boss could say: hey, I don't really need to see how the running sum changed during single days. Just show the values at the end of the day; if there are multiple orders on a single day, add them together.

The above may be implemented by changing ROWS to RANGE. Look how we highlighted the rows which now share the same running sum because they come from the same date.

SELECT
  id,
  placed,
  total_price,
  SUM(total_price) OVER(ORDER BY placed RANGE UNBOUNDED PRECEDING)
FROM single_order;
Diagram B

Exercise

Modify the example so that it shows the average total_price for single days for each row.

Stuck? Here's a hint!

You need to change both the aggregating function and the window frame.

Console

Code editor

Result

TableConsole