Applies to:  CELONIS 4.7 

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 LAG 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
Table1
INCOME : INT
100
200
300
400
500
Output
Result
Column1 : INT
100
300
600
1000
1500



[2] Simple example for Running Sum with Floats.

Query
Column1
RUNNING_SUM ( "Table1"."INCOME" )
Input
Table1
INCOME : FLOAT
1.0
20.5
8.3
0.5
4.7
Output
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
Table1
column : INTorder : INT
100
3
200
2
300
5
400
1
500
4
Output
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
Table1
column : INTCountry : STRING
1
'Germany'
2
'Germany'
3
'Germany'
1
'USA'
2
'USA'
Output
Result
Column1 : INT
1
3
6
1
3



[5] Simple example for Running Sum with null.

Query
Column1
RUNNING_SUM ( "Table1"."INCOME" )
Input
Table1
MONTH : INTINCOME : INT
1
null
1
null
2
300
3
null
4
500
Output
Result
Column1 : INT
null
null
300
300
800


See also:

  • No labels