Applies to:  CELONIS 4.7 

Description

LAG returns the row that precedes the current row by offset number of rows. It is possible to specify a column based ordering and partitioning. Null values are skipped.

Syntax

LAG ( column [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] [, offset ] )
  • 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.
  • offset: The number of non-NULL rows preceding the current row. The default value is 1.

Ordering

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


[1] LAG with a single order column.

Query
Column1
LAG ( "Table1"."column" , ORDER BY ( "Table1"."order" ) )
Input
Table1
column : STRINGorder : INT
'A'
3
'B'
2
'C'
5
'D'
1
'E'
4
Output
Result
Column1 : STRING
'B'
'D'
'E'
null
'A'



[2] LAG with a single order column tagged as descending.

Query
Column1
LAG ( "Table1"."column" , ORDER BY ( "Table1"."order" DESC ) )
Input
Table1
column : STRINGorder : INT
'A'
3
'B'
2
'C'
5
'D'
1
'E'
4
Output
Result
Column1 : STRING
'E'
'A'
null
'B'
'C'



[3] LAG with multiple order columns.

Query
Column1
LAG ( "Table1"."column" , ORDER BY ( "Table1"."order1" , "Table1"."order2" ) )
Input
Table1
column : STRINGorder1 : INTorder2 : STRING
'A'
3
'z'
'B'
1
'z'
'C'
1
'y'
'D'
2
'y'
'E'
2
'x'
Output
Result
Column1 : STRING
'D'
'C'
null
'E'
'B'


Partitioning

The partition columns specify groups. The LAG function operates independently within every group. This means when an ordering is given it is applied within every group and the last offset elements in every group have a lagging value of null.


[4] LAG with a single partition column.

Query
Column1
LAG ( "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
null
1
2
null
1



[5] LAG with multiple partition columns.

Query
Column1
LAG ( "Table1"."column" , PARTITION BY ( "Table1"."Country" , "Table1"."State" ) )
Input
Table1
column : INTCountry : STRINGState : STRING
1
'Germany''Bavaria'
2
'Germany''Berlin'
2
'Germany''Bavaria'
3
'Germany''Bavaria'
1
'USA''California'
Output
Result
Column1 : INT
null
null
1
2
null



[6] LAG with multiple partition columns.

Query
Column1
LAG (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" )
)
Input
Table1
column : INTyear : DATECountry : STRINGState : STRING
1
Tue Dec 31 2002 00:00:00.000
'Germany''Bavaria'
2
Fri Dec 31 1999 00:00:00.000
'Germany''Berlin'
2
Mon Dec 31 2001 00:00:00.000
'Germany''Bavaria'
3
Fri Dec 31 1999 00:00:00.000
'Germany''Bavaria'
1
Fri Dec 31 1999 00:00:00.000
'USA''California'
Output
Result
Column1 : INT
2
null
3
null
null


NULL handling

The lagging value for a NULL value is the same value as the lagging value for the next non-NULL value. The offset parameter counts only non-NULL values.


[7] Example for LAG applied to a column including NULL values.

Query
Column1
LAG ( "Table1"."column" , PARTITION BY ( "Table1"."Country" ) )
Input
Table1
column : INTCountry : STRING
1
'Germany'
null
'Germany'
3
'Germany'
4
'Germany'
null
'USA'
1
'USA'
2
'USA'
Output
Result
Column1 : INT
null
1
1
3
null
null
1



[8] Offset ignores NULL values.

Query
Column1
LAG ( "Table1"."column" , PARTITION BY ( "Table1"."Country" ) , 2 )
Input
Table1
column : INTCountry : STRING
1
'Germany'
null
'Germany'
3
'Germany'
4
'Germany'
1
'USA'
null
'USA'
null
'USA'
4
'USA'
5
'USA'
Output
Result
Column1 : INT
null
null
null
1
null
null
null
null
1


Examples


[9] Simple example of LAG returning the preceding row. If a row does not have a preceding row NULL is returned.

Query
Column1
LAG ( "Table1"."column" )
Input
Table1
column : STRING
'C'
'D'
'A'
'E'
'B'
Output
Result
Column1 : STRING
null
'C'
'D'
'A'
'E'



[10] LAG with an offset.

Query
Column1
LAG ( "Table1"."column" , 3 )
Input
Table1
column : STRING
'C'
'D'
'A'
'E'
'B'
Output
Result
Column1 : STRING
null
null
null
'C'
'D'



[11] LAG can be used to simulate ACTIVITY_LAG.

Query
Column1
LAG ( "Table1"."activity" , ORDER BY ( "Table1"."timestamp" ) , PARTITION BY ( "Table1"."case" ) )
Input
Table1
case : INTactivity : STRINGtimestamp : DATE
1
'A'
Mon Feb 01 2016 01:00:00.000
1
'B'
Mon Feb 01 2016 02:00:00.000
1
'C'
Mon Feb 01 2016 03:00:00.000
2
'A'
Mon Feb 01 2016 01:00:00.000
2
'B'
Mon Feb 01 2016 02:00:00.000
2
'C'
Mon Feb 01 2016 03:00:00.000
2
'D'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
null
'A'
'B'
null
'A'
'B'
'C'



[12] It is possible to combine an arbitrary amount of order columns with an arbitrary amount of partition columns.

Query
Column1
LAG (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" DESC , "Table1"."value" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" )
)
Input
Table1
column : INTyear : DATEvalue : FLOATCountry : STRINGState : STRING
1
Tue Dec 31 2002 00:00:00.000
1.0
'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'
3
Fri Dec 31 1999 00:00:00.000
4.0
'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
7.0
'USA''California'
6
Mon Dec 31 2001 00:00:00.000
8.0
'Germany''Berlin'
4
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'
Output
Result
Column1 : INT
null
1
4
2
5
6
7
null
1
null



[13] All optional parameters can be combined.

Query
Column1
LAG (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" DESC , "Table1"."value" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" ) ,
    2
)
Input
Table1
column : INTyear : DATEvalue : FLOATCountry : STRINGState : STRING
1
Tue Dec 31 2002 00:00:00.000
1.0
'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'
3
Fri Dec 31 1999 00:00:00.000
4.0
'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
7.0
'USA''California'
6
Mon Dec 31 2001 00:00:00.000
8.0
'Germany''Berlin'
4
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'
Output
Result
Column1 : INT
null
6
1
4
7
null
null
null
null
null


See also:

  • No labels