row_number / rank

What a window is

groupBy turns each group into one row. A window keeps every row and adds a column computed over that row's group, called its partition, in a given order. You define it once with Window.partitionBy(...).orderBy(...) and apply functions with .over(w).

Three ways to number rows

Ranking players by points within each team. Bo and Cy tie on 85:

• row_number always counts 1, 2, 3, 4. For the tie it picks an order arbitrarily: Bo could be 3 tomorrow.
• rank gives ties the same rank and skips the next numbers, like a sports ranking: two second places, then fourth.
• dense_rank gives ties the same rank without gaps: 1, 2, 2, 3.

Run the example

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("team").orderBy(F.col("points").desc())
result = scores.select(
    "team", "player", "points",
    F.row_number().over(w).alias("row_number"),
    F.rank().over(w).alias("rank"),
    F.dense_rank().over(w).alias("dense_rank"),
)

SELECT team, player, points,
       ROW_NUMBER() OVER w AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM scores
WINDOW w AS (PARTITION BY team ORDER BY points DESC)

Top N per group

The classic interview pattern: rank inside each group, then filter. The ranking function you choose decides what happens at the cut-off.

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("team").orderBy(F.col("points").desc())
result = (scores
    .withColumn("r", F.dense_rank().over(w))
    .filter(F.col("r") <= 2))

SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (
    PARTITION BY team ORDER BY points DESC) AS r
  FROM scores)
WHERE r <= 2

Under the hood

A window needs all rows of a partition together and sorted. Spark shuffles by the partition keys, then sorts each partition by the partition and order keys, then streams through the sorted rows. In the plan: Exchange hashpartitioning(team) → Sort → Window.

Several window functions over the same window spec share one shuffle and one sort, so computing row_number, rank and lag together is cheap. A different partitionBy means another shuffle.

Common mistakes