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.
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.
- Partition by id.
- Order by unique day.
- Set rowsBetween(-2,0).
- 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?