Window Functions Reference

Perform calculations across a set of rows related to the current row without collapsing row groups.

Window.partitionBy().orderBy()

Return: WindowSpec

Defines a window specification defining partition boundaries, sort ordering, and frame bounds.

Used In: Top-N per Group, Running Totals, Moving Averages

Syntax signature:

from pyspark.sql.window import Window

w = Window.partitionBy('dept').orderBy(col('salary').desc())

Code snippet:

python

df.withColumn('rank', row_number().over(w))

Expected Output:

department	salary	rank
Engineering	120000	1
Engineering	100000	2

Remember: Without `partitionBy()`, ALL rows are moved to a SINGLE partition/executor, risking severe OutOfMemory errors.

row_number vs rank vs dense_rank

Return: Column

Assigns sequential ranking indices to rows within each window partition.

Used In: Deduplication, Salary Rankings, Leaderboards

Syntax signature:row_number().over(w) | rank().over(w) | dense_rank().over(w)

Code snippet:

python

df.select('salary',
    row_number().over(w).alias('rn'),
    rank().over(w).alias('rk'),
    dense_rank().over(w).alias('dr')
)

Expected Output:

Salary [100, 100, 90]
- row_number: [1, 2, 3]
- rank: [1, 1, 3] (skips rank 2)
- dense_rank: [1, 1, 2] (no rank gaps)

Remember: Use `row_number()` for deterministic Top-1 deduplication. Use `dense_rank()` when ties should share rank without skipping numbers.

lead & lag

Return: Column

Accesses data from a subsequent (lead) or preceding (lag) row relative to current row within partition.

Used In: Financial Analysis, Trend Calculation, Funnel Analytics

Syntax signature:lag('col', offset=1, default=None).over(w)

Code snippet:

python

w = Window.partitionBy('account_id').orderBy('tx_date')
df.withColumn('prev_tx_amount', lag('amount', 1, 0.0).over(w))

Expected Output:

account_id	tx_date	amount	prev_tx_amount
A101	Jan 01	100	0.0
A101	Jan 05	250	100.0

Remember: Ideal for calculating month-over-month (MoM) growth, time differences between events, and sessionization.

Window.rowsBetween / rangeBetween

Return: WindowSpec

Defines sliding frame boundaries relative to current row index (`rowsBetween`) or numeric value (`rangeBetween`).

Used In: Moving Averages, Cumulative Running Totals

Syntax signature:

w = Window.partitionBy('dept').orderBy('date')\
  .rowsBetween(Window.currentRow - 2, Window.currentRow)

Code snippet:

python

df.withColumn('3_day_moving_avg', avg('sales').over(w))

Expected Output:Calculates average over current row and previous 2 rows

Remember: `Window.unboundedPreceding` and `Window.unboundedFollowing` extend the window frame to the partition start/end.