Window Spec: partitionBy, orderBy & Frames
Apache Spark · Window Functions
Theory
A window function calculates a value for every row by looking at a group of related rows, called the window, without collapsing the rows the way groupBy does. In PySpark you describe the window once with the Window class: partitionBy(...) says which rows belong together, orderBy(...) sorts rows inside each partition, and an optional frame (rowsBetween or rangeBetween) says which neighbouring rows to use. You then apply a function with .over(w). For example, F.sum("amount").over(Window.partitionBy("region")) puts each region's total next to every order in that region.
The frame has a default that surprises people. With no orderBy, the frame is the whole partition. With orderBy and no explicit frame, Spark uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so sum() becomes a running total. To process a window, Spark shuffles rows so each partition key sits in one task, then sorts it.
Why we use it
Many questions need a row-level answer plus group context: each order with its region total, its share of the total, its rank, or the previous order. groupBy loses the detail rows, and joining aggregates back is slower and messier. A window spec gives both in one pass.
When to use it
Use a window when every input row must stay in the output but needs a value computed from other rows. If you only need one row per group (totals, counts), use groupBy().agg() instead. Avoid a window with no partitionBy on big data: every row goes to one task.
How it works
- Start with a DataFrame of detail rows
- Define w = Window.partitionBy(keys)
- Does the calculation depend on row order? Yes: Add .orderBy(cols), and a frame if needed. No: Keep the whole partition as the frame.
- Apply a function with F.func(...).over(w)
- Spark shuffles by the partition keys and sorts each partition
- Every row keeps its detail plus the window value
Syntax
from pyspark.sql.window import Window
w = Window.partitionBy("region").orderBy(F.col("amount").desc())
w_all = Window.partitionBy("region") # whole partition
w_run = w.rowsBetween(Window.unboundedPreceding, Window.currentRow)
df.withColumn("x", F.sum("amount").over(w_all))Example
from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window
spark = SparkSession.builder.appName("techtown-spark").getOrCreate()
orders = spark.createDataFrame([
(1, 101, "North", "Laptop", 1, 55000.0, "2026-01-05"),
(2, 102, "West", "Phone", 2, 30000.0, "2026-01-06"),
(3, 101, "North", "Mouse", 3, 1500.0, "2026-01-07"),
(4, 103, "South", "Laptop", 1, 52000.0, "2026-01-07"),
(5, 102, "West", "Tablet", 1, 25000.0, "2026-01-09"),
(6, 104, "North", "Phone", 1, 18000.0, "2026-01-10"),
(7, 103, "South", "Mouse", 2, 1000.0, "2026-01-11"),
(8, 105, "West", "Laptop", 1, 60000.0, "2026-01-12"),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))
by_region = Window.partitionBy("region")
result = (orders
.withColumn("region_total", F.sum("amount").over(by_region))
.withColumn("pct_of_region", F.round(100 * F.col("amount") / F.col("region_total"), 1))
.withColumn("orders_in_region", F.count("*").over(by_region))
.select("order_id", "region", "amount", "region_total", "pct_of_region", "orders_in_region")
.orderBy("region", "order_id"))
result.show()
# Output:
# +--------+------+-------+------------+-------------+----------------+
# |order_id|region| amount|region_total|pct_of_region|orders_in_region|
# +--------+------+-------+------------+-------------+----------------+
# | 1| North|55000.0| 74500.0| 73.8| 3|
# | 3| North| 1500.0| 74500.0| 2.0| 3|
# | 6| North|18000.0| 74500.0| 24.2| 3|
# | 4| South|52000.0| 53000.0| 98.1| 2|
# | 7| South| 1000.0| 53000.0| 1.9| 2|
# | 2| West|30000.0| 115000.0| 26.1| 3|
# | 5| West|25000.0| 115000.0| 21.7| 3|
# | 8| West|60000.0| 115000.0| 52.2| 3|
# +--------+------+-------+------------+-------------+----------------+
Key points
- partitionBy = which rows belong together; orderBy = order inside; frame = which rows the function sees
- No orderBy: frame is the whole partition. With orderBy: default frame ends at the current row
- Window functions never reduce the row count, unlike groupBy
- A window with no partitionBy pulls every row into a single task: avoid it on large data
- Reuse one Window object for several columns so Spark can share the same shuffle and sort