The inter-row functions, LAG and LEAD, provide access to previous or subsequent values in a data series, or to multiple rows in a table.
Inter-row functions also partition simultaneously without a self-join. LAG provides access to a row at a given physical offset prior to the CURRENT ROW in the table or partition. LEAD provides access to a row at a given physical offset after the CURRENT ROW in the table or partition.
LAG and LEAD syntax is identical. Both functions require an OVER (ORDER_BY) window specification. For example:
LAG (value_expr) [, offset [, default]]) OVER ([PARTITION BY window partition] ORDER BY window ordering)and:
LEAD (value_expr) [, offset [, default]]) OVER ([PARTITION BY window partition] ORDER BY window ordering)
The PARTITION BY clause in the OVER (ORDER_BY) clause is optional. The OVER (ORDER_BY) clause cannot contain a window frame ROWS/RANGE specification.
value_expr is a table column or expression that defines the offset data to return from the table. You can define other functions in the value_expr, with the exception of analytic functions.
For both functions, specify the target row by entering a physical offset. The offset value is the number of rows above or below the current row. Enter a nonnegative numeric data type (entering a negative value generates an error). If you enter 0, SAP Sybase IQ returns the current row.
The optional default value defines the value to return if the offset value goes beyond the scope of the table. The default value of default is NULL. The data type of default must be implicitly convertible to the data type of the value_expr value, or SAP Sybase IQ generates a conversion error.
LAG example 1—The inter-row functions are useful in financial services applications that perform calculations on data streams, such as stock transactions. This example uses the LAG function to calculate the percentage change in the trading price of a particular stock. Consider the following trading data from a fictional table called stock_trades:
traded at symbol price ------------------- ------ ------ 2009-07-13 06:07:12 SQL 15.84 2009-07-13 06:07:13 TST 5.75 2009-07-13 06:07:14 TST 5.80 2009-07-13 06:07:15 SQL 15.86 2009-07-13 06:07:16 TST 5.90 2009-07-13 06:07:17 SQL 15.86
The query partitions the trades by stock symbol, orders them by time of trade, and uses the LAG function to calculate the percentage increase or decrease in trade price between the current trade and the previous trade:
select stock_symbol as 'Stock', traded_at as 'Date/Time of Trade', trade_price as 'Price/Share', cast ( ( ( (trade_price - (lag(trade_price, 1) over (partition by stock_symbol order by traded_at))) / trade_price) * 100.0) as numeric(5, 2) ) as '% Price Change vs Previous Price' from stock_trades order by 1, 2
The query returns these results:
Stock Date/Time of Trade Price/ % Price Change_vs symbol Share Previous Price ------ ------------------- ----- ----------------- SQL 2009-07-13 06:07:12 15.84 NULL SQL 2009-07-13 06:07:15 15.86 0.13 SQL 2009-07-13 06:07:17 15.86 0.00 TST 2009-07-13 06:07:13 5.75 NULL TST 2009-07-13 06:07:14 5.80 0.87 TST 2009-07-13 06:07:16 5.90 1.72
The NULL result in the first and fourth output rows indicates that the LAG function is out of scope for the first row in each of the two partitions. Since there is no previous row to compare to, SAP Sybase IQ returns NULL as specified by the default variable.