Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

LEAD

Applies to: CELONIS 4.7

Description

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

Syntax
LEAD ( table.column [, ORDER BY ( order_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] [, offset ] )

  • ASC: Ascending order

  • DESC: Descending order

  • column: The source column where following rows are taken from.

  • order_column: Optional order column to specify an sorting.

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

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

  • offset: The number of non-NULL rows following the current row. The default value is 1.

Ordering

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

[1] LEAD with a single order column.

Query

Column1

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

Input

Output

Table1

column : STRING

order : INT

'A'

3

'B'

2

'C'

5

'D'

1

'E'

4

Result

Column1 : STRING

'E'

'A'

null

'B'

'C'

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

Query

Column1

LEAD ( "Table1"."column" , ORDER BY ( "Table1"."order" DESC ) )

Input

Output

Table1

column : STRING

order : INT

'A'

3

'B'

2

'C'

5

'D'

1

'E'

4

Result

Column1 : STRING

'B'

'D'

'E'

null

'A'

[3] LEAD with multiple order columns.

Query

Column1

LEAD ( "Table1"."column" , ORDER BY ( "Table1"."order1" , "Table1"."order2" ) )

Input

Output

Table1

column : STRING

order1 : INT

order2 : STRING

'A'

3

'z'

'B'

1

'z'

'C'

1

'y'

'D'

2

'y'

'E'

2

'x'

Result

Column1 : STRING

null

'E'

'B'

'A'

'D'

Partitioning

The partition columns specify groups. The LEAD 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 leading value of null.

[4] LEAD with a single partition column.

Query

Column1

LEAD ( "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

2

3

null

2

null

[5] LEAD with multiple partition columns.

Query

Column1

LEAD ( "Table1"."column" , PARTITION BY ( "Table1"."Country" , "Table1"."State" ) )

Input

Output

Table1

column : INT

Country : STRING

State : STRING

1

'Germany'

'Bavaria'

2

'Germany'

'Berlin'

2

'Germany'

'Bavaria'

3

'Germany'

'Bavaria'

1

'USA'

'California'

Result

Column1 : INT

2

null

3

null

null

[6] LEAD with multiple partition columns.

Query

Column1

LEAD (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" )
)

Input

Output

Table1

column : INT

year : DATE

Country : STRING

State : 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'

Result

Column1 : INT

null

null

1

2

null

NULL handling

The leading value for a NULL value is the same value as the leading value of the last non-NULL value. The offset parameter counts only non-NULL values.

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

Query

Column1

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

Input

Output

Table1

column : INT

Country : STRING

1

'Germany'

null

'Germany'

3

'Germany'

4

'Germany'

1

'USA'

2

'USA'

null

'USA'

Result

Column1 : INT

3

3

4

null

2

null

null

[8] Offset ignores NULL values.

Query

[8] Offset ignores NULL values.

Query

Column1

LEAD ( "Table1"."column" , PARTITION BY ( "Table1"."Country" ) , 2 )

Input

Output

Table1

column : INT

Country : STRING

1

'Germany'

null

'Germany'

3

'Germany'

4

'Germany'

1

'USA'

2

'USA'

null

'USA'

null

'USA'

5

'USA'

Result

Column1 : INT

4

4

null

null

5

null

null

null

null

Examples

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

Query

Column1

LEAD ( "Table1"."column" )

Input

Output

Table1

column : STRING

'C'

'D'

'A'

'E'

'B'

Result

Column1 : STRING

'D'

'A'

'E'

'B'

null

[10] LEAD with an offset.

Query

Column1

LEAD ( "Table1"."column" , 3 )

Input

Output

Table1

column : STRING

'C'

'D'

'A'

'E'

'B'

Result

Column1 : STRING

'E'

'B'

null

null

null

[11] LEAD can be used to simulate ACTIVITY_LEAD.

Query

Column1

LEAD ( "Table1"."activity" , ORDER BY ( "Table1"."timestamp" ) , PARTITION BY ( "Table1"."case" ) )

Input

Output

Table1

case : INT

activity : STRING

timestamp : 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

Result

Column1 : STRING

'B'

'C'

null

'B'

'C'

'D'

null

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

Query

Column1

LEAD (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" DESC , "Table1"."value" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" )
)

Input

Output

Table1

column : INT

year : DATE

value : FLOAT

Country : STRING

State : 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

Column1 : INT

4

null

3

null

null

2

1

1

2

5

[13] All optional parameters can be combined.

Query

Column1

LEAD (
    "Table1"."column" ,
    ORDER BY ( "Table1"."year" DESC , "Table1"."value" ) ,
    PARTITION BY ( "Table1"."Country" , "Table1"."State" ) ,
    2
)

Input

Output

Table1

column : INT

year : DATE

value : FLOAT

Country : STRING

State : 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'

Result

Column1 : INT

2

null

null

null

null

null

null

2

3

1

See also: