content_image/804812.jpg
+----------+------+------+------+----------+-----------+ 

In the example, the LAG()  and LEAD()  calls use the default N  and default  values of 1 and NULL , respectively. 

The first row shows what happens when there is no previous row for LAG() : The function returns the default  value (in this case, NULL ). The last row shows the same thing when there is no next row for LEAD() . 

LAG()  and LEAD()  also serve to compute sums rather than differences. Consider this data set, which contains the first few numbers of the Fibonacci series: 

mysql> SELECT n FROM fib ORDER BY n; +------+ | n | +------+ | 1 | | 1 | | 2 | | 3 | | 5 | | 8 | +------+ 

The following query shows the LAG()  and LEAD()  values for the rows adjacent to the current row. It also uses those functions to add to the current row value the values from the preceding and following rows. The effect is to generate the next number in the Fibonacci series, and the next number after that: 

mysql> SELECT n, LAG(n, 1, 0) OVER w AS ’lag’, LEAD(n, 1, 0) OVER w AS ’lead’, n + LAG(n, 1, 0) OVER w AS ’next_n’, n + LEAD(n, 1, 0) OVER w AS ’next_next_n’ FROM fib WINDOW w AS (ORDER BY n); 

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

One way to generate the initial set of Fibonacci numbers is to use a recursive common table expression. For an example, see Fibonacci Series Generation. 

Beginning with MySQL 8.0.22, you cannot use a negative value for the rows argument of this function. 

• LAST_VALUE( expr )  [ null_treatment ] over_clause 

Returns the value of expr  from the last row of the window frame. 

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

For an example, see the FIRST_VALUE()  function description. 

