Window Functions
Overview
Learn how to perform calculations across a partition of rows—such as running totals, ranks, or moving averages—without collapsing the rows.
What You Will Learn
In this lesson, you will learn:
- Window Definition: Partitioning and ordering rows using
Window.partitionBy(). - Ranking Functions: Assigning ranks using
row_number(),rank(), anddense_rank(). - Analytical Functions: Calculating offsets using
lead()andlag().
Detailed Concept Explanation
A Window Function performs calculations across a set of rows (called a frame) that are related to the current row. Unlike a groupBy(), which collapses multiple rows into a single summary row, window functions return a value for every input row.
To use a window function:
- Define the Window: Specify how rows are grouped (
partitionBy) and ordered (orderBy). - Apply the Function: Use functions like
row_number()orlead()over the defined window.
Code Examples
Input Dataset Preview
Below is the student scores dataset:
| student | subject | score |
|---|---|---|
| Alice | Math | 95 |
| Bob | Math | 88 |
| Charlie | Science | 92 |
Python (PySpark) Implementation
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, row_number
spark = SparkSession.builder.appName("Window").getOrCreate()
data = [("Alice", "Math", 95), ("Bob", "Math", 88), ("Charlie", "Science", 92)]
df = spark.createDataFrame(data, ["student", "subject", "score"])
# Define window specification
windowSpec = Window.partitionBy("subject").orderBy(col("score").desc())
# Calculate rank per subject
ranked_df = df.withColumn("rank", row_number().over(windowSpec))
ranked_df.show()
Expected Output
+-------+-------+-----+----+
|student|subject|score|rank|
+-------+-------+-----+----+
| Alice| Math| 95| 1|
| Bob| Math| 88| 2|
|Charlie|Science| 92| 1|
+-------+-------+-----+----+
Execution Plan Diagram (Python & Scala)
Scala Implementation
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._
val spark = SparkSession.builder().appName("WindowScala").getOrCreate()
import spark.implicits._
val df = Seq(("Alice", "Math", 95), ("Bob", "Math", 88), ("Charlie", "Science", 92)).toDF("student", "subject", "score")
val windowSpec = Window.partitionBy("subject").orderBy($"score".desc)
val ranked = df.withColumn("rank", row_number().over(windowSpec))
ranked.show()
Expected Output
+-------+-------+-----+----+
|student|subject|score|rank|
+-------+-------+-----+----+
| Alice| Math| 95| 1|
| Bob| Math| 88| 2|
|Charlie|Science| 92| 1|
+-------+-------+-----+----+
SQL Implementation
SELECT student, subject, score,
ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS rank
FROM grades;
Expected Output
Executing this SQL query returns:
| student | subject | score | rank |
|---|---|---|---|
| Alice | Math | 95 | 1 |
| Bob | Math | 88 | 2 |
| Charlie | Science | 92 | 1 |
Common Mistakes
- Omission of partitionBy: Defining a window with only an
orderBy()clause forces Spark to gather all dataset rows onto a single node to determine the overall order. This will crash your driver with an Out Of Memory (OOM) error. Always usepartitionBy()to distribute the window calculations.
Best Practices
- Define Window Limits: Use frame boundaries (like
rowsBetween) to limit calculations to a subset of rows relative to the current row, protecting memory.
Interview Perspective
What is the difference between rank() and dense_rank() in Spark?
Both assign a rank to rows based on an order. However, if there is a tie (e.g. two rows share rank 1), rank() will skip the next rank (assigning the third row rank 3), while dense_rank() will not skip ranks (assigning the third row rank 2).
Interactive Challenges
Related Topics
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.