What Window Functions Solve
A window function computes a value for each row by looking at a defined set of related rows — its window — without collapsing those rows into a single output row the way groupBy() does. This makes window functions ideal for tasks like ranking each employee's salary within their department, computing a 7-day rolling average per store, or finding the difference between a row's value and the previous row's value, all while keeping every original row intact in the result.
Cricket analogy: It is like a scorer computing each batter's strike rate relative only to their own innings' deliveries, without merging all batters into one combined team total — every individual ball-by-ball row survives.
Defining a Window Specification
A window is defined with pyspark.sql.Window, combining partitionBy() to group rows (like a groupBy key, but without collapsing rows), orderBy() to sequence rows within each partition, and an optional frame clause such as rowsBetween(Window.unboundedPreceding, Window.currentRow) to control exactly which neighboring rows are visible to the function. Ranking functions like rank(), dense_rank(), and row_number() require an orderBy(), while aggregate functions used as window functions, such as sum() or avg(), work with or without one, though the frame's default behavior changes depending on whether an order is specified.
Cricket analogy: It is like defining a bowling analysis window as 'this bowler, this match, deliveries up to and including the current over' — partition by bowler and match, order by over number, frame ending at the current row.
from pyspark.sql import Window
from pyspark.sql import functions as F
sales_df = spark.table("regional_sales")
window_spec = (
Window.partitionBy("region")
.orderBy(F.col("sale_date"))
.rowsBetween(Window.unboundedPreceding, Window.currentRow)
)
result = sales_df.withColumn(
"running_total", F.sum("amount").over(window_spec)
).withColumn(
"rank_in_region", F.dense_rank().over(
Window.partitionBy("region").orderBy(F.col("amount").desc())
)
).withColumn(
"prev_day_amount", F.lag("amount", 1).over(
Window.partitionBy("region").orderBy("sale_date")
)
)
result.select("region", "sale_date", "amount", "running_total", "rank_in_region", "prev_day_amount").show()Ranking, Lag/Lead, and Aggregate Functions
Spark provides three families of window functions: ranking functions (row_number(), rank(), dense_rank(), ntile()) for assigning positions within a partition; offset functions (lag(), lead()) for pulling a value from a preceding or following row without a self-join; and any standard aggregate function (sum, avg, min, max, count) applied over a window frame instead of a groupBy. rank() leaves gaps after ties (1, 2, 2, 4) while dense_rank() does not (1, 2, 2, 3), a distinction that matters whenever the ranking feeds into something like top-N filtering.
Cricket analogy: It is like the ICC batting rankings using dense positions with no skipped numbers for tied points, versus a knockout bracket seeding that leaves a gap after a tie because two teams share the same win count.
lag() and lead() eliminate the need for a self-join when you want to compare a row to its neighbor — for example, computing day-over-day percentage change with (amount - lag(amount, 1).over(w)) / lag(amount, 1).over(w) is far cheaper than joining the DataFrame to itself on a shifted date key.
A window function without partitionBy() computes over the entire DataFrame as one giant partition, which Spark evaluates on a single executor if there's also an orderBy() — this is a common cause of out-of-memory errors and job stalls on large datasets. Always partition your window by a sensible key unless you truly need a global rank.
- Window functions compute per-row values over a related set of rows without collapsing the result like groupBy() does.
- Window.partitionBy() groups rows, orderBy() sequences them, and rowsBetween()/rangeBetween() defines the frame.
- rank() leaves gaps after ties; dense_rank() does not.
- lag() and lead() fetch neighboring row values without requiring a self-join.
- Standard aggregates (sum, avg, min, max, count) can be used as window functions via .over().
- An unpartitioned window with an orderBy() forces evaluation on a single executor — always partition sensibly.
- row_number() always assigns unique sequential numbers even when values tie.
Practice what you learned
1. How do window functions differ fundamentally from groupBy() aggregations?
2. What is the key difference between rank() and dense_rank()?
3. Which function retrieves the value from the previous row in a window without a self-join?
4. What is a common performance risk of using a window function without partitionBy()?
5. Which clause defines exactly which neighboring rows are visible to a window function?
Was this page helpful?
You May Also Like
Running SQL Queries on DataFrames
Learn how to register DataFrames as temporary views and query them with standard SQL syntax, and how SQL and the DataFrame API interoperate under the hood.
User-Defined Functions (UDFs)
Understand when and how to extend Spark SQL with custom Python or Pandas UDFs, and why built-in functions should always be preferred when available.
Structured Streaming Basics
Learn how Spark's Structured Streaming engine treats a live data stream as an unbounded, continuously growing table processed with the same DataFrame API used for batch jobs.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics