Windows and deterministic ranking

Opening your lesson

A window calculates a value for each row using related rows. partitionBy chooses those related rows, orderBy orders them and an optional frame sets the range used by an aggregate. Unlike groupBy, a window preserves the detail records.

Aster Supply needs a latest order per buyer and a running amount. Use ordered_at plus id as a tie-breaker when each row needs a deterministic position. row_number assigns a different number to every row, while rank gives tied values the same rank and leaves gaps. lag reads a previous row within the chosen ordering; the first row has no previous value and receives NULL. For a running sum, specify rowsBetween(unboundedPreceding,currentRow) so the frame means all preceding rows through this one. A missing amount still follows the aggregate's NULL rules.

from pyspark.sql import functions as F
from pyspark.sql.window import Window
w=Window.partitionBy('customer_id').orderBy(F.col('ordered_at').desc(),F.col('id').desc())
display(spark.table('main.bronze.orders').withColumn('rn',F.row_number().over(w)).filter(F.col('rn')==1).select('id','customer_id'))

Order descending to keep the latest order rather than the oldest.

Choose your character

Your Work Gym account