Understanding window function frame with RANGE mode

Question

SELECT
    sum(unique1) OVER () AS total,
    sum(unique1) OVER 
    (PARTITION BY four ORDER BY unique1 RANGE BETWEEN 5::int8 PRECEDING AND 6::int2 FOLLOWING),
    unique1,
    four
FROM
    tenk1
WHERE
    unique1 < 10;

return:

 total | sum | unique1 | four
-------+-----+---------+------
    45 |   4 |       0 |    0
    45 |  12 |       4 |    0
    45 |  12 |       8 |    0
    45 |   6 |       1 |    1
    45 |  15 |       5 |    1
    45 |  14 |       9 |    1
    45 |   8 |       2 |    2
    45 |   8 |       6 |    2
    45 |  10 |       3 |    3
    45 |  10 |       7 |    3
(10 rows)

Minor change based on this

Since partition by four make each frame only 2 or 3 rows. and if you between 5 preceding and 6 following, then I thought in this case, rows/range frame doesn't matter. I thought range from, rows from will return the same result. because 5 preceding 6 following covered enough 2, 3 rows per frame.

However it does matter. I guess I do understand the same query with ROWS instead of RANGE.

Quote from manual:

In RANGE or GROUPS mode, a frame_start of CURRENT ROW means the frame starts with the current row's first peer row (a row that the window's ORDER BY clause sorts as equivalent to the current row), while a frame_end of CURRENT ROW means the frame ends with the current row's last peer row. In ROWS mode, CURRENT ROW simply means the current row.

Question: How to interpret

partition by four order by unique1 rows between 5::int8 preceding and 6::int2 following

user_6464308 · Answer

The documentation states:

In ROWS mode, the offset must yield a non-null, non-negative integer, and the option means that the frame starts or ends the specified number of rows before or after the current row.

[...]

In RANGE mode, these options require that the ORDER BY clause specify exactly one column. The offset specifies the maximum difference between the value of that column in the current row and its value in preceding or following rows of the frame. The data type of the offset expression varies depending on the data type of the ordering column.

(The emphasis is mine.)

So with ROWS, you will get the 5 rows before and the 6 rows after the current row. With RANGE, you will get those rows where unique1 is no more than 5 less or 6 more than the unique1 of the current row.

In your example, if you consider the first row, ROWS BETWEEN ... AND 6 FOLLOWING would include the third row, but RANGE BETWEEN ... AND 6 FOLLOWING would not, because the difference between 8 (the value of unique1 in the third row) and 0 is greater than 6.

Understanding window function frame with RANGE mode

1 Answers