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

  1. Start with a DataFrame of detail rows
  2. Define w = Window.partitionBy(keys)
  3. Does the calculation depend on row order? Yes: Add .orderBy(cols), and a frame if needed. No: Keep the whole partition as the frame.
  4. Apply a function with F.func(...).over(w)
  5. Spark shuffles by the partition keys and sorts each partition
  6. 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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic