Rolling Window

What is a Rolling Window?

A rolling window (also known as a moving window) is a technique used to calculate aggregates (like averages, sums, counts, etc.) over a specified range of rows. This “window” slides over the data, calculating the aggregate for each subset.

In time-series analysis, this is often used to calculate things like moving averages, where the data “window” moves forward in time, aggregating the values over a defined period — say, 7 days, 30 days, or any other custom time range.

For example, in our example, we want to calculate the moving average of sales numbers over the last 7 days. This means that for each day, we calculate the average sales number for that day and the 6 previous days.

Sample Data

date,product_name,sales_number,monthly_profit
2025-01-01,Product A,100,5000
2025-01-02,Product A,150,7500
2025-01-03,Product A,120,6000
2025-01-04,Product A,180,9000
2025-01-05,Product A,200,10000
2025-01-06,Product A,170,8500
2025-01-07,Product A,160,8000
2025-01-08,Product A,190,9500
2025-01-09,Product A,210,10500

The SQL Approach

One of the most straightforward ways to implement a rolling window function is using SQL’s window functions. In SQL, the OVER() clause, combined with ROWS BETWEEN, lets us define a sliding window.

Here’s how we would write the query to calculate a 7-day moving average of sales numbers:

SELECT
  date,
  product_name,
  sales_number,
  AVG(sales_number) OVER (PARTITION BY product_name ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM product_sales
ORDER BY product_name, date;

Explanation:

• PARTITION BY product_name: This ensures that we calculate the rolling window separately for each product. We don’t want the moving average for Product A to include Product B’s sales data, right?
• ORDER BY date: This orders the data by the date column so that the window can "roll" over time.
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: This defines the window size. We want a 7-day window, which means we’re including the current day and the 6 previous rows.

The result of this query would give us the moving average of sales over the last 7 days for each product.

The Spark Approach

In distributed data processing frameworks like Apache Spark, the approach is quite similar but adapted to the distributed nature of the system. Spark provides the Window function to help with partitioning and ordering data in a distributed environment.

Get Devendra’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Here’s how you would calculate the same 7-day moving average in Spark:

 from pyspark.sql import SparkSession
from pyspark.sql.functions import col, avg
from pyspark.sql.window import Window

# Initialize Spark session
spark = SparkSession.builder.appName("RollingWindowExample").getOrCreate()

# Sample data
data = [
    ("2025-01-01", "Product A", 100, 5000),
    ("2025-01-02", "Product A", 150, 7500),
    ("2025-01-03", "Product A", 120, 6000),
    ("2025-01-04", "Product A", 180, 9000),
    ("2025-01-05", "Product A", 200, 10000),
    ("2025-01-06", "Product A", 170, 8500),
    ("2025-01-07", "Product A", 160, 8000),
    ("2025-01-08", "Product A", 190, 9500),
    ("2025-01-09", "Product A", 210, 10500),
]

# Create DataFrame
columns = ["date", "product_name", "sales_number", "monthly_profit"]
df = spark.createDataFrame(data, columns)

# Define the window specification
window_spec = Window.partitionBy("product_name").orderBy("date").rowsBetween(-6, 0)

# Calculate moving averages
df_with_moving_avg = df.withColumn(
    "moving_avg_sales", avg("sales_number").over(window_spec)
).withColumn(
    "moving_avg_profit", avg("monthly_profit").over(window_spec)
)

df_with_moving_avg.show(truncate=False)

Explanation:

• Window.partitionBy("product_name"): Similar to SQL, this ensures that the rolling window is calculated separately for each product.
• orderBy("date"): Orders the data by date, so the window can move forward through the rows.
• rowsBetween(-6, 0): This defines the window size, just like ROWS BETWEEN in SQL. It includes the current row and the previous 6 rows (a total of 7).

This approach would allow us to compute the moving average of sales numbers for each product over a 7-day period, leveraging the power of Spark for distributed processing.

Why Rolling Window Functions Matter in Data Engineering

As data engineers, we often face the challenge of working with time-series data — whether it’s tracking sales trends, calculating rolling averages, or computing other types of aggregates over time. Here’s why rolling window functions are so essential:

• Time-based Analysis: Rolling windows are perfect for handling time-based aggregations. For example, calculating moving averages for stock prices or tracking changes in user engagement over time.
• Window Functions in SQL and Spark: These functions are powerful because they allow us to efficiently calculate aggregates over specific subsets of data without having to resort to complex joins or subqueries.
• Handling Large Datasets: In a distributed system like Spark, window functions can be applied across massive datasets. The ability to partition and order data for time-based analysis is a key skill for any data engineer working with big data.
• Performance Optimization: When working with rolling windows, it’s important to optimize the queries to handle large datasets efficiently. Knowing how to implement these functions correctly can significantly improve query performance and reduce computation time.