8.1. Description

Icon:

Number of inputs: 1.
Number of outputs: 1.

Definition

The window function lets you perform a calculation on a set of records that is linked to the current record in the input dataset.

Unlike conventional aggregation functions, the window function does not aggregate records into a single output record: the records retain their separate identities.

Configuration

In order to configure a window function two things need to be specified:

Frame boundaries (and - optionally - sorting of the rows in the window).

Fields to be calculated.

Practical examples

Example 1: Simple Moving Average

Before operation:

Date	Sales
2024-01-01	100
2024-01-02	150
2024-01-03	200
2024-01-04	250
2024-01-05	300

Node Configuration:

Window Definition: Rows between 2 preceding and 0 following.
Field to Calculate: Moving average of “Sales”.

After operation:

Date	Sales	Moving Average
2024-01-01	100	100
2024-01-02	150	125
2024-01-03	200	150
2024-01-04	250	200
2024-01-05	300	250

Example 2: Ranking within Groups

Before operation:

Department	Employee	Sales
A	John	300
A	Jane	200
B	Mike	400
B	Lisa	500

Node Configuration:

Window Definition: Partition by “Department”, ordered by “Sales”.
Field to Calculate: Rank of each employee within their department based on “Sales”.

After operation:

Department	Employee	Sales	Rank
A	John	300	1
A	Jane	200	2
B	Lisa	500	1
B	Mike	400	2

Example 3: Cumulative Sum

Before operation:

Category	Value
A	10
A	20
A	30
B	15
B	25

Node Configuration:

Window Definition: Partition by “Category”, ordered by “Value”.
Field to Calculate: Cumulative sum of “Value”.

After operation:

Category	Value	Cumulative Sum
A	10	10
A	20	30
A	30	60
B	15	15
B	25	40

Example 4: Lag and Lead Difference

Before operation:

Date	Sales
2024-01-01	100
2024-01-02	150
2024-01-03	200
2024-01-04	250
2024-01-05	300

Node Configuration:

Window Definition: Rows between unbounded preceding and current row.
Field to Calculate: Lag of “Sales” by 1 row, and Lead of “Sales” by 1 row.

After operation:

Date	Sales	Sales Lag	Sales Lead
2024-01-01	100	NULL	150
2024-01-02	150	100	200
2024-01-03	200	150	250
2024-01-04	250	200	300
2024-01-05	300	250	NULL

Example 5: Text Concatenation

Before operation:

Group	Comments
A	Great performance
A	Needs improvement
B	Excellent delivery
B	Satisfactory results

Node Configuration:

Window Definition: Partition by “Group”.
Field to Calculate: Concat With Comma for “Comments”.

After operation:

Group	Concat Comments
A	Great performance, Needs improvement
A	Great performance, Needs improvement
B	Excellent delivery, Satisfactory results
B	Excellent delivery, Satisfactory results