Lag: lag

Syntax

in a Data Set expression	lag ( dataset {, offset {, defaultValue } } over ( { partitionClause } orderClause ) )
in a Component expression within a calc clause	lag ( component {, offset {, defaultValue } } over ( { partitionClause } orderClause ) )

Input parameters

dataset	the operand Data Set
component	the operand Component
offset	the relative position prior to the current Data Point
defaultValue	the value returned when the offset goes outside of the partition
partitionClause	see Analytic invocation
orderClause	see Analytic invocation

Semantics for scalar operations

This operator cannot be applied to scalar values.

Input parameters type

dataset

dataset

component

component

offset

integer [ value > 0 ]

default value

scalar

Result type

result

dataset
| component

Additional Constraints

The Aggregate invocation is not allowed.

The windowClause of the Analytic invocation syntax is not allowed.

Behaviour

In the ordered set of Data Points of the current partition, the operator returns the value(s) taken from the Data Point at the specified physical offset prior to the current Data Point.

If defaultValue is not specified then the value returned when the offset goes outside the partition is NULL.

For other details, see Analytic invocation.

Examples

Given the operand dataset DS_1:

Input DS_1 (see structure)

Id_1	Id_2	Id_3	Me_1	Me_2
A	XX	1993	3	1
A	XX	1994	4	9
A	XX	1995	7	5
A	XX	1996	6	8
A	YY	1993	9	3
A	YY	1994	5	4
A	YY	1995	10	2
A	YY	1996	2	7

Example 1

DS_r := lag ( DS_1 , 1 over ( partition by Id_1 , Id_2 order by Id_3 ) );

results in (see structure):

DS_r
Id_1	Id_2	Id_3	Me_1	Me_2
A	XX	1993
A	XX	1994	3	1
A	XX	1995	4	9
A	XX	1996	7	5
A	YY	1993
A	YY	1994	9	3
A	YY	1995	5	4
A	YY	1996	10	2