A data engineer is working with a large PySpark DataFrame `sensor_data_df` in Microsoft Fabric. The DataFrame contains `device_id`, `timestamp`, and `temperature` columns. The engineer needs to calculate the average temperature for each `device_id` over a 30-minute rolling window, based on the `timestamp`. Which PySpark window function definition correctly specifies the rolling window for this requirement?
- AWindow.partitionBy('device_id').orderBy('timestamp').rowsBetween(-30, 0)
- BWindow.partitionBy('device_id').orderBy('timestamp').rangeBetween(F.col('timestamp') - F.expr('INTERVAL 30 MINUTES'), F.col('timestamp'))
- CWindow.partitionBy('device_id').orderBy('timestamp').rowsBetween(-sys.maxsize, 0)
- DWindow.partitionBy('device_id').orderBy('timestamp').rangeBetween(-30 * 60, 0)
Show answer & explanationAnswer & explanation
Correct answer: D. Window.partitionBy('device_id').orderBy('timestamp').rangeBetween(-30 * 60, 0)
For time-based rolling windows with a fixed duration (like 30 minutes), `rangeBetween()` is the correct function. When `orderBy` is on a timestamp column, `rangeBetween()` expects numeric offsets representing seconds (or milliseconds, depending on the timestamp resolution). `-30 * 60` calculates 30 minutes in seconds, and `0` indicates the current row's timestamp. Option B is incorrect as `rangeBetween` does not directly accept `F.expr('INTERVAL 30 MINUTES')` for timestamp offsets in this direct manner.
Why the other options are wrong
- A. `rowsBetween()` defines the window by the number of rows, not by a time interval.
- B. This syntax for `rangeBetween()` with `F.col('timestamp') - F.expr('INTERVAL 30 MINUTES')` is not directly supported as a numerical offset for the start of the range.
- C. `rowsBetween(-sys.maxsize, 0)` defines an unbounded preceding window based on row count, not a fixed-duration time window.
PySpark rangeBetween() for Time
A PySpark window function clause that defines a window frame based on a range of values relative to the current row, commonly used for time-based windows when `orderBy` is on a numeric or timestamp column.
- Requires `orderBy` on a numeric or timestamp column.
- Offsets specify values relative to the current row (e.g., seconds for timestamp).
- Used for fixed-duration rolling windows, like 'last 30 minutes'.
Memory trick: Range by time, not just by row count.