Window Function

Add a rank, a bucket, a running total or a percent of total as a new column. Your rows stay as they are.

What it does

A window function looks at other rows to compute a value for the current row. It adds one column. It does not join rows together.

Before. Four sales people:

After. RANK, ordered by sales and partitioned by region:

Four rows in, four rows out. The rank starts again in each region.

The functions you can pick

The last group is the useful one people miss: an ordinary total that does not collapse the table. Add a SUM partitioned by region and every row carries its region's total beside its own value, ready to compare.

Buckets, shares and totals

NTILE asks for a Number of buckets. Four gives quartiles, ten gives deciles, two splits the table in half. Rows are dealt out in the order you set, so an order column is required, and the buckets come out as even as the row count allows.

PERCENT OF TOTAL is the one that used to take two steps, a partitioned SUM and then a Math step to divide. It takes a number column and divides each value by the total of its partition, times 100. With no partition the total is the whole table. A group whose total is zero comes back blank rather than as an infinity, so nothing downstream is poisoned.

FIRST VALUE and LAST VALUE read the whole group, not the rows up to the current one, so a row in the middle sees the same answer as the first and last rows do. Order by the column that decides which is first: order by a date and FIRST VALUE is the earliest reading.

Add a window column

1. Type rank in the Search transforms box, in the Pipeline panel.
2. Select Window Function in the results. The panel opens below the grid.
3. Open Function under Configuration and pick a function.
4. Open Choose column… and pick a column. This field is hidden for ROW_NUMBER, RANK and DENSE_RANK.
5. Open Order by (optional) and pick the column that sets the order.
6. Open Partition by (optional) and pick a group column. Skip this for one list.
7. Type a name in Output column name.
8. Click Apply.

The grid updates at once. The step appears in your pipeline, where you can edit or delete it later.

Order by and Partition by

Order by sets the sequence that the function reads. Ranks, row numbers and running totals all follow it.

When you pick an order column, a direction switch appears. Largest first makes rank 1 the highest value, which fits leaderboards. Smallest first makes rank 1 the lowest value.

Partition by splits the table into groups. The function starts again in each group. The example above partitions by region.

Tips

• For a top-seller list, keep Largest first selected. Rank 1 then marks the biggest value.
• Set Order by for LEAD and LAG. Without it the function follows the file order.
• RUNNING TOTAL, PERCENT OF TOTAL, SUM and AVG all need a number column, and say so rather than failing in the database.
• NTILE, PERCENT RANK, CUMULATIVE SHARE, FIRST VALUE and LAST VALUE all need an Order by column. Without one there is no first, no last and no position to report.
• Leave Output column name empty. The app then names the column rank_result or similar.
• Both order fields are optional here. For a moving average, use Rolling Window instead.

For SQL users

The step runs as one OVER clause:

SELECT *, RANK() OVER (PARTITION BY "region" ORDER BY "sales" DESC) AS "rank_in_region" FROM data

Percent of total is a partitioned SUM used as a divisor, and LAST VALUE carries the frame that makes it mean the last row of the group rather than the current one:

SELECT *, "sales" * 100.0 / SUM("sales") OVER (PARTITION BY "region") AS "pct_of_region" FROM data

SELECT *, LAST_VALUE("reading") OVER (
  PARTITION BY "sensor" ORDER BY "taken_at"
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "final_reading" FROM data

Related Operations

• Pivot - Pivot table (rows to columns)
• Smallest - Get nth smallest value as a column
• Largest - Get nth largest value as a column