← PySpark roadmap

Topic 27 of 28 · Expert

Compute a rolling mean of the last three observations per entity

Compute the mean over the current and preceding two rows per id ordered by unique day.

ExpertSenior · 5–8 yearsWindow frames

Apache Spark / PySpark 3.5+ with a local JVM. Not executable inside a plain Python browser runtime.

Problem statement

Compute the mean over the current and preceding two rows per id ordered by unique day.

Monitor a bounded rolling observation trend.

Input: id int, day int, amount int non-null.

Output: Input rows plus mean3 double.

  • One observation per id/day.
  • This is three rows, not three calendar days.

Sample 1

Input: amounts10,20,30,40
Output: 10,15,20,30

Early windows use available rows.

Sample 2

Input: one amount8
Output: 8

Single observation average is itself.

Brute-force solution

Self-join each observation to its preceding row positions and aggregate, creating extra intermediate rows.

O(n log n) sorting and O(n) bounded-window aggregation, implementation-dependent.

from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
df=spark.createDataFrame([(1,1,10),(1,2,20),(1,3,30),(1,4,40),(2,1,8)],"id int, day int, amount int")
w=Window.partitionBy("id").orderBy("day").rowsBetween(-2,0)
result=df.withColumn("mean3",F.avg("amount").over(w)).orderBy("id","day")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,1,10,10.0),(1,2,20,15.0),(1,3,30,20.0),(1,4,40,30.0),(2,1,8,8.0)]
print(rows)
spark.stop()
Optimized solution

A partitioned ROWS frame computes the bounded rolling aggregate within sorted entity records.

  1. Partition by id.
  2. Order by unique day.
  3. Set rowsBetween(-2,0).
  4. Apply avg.
from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
df=spark.createDataFrame([(1,1,10),(1,2,20),(1,3,30),(1,4,40),(2,1,8)],"id int, day int, amount int")
w=Window.partitionBy("id").orderBy("day").rowsBetween(-2,0)
result=df.withColumn("mean3",F.avg("amount").over(w)).orderBy("id","day")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,1,10,10.0),(1,2,20,15.0),(1,3,30,20.0),(1,4,40,30.0),(2,1,8,8.0)]
print(rows)
spark.stop()
Complexity analysis

Time: O(n log n) sorting and O(n) bounded-window aggregation, implementation-dependent.

Auxiliary space: Distributed sorting workspace; bounded frame aggregation state.

Test cases
  • Sparse days.
  • One observation.
  • Separate entity boundaries.
Supplied script asserts all five rolling means.
Common mistakes
  • Claiming a ROWS frame measures elapsed days.
  • Leaving ordering ties unspecified.
Key takeaways
  • Specify row versus time windows.
  • Keep entity partitions.
  • Test early partial windows.
Follow-up challenges
  • How would a three-day RANGE frame differ?
  • What changes with NULL amounts?
Practise PySpark with the AI mentor

Can't find a PySpark topic you need for exams or placements? Request it and we'll add it.