PySpark window functions explained

PySpark window functions compute a value across a set of rows related to the current row without collapsing them (unlike groupBy). You define a `Window` with `partitionBy()` and `orderBy()`, then apply functions like `row_number()`, `rank()`, `dense_rank()`, `lag()`, `lead()`, or a running `sum()` over the window.

Defining a window

`from pyspark.sql.window import Window` then `w = Window.partitionBy("group").orderBy("date")`. partitionBy splits rows into independent groups; orderBy defines the order within each group.

Ranking and running totals

`row_number().over(w)` numbers rows 1..n per partition; a running total uses `sum(col).over(w.rowsBetween(Window.unboundedPreceding, Window.currentRow))`.

Building a window: partitionBy, orderBy and frames

A window spec has up to three parts. partitionBy defines the groups the calculation restarts within, orderBy defines the sequence inside each group, and the frame defines which rows around the current one are included. Window.partitionBy('dept').orderBy(F.col('salary').desc()) is the typical ranking window. Omitting partitionBy puts every row in a single partition, which forces all the data onto one executor — fine for small data, dangerous at scale.

rowsBetween vs rangeBetween, and choosing a ranking function

rowsBetween counts physical rows, so rowsBetween(-2, 0) is 'this row and the two before it' — the right choice for a rolling window of N records. rangeBetween works on the values of the order column, so rows with equal values are treated as one unit; that is what you want for a true time-based range. For ranking, row_number() always produces unique consecutive numbers, rank() leaves gaps after ties (1, 1, 3) and dense_rank() does not (1, 1, 2). Use row_number() when you need exactly one row per group, such as deduplication, and rank() or dense_rank() when genuine ties should share a position.

Example (PySpark)

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, sum

spark = SparkSession.builder.getOrCreate()
sales = spark.createDataFrame(
    [("A", "2024-01-01", 100), ("A", "2024-01-02", 150), ("B", "2024-01-01", 200)],
    ["region", "day", "amount"],
)
w = Window.partitionBy("region").orderBy("day")

sales.withColumn("rn", row_number().over(w)) \
     .withColumn("running_total", sum("amount").over(w)) \
     .show()

Numbers rows per region by day and computes a running total of amount within each region.

Run this example in the free online PySpark compiler

Frequently asked questions

What is the difference between groupBy and window functions in PySpark?

groupBy collapses each group into one row; window functions keep every row and add a computed column based on a window of related rows.

What is the difference between rank, dense_rank and row_number?

row_number gives a unique 1..n per partition; rank leaves gaps after ties; dense_rank does not leave gaps after ties.

How do I compute a running total in PySpark?

Use sum(col).over(Window.partitionBy(...).orderBy(...)) — by default the frame runs from the start of the partition to the current row, giving a running total.

Practice challenges

• 7-Day Rolling Sum with Increase Flag
• Running Total Revenue
• Rolling 7-Day Average Daily Sales

Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs