Skip to main content

Celonis Product Documentation

WINDOW_AVG
Description

WINDOW_AVG can be used to calculate the average over a user-defined window. It is possible to specify a column based ordering and partitioning.

The output type is FLOAT.

Syntax
 WINDOW_AVG ( column, start, end [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] )

  • column: The input column where values should be averaged. Supported input types: INT, FLOAT

  • start: Amount of rows where the window should start, relative to the window. Must be of type INT.

  • end: Amount of rows where the window should end, relative to the window. Must be of type INT.

  • sort_column: Optional sorting column to specify an order.

  • sorting: Each of these columns can have an optional tag specifying the ordering of the column. Default is ascending:

    • ASC: Ascending order

    • DESC: Descending order

  • partition_column: Optional partition column to specify groups in which WINDOW_AVG should operate.

One or more columns can be given to specify an ordering. This tells the WINDOW_AVG function what the preceding/following element actually is. Optionally every column can be tagged as ascending or descending. The partition columns specify groups. The WINDOW_AVG function operates independently within every group. This means when an ordering is given it is applied within every group.

NULL handling

NULL values are ignored, meaning that even if they are inside the window, they do not influence the result of the average. If all values of a window are NULL, the result for this window is also NULL.

Examples
Basic usage

[1]

WINDOW_AVG with INT values, taking the current and the row afterwards into consideration.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , 0 , 1 )

Input

Output

Table

values : int

1

2

3

5

7

8

8

9

Result

Column1 : float

1.5

2.5

4.0

6.0

7.5

8.0

8.5

9.0

[2]

WINDOW_AVG with INT values, taking the current and the previous row into consideration.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 0 )

Input

Output

Table

values : int

1

2

3

5

7

8

8

9

Result

Column1 : float

1.0

1.5

2.5

4.0

6.0

7.5

8.0

8.5

[3]

WINDOW_AVG with negative and zero INT values.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 1 )

Input

Output

Table

values : int

-3

0

6

-3

0

Result

Column1 : float

-1.5

1.0

1.0

1.0

-1.5

Advanced usage (e.g. showing NULL handling)

[4]

WINDOW_AVG with FLOAT values and NULL occurrences.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 1 )

Input

Output

Table

values : float

4.0

null

11.0

8.0

8.0

null

4.0

1.0

Result

Column1 : float

4.0

7.5

9.5

9.0

8.0

6.0

2.5

2.5

[5]

WINDOW_AVG with a leading NULL.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 2 , 2 )

Input

Output

Table

values : int

null

1

2

3

4

9

1

-3

Result

Column1 : float

1.5

2.0

2.5

3.8

3.8

2.8

2.75

2.33333333333

[6]

WINDOW_AVG with NULL occurrences.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 1 )

Input

Output

Table

values : int

6

5

null

null

null

5

2

Result

Column1 : float

5.5

5.5

5.0

null

5.0

3.5

3.5

Ordering, Partitioning

[7]

WINDOW_AVG with an ORDER BY clause.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 1 , ORDER BY ( "Table"."order" DESC ) )

Input

Output

Table

values : int

order : int

6

3

5

2

8

5

10

1

1

4

Result

Column1 : float

4.0

7.0

4.5

7.5

5.0

[8]

WINDOW_AVG with a PARTITION BY clause.

Query

Column1 

         WINDOW_AVG ( "Table"."values" , - 1 , 1 , PARTITION BY ( "Table"."fruit" ) )

Input

Output

Table

values : int

fruit : string

6

'Apple'

5

'Apple'

8

'Strawberry'

10

'Orange'

1

'Strawberry'

Result

Column1 : float

5.5

5.5

4.5

10.0

4.5

[9]

WINDOW_AVG with multiple orders and partition clauses.

Query

Column1 

         WINDOW_AVG ( "Table1"."value" , - 1 , 1 , ORDER BY ( "Table1"."year" ASC , "Table1"."column" DESC ) , PARTITION BY ( "Table1"."Country" , "Table1"."State" ) )

Input

Output

Table1

column : int

year : date

value : float

Country : string

State : string

4

Tue Dec 31 2002 00:00:00.000

null

'Germany'

'Bavaria'

2

Fri Dec 31 1999 00:00:00.000

2.0

'Germany'

'Berlin'

2

Mon Dec 31 2001 00:00:00.000

3.0

'Germany'

'Bavaria'

1

Fri Dec 31 1999 00:00:00.000

null

'Germany'

'Bavaria'

1

Fri Dec 31 1999 00:00:00.000

5.0

'USA'

'California'

1

Sun Dec 31 2000 00:00:00.000

6.0

'Germany'

'Berlin'

5

Tue Dec 31 2002 00:00:00.000

null

'USA'

'California'

6

Mon Dec 31 2001 00:00:00.000

null

'Germany'

'Berlin'

1

Tue Dec 31 2002 00:00:00.000

9.0

'Germany'

'Bavaria'

7

Wed Dec 31 2003 00:00:00.000

10.0

'USA'

'California'

Result

Column1 : float

6.0

4.0

3.0

3.0

5.0

4.0

7.5

6.0

9.0

10.0