Search

1. Preface
2. Transformations
3. Source transformation
4. Target transformation
5. Aggregator transformation
6. Cleanse transformation
8. Deduplicate transformation
9. Expression transformation
10. Filter transformation
11. Hierarchy Builder transformation
12. Hierarchy Parser transformation
13. Hierarchy Processor transformation
14. Input transformation
15. Java transformation
16. Java transformation API reference
17. Joiner transformation
18. Labeler transformation
19. Lookup transformation
20. Mapplet transformation
21. Normalizer transformation
22. Output transformation
23. Parse transformation
24. Python transformation
25. Rank transformation
26. Router transformation
27. Rule Specification transformation
28. Sequence Generator transformation
29. Sorter transformation
30. SQL transformation
31. Structure Parser transformation
32. Transaction Control transformation
33. Union transformation
34. Velocity transformation
35. Verifier transformation
36. Web Services transformation

# Example: Run an aggregate function on a window

You want to compare the salary of each employee with the average salary for the corresponding department.
The following table lists the department names, the employee identification number, and the employee salary:
`Department`
`Employee`
`Salary`
`Development`
`11`
`5200`
`Development`
`7`
`4200`
`Development`
`9`
`4500`
`Development`
`8`
`6000`
`Development`
`10`
`5200`
`Personnel`
`5`
`3500`
`Personnel`
`2`
`3900`
`Sales`
`3`
`4800`
`Sales`
`1`
`5000`
`Sales`
`4`
`4800`
You set an unbounded frame to include all employees in the calculation, and you define an aggregate function to calculate the difference between the salary of each employee and the average salary in the department.

## Window Properties

You define the following window properties on the
Window
tab:
Property
Value
Description
Start offset
All Preceding Rows
Describes the number of rows that appear before the current input row.
End offset
All Following Rows
Describes the number of rows that appear after the current input row.
Order key
Salary Ascending
Arranges the data by increasing salary.
Partition key
Department
Groups the rows according to department.
When you select All Preceding Rows and All Following Rows, the function includes all partition rows. For example, suppose the current row is the third row. The third row is in the "Development" partition, so the frame includes the third row in addition to all rows before and after the third row in the "Development" partition.

## Window Function

Because you configure window properties in the Expression transformation, you can use an aggregate function as a window function.
You define the following aggregate function to calculate the difference between the salary of each employee and the average salary in the corresponding department:
`Salary - AVG ( Salary ) = Salary_Diff`

## Output

The transformation produces the following salary differences:
`Department`
`Employee`
`Salary`
`Salary_Diff`
`Development`
`11`
`5200`
`-820`
`Development`
`7`
`4200`
`-520`
`Development`
`9`
`4500`
`180`
`Development`
`8`
`6000`
`180`
`Development`
`10`
`5200`
`980`
`Personnel`
`5`
`3500`
`200`
`Personnel`
`2`
`3900`
`200`
`Sales`
`3`
`4800`
`-66`
`Sales`
`1`
`5000`
`-66`
`Sales`
`4`
`4800`
`134`
You can identify which employees are making less or more than the average salary within the same department. Based on this information, you can add other transformations to learn more about the data. For example, you might add a Rank transformation to produce a numerical rank for each employee within the same department.

Updated November 19, 2021

Resources