sql - What is the difference between rowsBetween and rangeBetween?

Question

From the PySpark docs rangeBetween:

rangeBetween(start, end)

Defines the frame boundaries, from start (inclusive) to end (inclusive).

Both start and end are relative from the current row. For example, “0” means “current row”, while “-1” means one off before the current row, and “5” means the five off after the current row.

Parameters:

• start – boundary start, inclusive. The frame is unbounded if this is -sys.maxsize (or lower).
• end – boundary end, inclusive. The frame is unbounded if this is sys.maxsize (or higher). New in version 1.4.

while rowsBetween

rowsBetween(start, end)

Defines the frame boundaries, from start (inclusive) to end (inclusive).

Both start and end are relative positions from the current row. For example, “0” means “current row”, while “-1” means the row before the current row, and “5” means the fifth row after the current row.

Parameters:

• start – boundary start, inclusive. The frame is unbounded if this is -sys.maxsize (or lower).
• end – boundary end, inclusive. The frame is unbounded if this is sys.maxsize (or higher). New in version 1.4.

For rangeBetween how is "1 off" different from "1 row", for example?

Answer 1

It is simple:

• ROWS BETWEEN doesn't care about the exact values. It cares only about the order of rows, and takes fixed number of preceding and following rows when computing frame.
• RANGE BETWEEN considers values when computing frame.

Let's use an example using two window definitions:

• ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• ORDER BY x RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

and data as

+---+
|  x|
+---+
| 10|
| 20|
| 30|
| 31|
+---+

Assuming the current row is the one with value 31 for the first window following rows will be included (current one, and two preceding):

+---+----------------------------------------------------+
|  x|ORDER BY x ROWS BETWEEN 2  PRECEDING AND CURRENT ROW|
+---+----------------------------------------------------+
| 10|                                               false|
| 20|                                                true|
| 30|                                                true|
| 31|                                                true|
+---+----------------------------------------------------+

and for the second one following (current one, and all preceding where x >= 31 - 2):

+---+-----------------------------------------------------+
|  x|ORDER BY x RANGE BETWEEN 2  PRECEDING AND CURRENT ROW|
+---+-----------------------------------------------------+
| 10|                                                false|
| 20|                                                false|
| 30|                                                 true|
| 31|                                                 true|
+---+-----------------------------------------------------+