SQL Windowing functions
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.
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.
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:
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.