Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Spark and DataFrames

Joins, Aggregations and Window Functions

Combine and summarise data in Spark: join types, broadcast joins, groupBy and agg, pivot, and window functions such as row_number, rank, lag and running totals.

Most analysis boils down to three moves: bring tables together (joins), collapse rows into summaries (aggregations) and compare a row with its neighbours (window functions). These are also the operations that shuffle data, so they are where Spark performance is decided. See Spark architecture for why.

Real PySpark is shown read-only. The runnable stand-ins use pandas, which has close equivalents.

Joins

Python
from pyspark.sql import functions as F

orders = spark.read.table("main.sales.orders")
customers = spark.read.table("main.sales.customers")

joined = orders.join(customers, on="customer_id", how="inner")

Join types, with the same meaning as in SQL:

how= Keeps
inner Rows with a match in both
left All left rows, matched right columns or null
right All right rows
full All rows from both
left_semi Left rows that have a match (only left columns)
left_anti Left rows with no match
cross Every combination (use rarely)

Joining on different column names or several conditions:

Python
orders.join(customers, orders.cust == customers.id, "left")

When both sides have columns with the same name you must alias or drop one, or you get "ambiguous reference" errors.

Broadcast joins

A normal join shuffles both tables. If one side is small (megabytes, not gigabytes), Spark can broadcast it: send a copy to every executor so the big table is not shuffled at all.

Python
from pyspark.sql.functions import broadcast
result = orders.join(broadcast(customers), "customer_id")

Spark broadcasts automatically below a size threshold, and AQE can switch strategies at run time, but an explicit hint is useful when you know better.

Aggregations

Python
summary = (orders
    .groupBy("city")
    .agg(
        F.count("*").alias("orders"),
        F.sum("amount").alias("revenue"),
        F.avg("amount").alias("avg_order"),
        F.max("order_ts").alias("last_order"),
    )
    .orderBy(F.col("revenue").desc()))

Other useful functions: countDistinct, approx_count_distinct (fast, approximate), collect_list, first, stddev. Pivot turns row values into columns:

Python
orders.groupBy("city").pivot("year", [2023, 2024]).sum("amount")

Listing the pivot values avoids an extra pass over the data.

Window functions

A window function computes a value for each row using a set of related rows, without collapsing them. You define a window with partitionBy (groups), orderBy (sort inside the group) and optionally a frame.

Python
from pyspark.sql.window import Window

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

ranked = (orders
    .withColumn("rn", F.row_number().over(w))
    .withColumn("prev_amount", F.lag("amount").over(w))
    .filter(F.col("rn") <= 3))                      # top 3 orders per city

running = Window.partitionBy("city").orderBy("order_ts").rowsBetween(Window.unboundedPreceding, Window.currentRow)
orders.withColumn("running_revenue", F.sum("amount").over(running))

row_number gives a unique sequence, rank leaves gaps after ties and dense_rank does not. lag and lead read the previous or next row. Windows need a shuffle on the partition keys, and a window without partitionBy puts all rows in one partition, which does not scale.

Stand-ins with pandas

The first stand-in does a left join, a left anti join and an aggregation.

Python Stand-in (pandas): joins and groupBy

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.

The second stand-in shows the window ideas: rank inside a group, previous value and a running total.

Python Stand-in (pandas): row_number, lag and running total

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.

Practical tips

  • Filter before joining and select only needed columns, so less data is shuffled.
  • Beware duplicates in the join key: a key that appears twice on both sides multiplies rows.
  • Null keys never match in a join; handle them first if they matter.
  • Check the row count after a join against what you expect.
  • Skew: a hot key can overload one task; AQE may split it, otherwise salt the key.

Common mistakes

  • Joining on a column name that exists on both sides and then selecting it ambiguously.
  • Using count("*") and countDistinct interchangeably.
  • Forgetting partitionBy in a window, which collapses everything into one partition.
  • Pivoting on a column with thousands of values.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join type returns left rows that have NO match on the right?

    left_anti keeps only unmatched left rows. left_semi keeps matched ones.
  2. 2. Why use broadcast(small_df) in a join?

    A broadcast join sends the small table to every executor, so the big table stays where it is.
  3. 3. What is the difference between rank and row_number?

    With ties, row_number still counts 1,2,3 while rank gives equal values and then skips numbers.
  4. 4. What happens if a window has no partitionBy?

    Without partitions Spark must process every row in a single task.

Summary

  • Joins combine tables; know inner, left, semi and anti, and prefer broadcast joins for small sides.
  • groupBy().agg() collapses rows into summaries; pivot reshapes values into columns.
  • Window functions compute per-row values over partitions: ranks, lag, running totals.
  • All three can shuffle: filter and select early, and watch for duplicate keys and skew.

Next: Spark SQL, Views and UDFs