What is the difference between rowsBetween and rangeBetween?

by

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|
+---+-----------------------------------------------------+

Leave a Comment