snowflake.snowpark.Window.rangeBetween¶

static Window.rangeBetween(start: Union[int, Column], end: Union[int, Column]) → WindowSpec[source]¶

Returns a WindowSpec object with the range frame clause. start and end can be

an integer representing the relative position from the current row, or

Window.UNBOUNDED_PRECEDING, Window.UNBOUNDED_FOLLOWING and Window.CURRENT_ROW, which represent unbounded preceding, unbounded following and current row respectively, or

a Column object created by make_interval() to use Interval constants. Interval constants can only be used with this function when the order by column is TIMESTAMP or DATE type See more details how to use interval constants in RANGE BETWEEN clause. However, you cannot mix the numeric values and interval constants in the same range frame clause.

Parameters:

start – The relative position from the current row as a boundary start (inclusive). The frame is unbounded if this is Window.UNBOUNDED_PRECEDING, or any value less than or equal to -9223372036854775807 (-sys.maxsize).
end – The relative position from the current row as a boundary end (inclusive). The frame is unbounded if this is Window.UNBOUNDED_FOLLOWING, or any value greater than or equal to 9223372036854775807 (sys.maxsize).

Example 1

Use numeric values to specify the range frame:

>>> from snowflake.snowpark.functions import col, count, make_interval
>>>
>>> df = session.range(5)
>>> window = Window.order_by("id").range_between(-1, Window.CURRENT_ROW)
>>> df.select(col("id"), count("id").over(window).as_("count")).show()
------------------
|"ID"  |"COUNT"  |
------------------
|0     |1        |
|1     |2        |
|2     |2        |
|3     |2        |
|4     |2        |
------------------

Copy

Example 2

Use interval constants to specify the range frame:

>>> import datetime
>>> from snowflake.snowpark.types import StructType, StructField, TimestampType, TimestampTimeZone
>>>
>>> df = session.create_dataframe(
...    [
...        datetime.datetime(2021, 12, 21, 9, 12, 56),
...        datetime.datetime(2021, 12, 21, 8, 12, 56),
...        datetime.datetime(2021, 12, 21, 7, 12, 56),
...        datetime.datetime(2021, 12, 21, 6, 12, 56),
...    ],
...    schema=StructType([StructField("a", TimestampType(TimestampTimeZone.NTZ))]),
... )
>>> window = Window.order_by(col("a").desc()).range_between(-make_interval(hours=1), make_interval(hours=1))
>>> df.select(col("a"), count("a").over(window).as_("count")).show()
---------------------------------
|"A"                  |"COUNT"  |
---------------------------------
|2021-12-21 09:12:56  |2        |
|2021-12-21 08:12:56  |3        |
|2021-12-21 07:12:56  |3        |
|2021-12-21 06:12:56  |2        |
---------------------------------

Copy