row_number, rank & dense_rank
Apache Spark · Window Functions
Theory
These three ranking functions number the rows inside each window partition in the order given by orderBy. row_number() always gives 1, 2, 3, ... with no repeats, even when two rows tie, so the tie is broken arbitrarily unless you add a tie-breaker column. rank() gives tied rows the same number and then skips, so two orders tied for first are both 1 and the next is 3. dense_rank() also gives ties the same number but does not skip, so the next one is 2.
For example, ranking orders by amount within West where two Phones both cost 30000: row_number gives 1 and 2, rank gives 1 and 1 then 3, dense_rank gives 1 and 1 then 2. Ranking functions need an ordered window; Spark raises an error if the window has no orderBy. Related functions are percent_rank(), ntile(n) and cume_dist().
Why we use it
Ranking is the basis of top-N reports, leaderboards, picking the latest record per key and removing duplicates. Choosing the right function decides how ties behave, which is a very common source of wrong numbers and a favourite interview question.
When to use it
Use row_number when you need exactly one row per position (dedup, latest record). Use rank for competition style ranking where ties share a place and gaps follow. Use dense_rank for 'Nth highest value' questions where ties should not create gaps.
How it works
- Rows inside one partition, sorted by orderBy
- Walk the rows in order
- Is this row tied with the previous one? Yes: rank and dense_rank repeat the number; row_number still adds 1. No: All three move to the next number.
- rank jumps by the number of tied rows; dense_rank goes up by one
- Each row gets its position number
Syntax
w = Window.partitionBy("region").orderBy(F.desc("amount"))
F.row_number().over(w)
F.rank().over(w)
F.dense_rank().over(w)
F.ntile(4).over(w) # quartilesExample
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, "West", "Phone", 30000.0), (2, "West", "Phone", 30000.0),
(3, "West", "Tablet", 25000.0), (4, "West", "Laptop", 60000.0),
(5, "North", "Laptop", 55000.0), (6, "North", "Mouse", 1500.0),
], "order_id INT, region STRING, product STRING, amount DOUBLE")
by_amount = Window.partitionBy("region").orderBy(F.desc("amount"))
by_amount_id = Window.partitionBy("region").orderBy(F.desc("amount"), "order_id") # tie-breaker
ranked = (orders
.withColumn("row_number", F.row_number().over(by_amount_id))
.withColumn("rank", F.rank().over(by_amount))
.withColumn("dense_rank", F.dense_rank().over(by_amount))
.orderBy("region", "row_number"))
ranked.show()
# Output:
# +--------+------+-------+-------+----------+----+----------+
# |order_id|region|product| amount|row_number|rank|dense_rank|
# +--------+------+-------+-------+----------+----+----------+
# | 5| North| Laptop|55000.0| 1| 1| 1|
# | 6| North| Mouse| 1500.0| 2| 2| 2|
# | 4| West| Laptop|60000.0| 1| 1| 1|
# | 1| West| Phone|30000.0| 2| 2| 2|
# | 2| West| Phone|30000.0| 3| 2| 2|
# | 3| West| Tablet|25000.0| 4| 4| 3|
# +--------+------+-------+-------+----------+----+----------+
Key points
- row_number: unique 1..n; rank: ties share a number then skip; dense_rank: ties share, no gaps
- Ranking functions need orderBy in the window; Spark errors without it
- Add a tie-breaker column (e.g. order_id) to make row_number deterministic
- rank and dense_rank only differ when ties exist
- ntile(n) splits each partition into n roughly equal buckets