LAG() / LEAD()

Access value from previous/next row(s)

Window functions are only available in Analytics queries

Code

Groovy

def qapi = api.queryApi()
def exprs = qapi.exprs()
def orders = qapi.orders()

def t = qapi.tables().dataSource("PriceHistory")

def wnd = qapi.exprs().window()
        .allRows()
        .partitionBy([t.sku])
        .sortBy([orders.ascNullsLast(t.ValidFrom)])

qapi.source(t, [t.sku, t.ValidFrom, t.Price,
            wnd.lag(t.Price).as("Lag"),
            wnd.lag(t.Price).minus(t.Price).as("Diff")
        ]
    )
        .stream { it.toList() }

Result

See Also