Running Totals & Moving Averages

Apache Spark · Window Functions

Theory

A frame limits which rows of the window an aggregate sees. rowsBetween(start, end) counts physical rows: rowsBetween(-2, 0) means the current row and the two rows before it, whatever their values. rangeBetween(start, end) works on the value of the single orderBy column: rangeBetween(-2, 0) on a day number means every row whose day is within two days before the current row's day, so missing days shrink the frame and duplicate days are all included. Window.unboundedPreceding, Window.currentRow and Window.unboundedFollowing mark the ends.

A running total is F.sum("amount").over(w.rowsBetween(Window.unboundedPreceding, Window.currentRow)). A 3-row moving average is F.avg("amount").over(w.rowsBetween(-2, 0)). If dates have gaps and you want a true 7-day window, order by a numeric day value (or a timestamp cast to long seconds) and use rangeBetween.

Why we use it

Running totals, cumulative revenue, rolling 7-day averages and year-to-date figures are everyday reporting needs. Frames compute them in one sorted pass per partition instead of self-joins, and choosing rows vs range decides whether gaps in dates are handled correctly.

When to use it

Use rowsBetween for 'last N records' and for running totals where each row counts once (ties included one at a time). Use rangeBetween for 'last N days/units' when dates can be missing or repeated. rangeBetween needs exactly one numeric or date-like orderBy column.

How it works

  1. Ordered window per partition
  2. Is the window 'last N rows' or 'last N days/units'? Yes: Use rowsBetween(-N+1, 0). No: Use rangeBetween(-N+1, 0) on a numeric day column.
  3. For running totals use (unboundedPreceding, currentRow)
  4. Apply sum / avg / count over the framed window
  5. Each row gets its cumulative or rolling value

Syntax

w = Window.partitionBy("region").orderBy("order_date")
run = w.rowsBetween(Window.unboundedPreceding, Window.currentRow)
last3 = w.rowsBetween(-2, Window.currentRow)
last7d = Window.partitionBy("region").orderBy(F.col("day_num")).rangeBetween(-6, 0)
F.sum("amount").over(run); F.avg("amount").over(last3)

Example

from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

orders = spark.createDataFrame([
    (1, 101, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (2, 102, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (3, 101, "North", "Mouse",  3,  1500.0, "2026-01-07"),
    (4, 103, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (5, 102, "West",  "Tablet", 1, 25000.0, "2026-01-09"),
    (6, 104, "North", "Phone",  1, 18000.0, "2026-01-10"),
    (7, 103, "South", "Mouse",  2,  1000.0, "2026-01-11"),
    (8, 105, "West",  "Laptop", 1, 60000.0, "2026-01-12"),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

daily = (orders.groupBy("order_date").agg(F.sum("amount").alias("sales"))
               .withColumn("day_num", F.datediff("order_date", F.lit("2026-01-01"))))

w = Window.orderBy("order_date")              # small daily table, one partition is fine

result = (daily
    .withColumn("running_total", F.sum("sales").over(w.rowsBetween(Window.unboundedPreceding, Window.currentRow)))
    .withColumn("avg_last_3_rows", F.round(F.avg("sales").over(w.rowsBetween(-2, 0)), 0))
    .withColumn("sum_last_3_days", F.sum("sales").over(Window.orderBy("day_num").rangeBetween(-2, 0)))
    .orderBy("order_date"))

result.show()

# Output:
# +----------+-------+-------+-------------+---------------+---------------+
# |order_date|  sales|day_num|running_total|avg_last_3_rows|sum_last_3_days|
# +----------+-------+-------+-------------+---------------+---------------+
# |2026-01-05|55000.0|      4|      55000.0|        55000.0|        55000.0|
# |2026-01-06|30000.0|      5|      85000.0|        42500.0|        85000.0|
# |2026-01-07|53500.0|      6|     138500.0|        46167.0|       138500.0|
# |2026-01-09|25000.0|      8|     163500.0|        36167.0|        78500.0|
# |2026-01-10|18000.0|      9|     181500.0|        32167.0|        43000.0|
# |2026-01-11| 1000.0|     10|     182500.0|        14667.0|        44000.0|
# |2026-01-12|60000.0|     11|     242500.0|        26333.0|        79000.0|
# +----------+-------+-------+-------------+---------------+---------------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic