Window & Ranking Functions

Window functions retain row detail. over partitions the rows and by sorts within each partition. Each example's Run and Copy includes the orders model. See context-sensitive functions for ranking, ties, and neighboring-row comparisons.

RANK

Assigns a rank to each row within a partition based on the specified ordering. Rows with equal values receive the same rank, and the next rank is incremented by the number of tied rows (e.g., 1, 2, 2, 4).

Inputs:

Any (concept to rank)Any (partition key, optional)Any (sort expression)

Output:

INTEGER

Purpose:

property

Example:

select order_id, customer_id, rank order_id over customer_id by order_total desc as amount_rank;

Tips

The concept after rank identifies which entity is being ranked. The over clause specifies the partition, and by specifies the ordering.

ROW_NUMBER

Assigns a unique sequential integer to each row within a partition, starting at 1. Unlike RANK, ROW_NUMBER always produces distinct values even for ties.

Inputs:

Any (concept for grain)Any (partition key, optional)Any (sort expression)

Output:

INTEGER

Purpose:

property

Example:

select order_id, customer_id, row_number order_id over customer_id by order_id asc as order_sequence;

LAG

Accesses a value from a previous row within the partition, based on the specified offset and ordering. Useful for computing differences between consecutive rows, such as period-over-period changes.

Inputs:

INTEGER (offset)Any (concept to access)Any (partition key, optional)Any (sort expression)

Output:

Same as input concept type

Purpose:

property

Example:

select order_id, customer_id, lag 1 order_total over customer_id by order_id asc as previous_amount;

Tips

The integer after lag specifies how many rows back to look. lag 1 gets the immediately preceding row's value. It returns null when no preceding row exists. An offset of one means one row, not necessarily one calendar month; fill missing periods before interpreting it as a month-over-month comparison.

LEAD

Accesses a value from a subsequent row within the partition, based on the specified offset and ordering. Useful for computing forward-looking comparisons.

Inputs:

INTEGER (offset)Any (concept to access)Any (partition key, optional)Any (sort expression)

Output:

Same as input concept type

Purpose:

property

Example:

select order_id, customer_id, lead 1 order_total over customer_id by order_id asc as next_amount;