27. Frames: running totals and moving averages

An ordered window has a frame — which rows around the current one the function actually sees. Controlling the frame is how you build running totals and moving averages.

The default frame, and running totals

With an orderBy, the default frame is "everything from the start of the partition up to the current row". That's exactly a running total:

w = Window.partitionBy("customer_id").orderBy("order_date")

orders.dropDuplicates().withColumn(
    "line_total", F.col("quantity") * F.col("unit_price")
).withColumn(
    "running_total", F.round(F.sum("line_total").over(w), 2)
).select("customer_id", "order_date", "line_total", "running_total").show(10)

The running_total climbs with each of a customer's orders and resets for the next customer.

Spelling the frame out

You can state the frame explicitly with rowsBetween:

w = (
    Window.partitionBy("customer_id").orderBy("order_date")
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)
)

• Window.unboundedPreceding — start of partition
• Window.currentRow — the current row
• Window.unboundedFollowing — end of partition
• integers are offsets: -1 one row back, 2 two rows ahead

Moving averages

A moving average is a sliding frame — average of the row and its neighbours. rowsBetween(-1, 1) is a 3-row window:

w = (
    Window.partitionBy("customer_id").orderBy("order_date")
    .rowsBetween(-1, 1)
)
orders.dropDuplicates().withColumn(
    "moving_avg_price", F.round(F.avg("unit_price").over(w), 2)
).select("customer_id", "order_date", "unit_price", "moving_avg_price").show(10)

rowsBetween vs rangeBetween. rowsBetween counts physical rows. rangeBetween counts by the value of the order-by column (e.g. "within 7 days") — powerful for time windows, but subtler. Start with rowsBetween; reach for rangeBetween when you need value-based ranges.

Your turn

Compute each customer's running total of quantity over their orders ordered by order_date. Add running_qty, and return customer_id, order_date, quantity, running_qty, from deduplicated orders. Assign to result.

w = Window.partitionBy("customer_id").orderBy("order_date")
result = orders  # <- dropDuplicates, running_qty = sum(quantity).over(w), select 4 cols
result.show()

w = Window.partitionBy("customer_id").orderBy("order_date")
result = orders.dropDuplicates().withColumn(
    "running_qty", F.sum("quantity").over(w)
).select("customer_id", "order_date", "quantity", "running_qty")