content_image/718054.jpg
| 08:00:00 | xh458 | 25 | 0 | 25 | 10 | 30 | +----------+---------+------+-------+------+--------+--------+ 

Each function uses the rows in the current frame, which, per the window definition shown, extends from the first partition row to the current row. For the NTH_VALUE()  calls, the current frame does not always include the requested row; in such cases, the return value is NULL . 

• LAG( expr  [, N [, default ]])  [ null_treatment ] over_clause 

Returns the value of expr  from the row that lags (precedes) the current row by N  rows within its partition. If there is no such row, the return value is default . For example, if N  is 3, the return value is default for the first two rows. If N  or default  are missing, the defaults are 1 and NULL , respectively. 

N  must be a literal nonnegative integer. If N  is 0, expr  is evaluated for the current row. 

Beginning with MySQL 8.0.22, N  cannot be NULL . In addition, it must now be an integer in the range 1  to 2 $^{63}$, inclusive, in any of the following forms: 

•an unsigned integer constant literal 

•a positional parameter marker ( ? ) 

•a user-defined variable 

•a local variable in a stored routine 

over_clause  is as described in Section 12.21.2, “Window Function Concepts and Syntax” . null_treatment  is as described in the section introduction. 

LAG()  (and the similar LEAD()  function) are often used to compute differences between rows. The following query shows a set of time-ordered observations and, for each one, the LAG()  and LEAD() values from the adjoining rows, as well as the differences between the current and adjoining rows: 

<img src='content_image/718054.jpg'>

