Skip to main content

Celonis Product Documentation

MOVING_COUNT_DISTINCT
Description

This function counts the distinct values for each window. It can be applied to any data type.

Syntax
 MOVING_COUNT_DISTINCT ( table.column, start, end )
NULL handling

NULL values are not counted. If all values of a group are NULL, the result for this group is 0.

Examples

[1]

Count of distinct values for current row and one row above.

Query

Column1 

         "Table1"."MONTH"

Column2 

         MOVING_COUNT_DISTINCT ( "Table1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : int

INCOME : int

1

100

1

100

2

400

3

300

4

300

Result

Column1 : int

Column2 : int

1

1

1

1

2

2

3

2

4

1

[2]

Count of distinct values for current row and one row above with null values.

Query

Column1 

         "Table1"."MONTH"

Column2 

         MOVING_COUNT_DISTINCT ( "Table1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : int

INCOME : int

1

null

1

null

2

400

3

300

4

300

Result

Column1 : int

Column2 : int

1

0

1

0

2

1

3

2

4

1

[3]

Count of distinct values for current row and one row above. A FILTER is applied, such that only rows with an INCOME value less than 400 are taken into account.

Query

Filter 

         FILTER "Table1"."INCOME" < 400;

Column1 

         "Table1"."MONTH"

Column2 

         MOVING_COUNT_DISTINCT ( "Table1"."INCOME" , - 1 , 0 )

Input

Output

Table1

MONTH : int

INCOME : int

1

null

1

100

2

400

3

300

4

300

Result

Column1 : int

Column2 : int

1

1

3

2

4

1

See also: