Window Function
Add a rank, a row number, or a running 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:
| region | rep | sales |
|---|---|---|
| West | Ana | 500 |
| West | Bo | 700 |
| East | Cy | 300 |
| East | Di | 900 |
After. RANK, ordered by sales and partitioned by region:
| region | rep | sales | rank_in_region |
|---|---|---|---|
| West | Ana | 500 | 1 |
| West | Bo | 700 | 2 |
| East | Cy | 300 | 1 |
| East | Di | 900 | 2 |
Four rows in, four rows out. The rank starts again in each region.
The functions you can pick
| Function | What the new column holds | Needs a column |
|---|---|---|
| ROW_NUMBER | 1, 2, 3 in order, with no ties | No |
| RANK | The position, with gaps after a tie | No |
| DENSE_RANK | The position, with no gaps | No |
| LEAD | The value from the next row | Yes |
| LAG | The value from the row before | Yes |
| RUNNING TOTAL (cumulative sum) | The total up to this row | Yes, a number column |
Add a window column
- Type rank in the Search transforms box, in the Pipeline panel.
- Select Window Function in the results. The panel opens below the grid.
- Open Function under Configuration and pick a function.
- Open Choose column… and pick a column. This field is hidden for ROW_NUMBER, RANK and DENSE_RANK.
- Open Order by (optional) and pick the column that sets the order.
- Open Partition by (optional) and pick a group column. Skip this for one list.
- Type a name in Output column name.
- 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 lists only number columns in Column.
- 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 dataRelated Operations
- Pivot - Pivot table (rows to columns)
- Smallest - Get nth smallest value as a column
- Largest - Get nth largest value as a column