5.3 Window Functions and Complex Joins in Spark SQL

In standard relational queries, aggregation operations grouped by keys (using GROUP BY) collapse all contributing rows into a single summary record per group. While suitable for high-level metric rollups, grouping destroys row-level detail. Window functions overcome this limitation by computing aggregations, moving averages, relative ranks, or offsets over a designated group of rows (called a window frame) while preserving the unique identity of each individual row in the output.

Alongside window functions, advanced data pipelines rely on complex join strategies—such as Semi Joins, Anti Joins, and Cross Joins—to filter, reconcile, and combine distributed datasets efficiently across cluster nodes.

---

1. Window Specification Architecture: Syntax and Execution Mechanics

A window function calculates a return value for every row in a relation based on a defined subset of rows related to the current row. In Spark SQL, window operations are declared using the OVER clause.

The Anatomy of the OVER Clause

A window specification comprises three distinct clauses:

function_name(expression) OVER (
    [PARTITION BY partition_col1, partition_col2, ...]
    [ORDER BY sort_col1 [ASC|DESC] [NULLS FIRST|LAST], ...]
    [ROWS|RANGE BETWEEN frame_start AND frame_end]
)

1. PARTITION BY: Divides input rows into logical partitions across cluster nodes. The window function evaluates independently within each partition. If omitted, the entire dataset is treated as a single partition (which can cause severe executor memory bottlenecks).
2. ORDER BY: Determines the physical ordering of rows within each partition. Mandatory for ranking and offset functions, and establishes cumulative ordering for aggregate functions.
3. Frame Specification (ROWS / RANGE): Defines the subset of rows within the partition that bounds the calculation relative to the current row.

Reusing Window Definitions: The WINDOW Clause

When multiple analytical functions share the same partitioning and ordering rules, writing duplicate OVER (...) clauses creates verbose, error-prone code. Spark SQL supports the ANSI SQL WINDOW clause to define reusable window aliases at the end of the query:

SELECT 
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER w AS dept_avg_salary,
    SUM(salary) OVER w AS dept_running_total,
    MAX(salary) OVER w AS dept_max_salary
FROM corporate.employees
WINDOW w AS (
    PARTITION BY department_id 
    ORDER BY salary DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

Programmatic PySpark Window API

In PySpark, window operations are formulated using pyspark.sql.window.Window:

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

# Define the window specification
window_spec = (
    Window.partitionBy("department_id")
    .orderBy(F.col("salary").desc())
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)
)

# Apply the window specification
df_ranked = df.withColumn("dept_running_total", F.sum("salary").over(window_spec))

Physical Execution Mechanics: Shuffling and Sorting

A window physical plan can include distribution, sorting and a window operator; these are operators, not necessarily three separate scheduler stages:

1. Exchange (Shuffle): If PARTITION BY is specified, Spark applies hash-partitioning on the partition columns to ensure all rows sharing the same partition key land on the same executor JVM.
2. In-Memory Sort (SortExec): Within each partition, rows are sorted by the ORDER BY columns.
3. Window Operator (WindowExec): A specialized physical operator sweeps through the sorted partition, maintaining a sliding frame buffer to evaluate aggregates or rank calculations.

---

2. Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE

Ranking functions assign an ordinal integer to each row within a partition based on the ORDER BY specification. How these functions handle duplicate (tied) values is one of the most frequently tested concepts on the Databricks certification exam.

Comparative Ranking Mechanics

• ROW_NUMBER(): Generates a strict, unique sequential integer (1, 2, 3, ...) for every row in the partition. If two rows have identical sort values, ROW_NUMBER() assigns distinct numbers non-deterministically unless additional tie-breaking columns are added to ORDER BY.
• RANK(): Assigns identical rank numbers to rows with identical sort values. However, it leaves gaps in the sequence equal to the number of tied rows. The next non-tied row receives a rank equal to: $1 + \text{count of all preceding rows}$.
• DENSE_RANK(): Assigns identical rank numbers to rows with identical sort values, but never leaves gaps. The next non-tied row receives the immediate next sequential integer.
• NTILE(n): Divides rows within each partition as equally as possible into $n$ numbered buckets (quantiles), ranging from 1 to $n$. If the row count is not evenly divisible by $n$, earlier buckets receive one extra row.
• PERCENT_RANK(): Computes the relative rank of a row as a percentage from 0.0 to 1.0, calculated as: $\frac{\text{RANK} - 1}{\text{Total Rows in Partition} - 1}$.
• CUME_DIST(): Calculates the cumulative distribution of a value, defined as: $\frac{\text{Number of rows preceding or tied with the current row in sort order}}{\text{Total Rows in Partition}}$.

Comparative Ranking Table on Duplicate Values

Consider a department containing eight employees with the following salaries: [100k, 90k, 90k, 80k, 70k, 70k, 70k, 50k]:

Notice that for RANK(), when E2 and E3 tie at rank 2, E4 jumps immediately to rank 4 (skipping 3). For DENSE_RANK(), E4 receives rank 3 without any gap.

---

3. Analytic and Offset Functions

Offset functions allow queries to inspect values from adjacent rows without performing self-joins.

Offset Functions: LAG and LEAD

• LAG(col, [offset=1], [default=null]): Returns the value of col from $N$ rows before the current row within the partition.
• LEAD(col, [offset=1], [default=null]): Returns the value of col from $N$ rows after the current row within the partition.

SELECT 
    customer_id,
    order_date,
    order_amount,
    -- Amount spent on the immediate previous order
    LAG(order_amount, 1, 0.0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
    -- Days elapsed since the previous order
    datediff(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_prev_order
FROM sales.orders;

Value Functions: FIRST_VALUE and LAST_VALUE

• FIRST_VALUE(col [, ignoreNulls]): Returns the value of col from the first row of the window frame.
• LAST_VALUE(col [, ignoreNulls]): Returns the value of col from the last row of the window frame.

The Classic LAST_VALUE() Exam Trap

A critical trap occurs when developers invoke LAST_VALUE() using the default window frame. By default, when ORDER BY is present, the frame is:

$$\texttt{RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW}$$

Under this cumulative frame, the "last row" evaluated by the function is the last peer with the current ordering value, which is the current row when ordering values are unique. Consequently, LAST_VALUE() is evaluated over the current cumulative frame as the query progresses down the partition, rather than the final value of the entire partition.

-- BUG: Returns the current row's order_amount, NOT the final order amount!
SELECT customer_id, order_date, order_amount,
       LAST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS wrong_last
FROM sales.orders;

-- CORRECT: Explicitly extend the frame to the end of the partition
SELECT customer_id, order_date, order_amount,
       LAST_VALUE(order_amount) OVER (
           PARTITION BY customer_id 
           ORDER BY order_date, order_id 
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS true_last
FROM sales.orders;

---

4. Window Frame Specifications: ROWS vs RANGE

The frame clause defines the physical or logical slice of rows within a partition evaluated by the window function.

Frame Boundary Syntax

[ROWS | RANGE] BETWEEN frame_start AND frame_end

Valid boundary keywords include:

• UNBOUNDED PRECEDING: Frame begins at the very first row of the partition.
• n PRECEDING: Frame starts $n$ physical rows before the current row (ROWS) or $n$ value units before (RANGE).
• CURRENT ROW: The active row being evaluated.
• n FOLLOWING: Frame extends $n$ physical rows ahead (ROWS) or $n$ value units ahead (RANGE).
• UNBOUNDED FOLLOWING: Frame extends to the very last row of the partition.

ROWS vs RANGE: The Fundamental Difference

• ROWS (Physical Offsets): Operates purely on row index positions. For example, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING always evaluates exactly up to 3 rows (the previous row, the current row, and the next row), regardless of whether column values are identical.
• RANGE (Logical Value Offsets): Operates on the actual values of the ORDER BY expression. In Spark SQL:
  1. Tied Sort Values: If multiple rows share the exact same value in the ORDER BY column, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW considers all tied rows as peers and evaluates the aggregate across all of them together up to the end of the tied group.
  2. Numeric and Date Intervals: RANGE can evaluate logical intervals, such as RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW, aggregating all transactions that occurred in the 7 days prior to the current transaction date regardless of how many rows exist.

Default Window Frame Rules

The following defaults apply to ordinary aggregate/value windows. Ranking and offset functions can require their own frames or reject explicit custom frames; do not impose this table on every function.

---