A nested aggregate function in a window function performs a separate calculation for each partition.
When you include nested aggregate functions in an Expression transformation and configure the transformation for window functions, the function performs the calculation separately for each partition.
You partition the data by P2 and specify a frame of All Preceding Rows and All Following Rows. The window functions perform the following calculations:
COUNT (P1) produces one value for every row. COUNT returns the number of rows in the partition that have non-null values.
MEDIAN of that value produces the median of a window of values generated by COUNT.
The window functions produce the following outputs:
P1
P2
Output
10
1
3
7
1
3
12
1
3
11
2
4
13
2
4
8
2
4
10
2
4
You can nest aggregate functions with multiple window functions. For example:
LAG ( LEAD( MAX( FIRST ( p1 )))
You can nest multiple window functions LEAD and LAG, but you cannot nest more than one aggregate function within another aggregate function.
ASK INFAPreview
