Search

1. Preface
2. Introduction to Informatica Data Engineering Integration
3. Mappings
4. Mapping Optimization
5. Sources
6. Targets
7. Transformations
8. Python Transformation
9. Data Preview
10. Cluster Workflows
11. Profiles
12. Monitoring
13. Hierarchical Data Processing
14. Hierarchical Data Processing Configuration
15. Hierarchical Data Processing with Schema Changes
16. Intelligent Structure Models
17. Blockchain
18. Stateful Computing
19. Appendix A: Connections Reference
20. Appendix B: Data Type Reference
21. Appendix C: Function Reference

# Aggregate Function as Window Function Example

You work for a human resources group and you want to compare each of your employees' salaries with the average salary in his or her department:
The following table lists the department names, the employee identification number, and the employee's 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 each employee's salary and the average salary in the department.

## Windowing Properties

You define the following windowing properties on the Windowing tab:
Property
Description
Order key
Salary Ascending
. Arranges the data by increasing salary.
Partition key
Department
. Groups the rows according to department.
Start offset
All Rows Preceding
End offset
All Rows Following
With an unbounded frame, the aggregate function includes all partition rows in the calculation.
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

An aggregate function acts as a window function when you configure the transformation for windowing.
You define the following aggregate function to calculate the difference between each employee's salary and the average salary in his or her 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 for his or her department. Based on this information, you can add other transformations to learn more about your data. For example, you might add a Rank transformation to produce a numerical rank for each employee within his or her department.
Actions
Resources