Skip to main content

Celonis Product Documentation

RUNNING_SUM
Description

RUNNING_SUM returns the Sum of the previous rows. It is possible to specify a column based ordering and partitioning. Null values are skipped.

Syntax
  RUNNING_SUM ( column [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] )
 
  • column: The source column where preceding rows are taken from.

  • 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 RUNNING_SUM should operate.

Ordering

One or more columns can be given to specify an ordering. This tells the RUNNING_SUM function what the preceding element actually is. Optionally every column can be tagged as ascending or descending.

Partitioning

The partition columns specify groups. The RUNNING_SUM function operates independently within every group. This means when an ordering is given it is applied within every group.

Null handling

The RUNNING_SUM for a NULL value is the same value as the RUNNING_SUM value for the last non-NULL value.

Examples

[1]

Simple example for Running Sum with Integer.

Query

Column1

         RUNNING_SUM ( "Table1"."INCOME" )
        

Input

Output

Table1

INCOME : int

100

200

300

400

500

Result

Column1 : int

100

300

600

1000

1500

[2]

Simple example for Running Sum with Floats.

Query

Column1

         RUNNING_SUM ( "Table1"."INCOME" )
        

Input

Output

Table1

INCOME : float

1.0

20.5

8.3

0.5

4.7

Result

Column1 : float

1.0

21.5

29.8

30.3

35.0

[3]

RUNNING SUM with a single order column.

Query

Column1

         RUNNING_SUM ( "Table1"."column" , ORDER BY ( "Table1"."order" ) )
        

Input

Output

Table1

column : int

order : int

100

3

200

2

300

5

400

1

500

4

Result

Column1 : int

700

600

1500

400

1200

[4]

RUNNING_SUM with a single partition column.

Query

Column1

         RUNNING_SUM ( "Table1"."column" , PARTITION BY ( "Table1"."Country" ) )
        

Input

Output

Table1

column : int

Country : string

1

'Germany'

2

'Germany'

3

'Germany'

1

'USA'

2

'USA'

Result

Column1 : int

1

3

6

1

3

[5]

Simple example for Running Sum with null.

Query

Column1

         RUNNING_SUM ( "Table1"."INCOME" )
        

Input

Output

Table1

MONTH : int

INCOME : int

1

null

1

null

2

300

3

null

4

500

Result

Column1 : int

null

null

300

300

800

See also: