Introduction

New functions have been introduced in HANA for some years ago, but haven’t received a lot of attention. The two functions we will cover here are LAG() and LEAD().

The functions provides access from the current row to the previous and next row in the dataset.

Function Details

Using the LAG() function it is possible to access fields from a previous row from the SELECT statement of the current row. The function must be used in conjunction with OVER() which provides a «window» the table content defined by the «PARTITION BY» clause.

The same are valid for the LEAD() function, which provides access to the next row.

Example

In this example we calculate the number of free seats on the previous and next flight out of Frankfurt.

SELECT 
  sflight~carrid,
  sflight~connid,
  sflight~fldate,
  sflight~seatsmax,
  sflight~seatsocc,
  ( sflight~seatsmax - sflight~seatsocc ) AS seatsfree,
  ( LAG( sflight~seatsmax )
      OVER( PARTITION BY sflight~carrid ORDER BY fldate )
  - LAG( sflight~seatsocc )
      OVER( PARTITION BY sflight~carrid ORDER BY fldate ) ) AS seatsfree_previous,
  ( LEAD( sflight~seatsmax )
      OVER( PARTITION BY sflight~carrid ORDER BY fldate )
  - LEAD( sflight~seatsocc )
      OVER( PARTITION BY sflight~carrid ORDER BY fldate ) ) AS seatsfree_next
  FROM spfli
  INNER JOIN sflight ON spfli~carrid = sflight~carrid AND spfli~connid = sflight~connid
  WHERE spfli~cityfrom = 'FRANKFURT'
  ORDER BY sflight~carrid, sflight~fldate
  INTO TABLE @DATA(lt_sflights).

This gives the following result in the internal table lt_sflights:

Conclusion

The real value will appear when we select just one row from the database, and are provided with results from three rows. We could select a flight and get information of the previous and next flight with just one access to the database.

References

LAG function: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/e7ef7cc478f14a408e1af27fc1a78624.html

LEAD function: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/5932eebb6208406590071eb65c6caa83.html

Original Article:
https://blogs.sap.com/2020/05/04/sql-windowing-functions/