Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 48 ยท DataFrames and Spark SQL

Window Functions

Use Spark window functions: partitionBy and orderBy, row_number, rank, dense_rank, lag and lead, running totals, and rows versus range frames with examples.

An aggregation collapses many rows into one. Sometimes you want the opposite: keep every row and add a value computed from related rows. "Each sale next to its country's average." "Each day's revenue and the revenue of the day before." "The three most recent orders of every customer." These are jobs for window functions.

A window function looks at a window of rows around the current row, computes a value, and adds it as a new column, without removing any row. This lesson covers how to define a window, the ranking functions, lag and lead, running totals, and the frame rules that decide exactly which rows are included. It follows Joins and uses the grouping ideas of Grouping and Aggregations.

Window functions run in Spark on a cluster, so the Spark code is read-only. The runnable examples reproduce the same results with pandas.

Defining a window

A window has up to three parts:

  • partitionBy: splits the rows into independent groups (like a groupBy, but rows are kept).
  • orderBy: orders the rows inside each partition.
  • frame: which rows around the current row take part (optional; defaults apply).
Python
from pyspark.sql import Window
from pyspark.sql import functions as F

w = Window.partitionBy("country").orderBy(F.col("amount").desc())

sales.withColumn("rank_in_country", F.row_number().over(w))
sales.withColumn("country_avg", F.avg("amount").over(Window.partitionBy("country")))

The same in SQL:

Python
spark.sql("""
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY country ORDER BY amount DESC) AS rank_in_country,
           AVG(amount)  OVER (PARTITION BY country)                      AS country_avg
    FROM sales
""")

You can use a window with an ordinary aggregate (sum, avg, count, max), with a ranking function, or with an analytic function such as lag. Without partitionBy, the whole DataFrame is one partition, and Spark must move all rows to a single task. It will log a warning about that, and it will not scale; always partition when you can.

flowchart LR
    D["All rows"] --> P["Partition by country<br/>(shuffle)"]
    P --> G1["DE rows<br/>sorted by amount"]
    P --> G2["FR rows<br/>sorted by amount"]
    G1 --> F1["Function sees the frame<br/>of each row"]
    G2 --> F2["Function sees the frame<br/>of each row"]

Spark shuffles rows by the partition key, sorts inside each partition, then evaluates the function per row.

Ranking functions

Ranking functions need an orderBy. They differ in how they treat ties:

Function Ties Example for values 100, 90, 90, 80
row_number() Never tied; arbitrary order among equals 1, 2, 3, 4
rank() Equal rank, then a gap 1, 2, 2, 4
dense_rank() Equal rank, no gap 1, 2, 2, 3
ntile(n) Splits the partition into n buckets depends on n
percent_rank(), cume_dist() Relative position from 0 to 1

With row_number, if two rows tie on the order column, which one gets 2 and which gets 3 is not defined. Add a second order column (such as an id) to make it deterministic.

The most useful pattern in practice is top-N per group: rank within each partition, then filter.

Python
w = Window.partitionBy("customer_id").orderBy(F.col("order_date").desc(), F.col("order_id").desc())

latest_per_customer = (orders
    .withColumn("rn", F.row_number().over(w))
    .filter("rn = 1")
    .drop("rn"))

This is the right way to "keep the latest row per key", which dropDuplicates cannot do reliably (see Filtering, Sorting and Deduplicating). Window results cannot be used directly in filter, so compute the column first, then filter.

Python row_number, rank and dense_rank, then top-N per group

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

lag and lead

lag(col, n) returns the value from n rows before the current row (within the partition's order), and lead(col, n) from n rows after. When no such row exists, you get null, or a default value you pass as the third argument.

Python
w = Window.partitionBy("product").orderBy("day")

daily = (daily
    .withColumn("prev_revenue", F.lag("revenue", 1).over(w))
    .withColumn("change", F.col("revenue") - F.col("prev_revenue"))
    .withColumn("next_revenue", F.lead("revenue", 1).over(w)))

This is how you compute day-over-day change, time between events (event_time - lag(event_time)), or sessions. Both functions need an orderBy, because "previous" has no meaning without an order.

Frames and running totals

For aggregates, the frame says which rows around the current row are included. You specify it with rowsBetween or rangeBetween, using these boundaries: Window.unboundedPreceding, Window.currentRow, Window.unboundedFollowing, or an integer offset (-2 means two rows back).

Python
w = Window.partitionBy("product").orderBy("day")

running = F.sum("revenue").over(w.rowsBetween(Window.unboundedPreceding, Window.currentRow))
moving3 = F.avg("revenue").over(w.rowsBetween(-2, 0))       # this row and the two before
whole   = F.sum("revenue").over(w.rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing))

A rows frame counts physical rows. A range frame is defined by the values of the order column: rangeBetween(-6, 0) on a day number means "every row whose day is between today - 6 and today", however many rows that is. Range frames need exactly one numeric (or date-like) order column.

Now the default, which surprises people. When a window has an orderBy and no explicit frame, aggregates use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With a range frame, rows that tie on the order value are peers, and they all see each other: a running sum includes every peer of the current row. Without an orderBy, the frame is the whole partition. If you want a plain running total that adds one row at a time, say rowsBetween(Window.unboundedPreceding, Window.currentRow), and make the order unique.

flowchart TB
    subgraph R["rowsBetween(-1, 0)"]
        R1["Frame = previous row + current row"]
    end
    subgraph G["rangeBetween(-1, 0) on day"]
        G1["Frame = all rows with day in [day-1, day]"]
    end
    subgraph D["default with orderBy"]
        D1["Frame = start of partition up to the current row and its ties"]
    end

Three common frames: rows counted physically, range by value, and the default that includes ties.

The stand-in implements frames by hand, so you can see exactly which rows each window includes, including the tie surprise.

Python lag, running total and the tie surprise of the default frame

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Window functions versus groupBy

A groupBy returns one row per group and loses the detail. A window keeps the detail and adds the group-level value. If you need both (each sale and its share of the country total), use a window; if you only need the total, use groupBy, which is cheaper because of partial aggregation.

Python
share = sales.withColumn(
    "share_of_country",
    F.col("amount") / F.sum("amount").over(Window.partitionBy("country")))

Every distinct window specification may need its own shuffle and sort, so several functions that share the same partitionBy and orderBy are evaluated together in one pass, while different specifications cost extra passes. Group your window columns by specification. Very large partitions (for example one customer with a billion rows) put all those rows in one task, which is a form of skew (see Performance Tuning).

Python Share of total and cumulative share per country

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real versus stand-in. Real Spark shuffles the data by the partitionBy key, sorts each partition and evaluates every function with the configured frame, in parallel. The pandas code reproduces the results: rank numbering, lag/lead, rows and range frames. Ties and frames are the usual source of confusion, which is why they are shown by hand.

Common mistakes

  • Using a window with no partitionBy on a big DataFrame: everything lands in one task.
  • Using row_number with a non-unique order and getting different results from run to run.
  • Expecting a running sum without an explicit frame to add one row at a time when order values tie.
  • Trying to filter on a window expression directly instead of creating a column first.
  • Calling lag or lead without an orderBy.
  • Mixing many different window specifications and paying for several shuffles.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. For the values 100, 90, 90, 80 (descending), what does rank() return?

    rank gives tied rows the same rank and then skips numbers (1, 2, 2, 4). dense_rank would give 1, 2, 2, 3 and row_number 1, 2, 3, 4.
  2. 2. How do you keep only the latest row per customer?

    The window ranks rows inside each customer by date, and filtering on rank 1 keeps the newest. dropDuplicates keeps an arbitrary row.
  3. 3. A window has orderBy but no explicit frame. Which frame does an aggregate such as sum use?

    The default with an order is a range frame, so rows that tie on the order value are all included together. Use rowsBetween for a strict row-by-row running total.
  4. 4. What does lag('revenue', 1) return for the first row of a partition?

    There is no previous row, so the result is null. A third argument supplies a default.
  5. 5. What is the main downside of a window with no partitionBy on a large DataFrame?

    With no partition key there is one big partition and one task does all the work.

Summary

  • A window function adds a value computed from related rows without removing rows; define it with partitionBy, orderBy and an optional frame.
  • row_number, rank and dense_rank differ in how they treat ties; add a tie-breaker for deterministic results.
  • Top-N and "latest per key" are done by ranking in a window and filtering the rank.
  • lag and lead read neighboring rows; they need an order and return null at the edges.
  • Frames use rowsBetween (physical rows) or rangeBetween (values); the default frame with an order is a range frame that includes ties.
  • Always partition large windows, and group functions that share a specification.

Next: work with nested data in Arrays, Maps, Structs and JSON.