Offset Window Functions

Offset/Navigation Functions

Offset or navigation functions are analytic window functions that select a value from a frame row.

1. FIRST_VALUE and LAST_VALUE are absolute offset-based functions. They look at the first or the last row in the frame.
2. LAG and LEAD are relative offset-based functions. They look a few rows back or forth from the current row.
3. NTH_VALUE locates a relative row looking either a few rows forward from the first row (FROM FIRST, by default), or a few rows backwards from the last row (FROM LAST). The negative offset value from the image below actually translates into a FROM LAST keyword.

Offset Function Configuration Popup

In Data Xtractor and Query Xtractor we define and configure any SQL offset window function through a highly specialized popup. Go to the Formula cell with your function call and click on the contextual menu command Edit Function Arguments anytime.

Previous image translates into a LAG(4, ‘nothing found’) call, as the popup title suggests. Particular areas of this popup:

1. Left area with particular row types on the frame. Visually locating what you want to select is better than blindly calling LEAD, LAG etc. Click on a particular area or drag the selection rectangle over that place. Or click on the related radio button on the right.
2. Title with the actual function call. This is the same as the text that will appear in builder’s cell after you close the popup.
3. Description of your particular frame selection, in the bottom-left corner. This tells you more about your selection and the actual SQL function it will call.
4. Offset for the number of rows, as a positive integer, when you select something for LEAD, LAG or NTH_VALUE. The entry field appears only for these particular selections.
5. Default Value for LEAD/LAG, when no row is found. This entry field, if the feature is supported, appears right below the Offset field.
6. Ignore NULLs checkbox, enabled only when supported by your database.

The popup allows you to configure the current function call. But you can also switch to another related offset function, and change completely the initial signature.

Generated SQL

For a database supporting window functions, locate the Offset Functions generic demo query under the Queries > Demo Queries > Basic > Window Functions folder. Open it and switch to design mode. Click on the image below to enlarge:

Switch to SQL mode, click on Hide SQL for Subqueries, and look at the generated code, for MySQL here:

SELECT name, id,
   LAG(id) OVER (ORDER BY id) AS `lag`,
   LEAD(id) OVER (ORDER BY id) AS lead_1,
   LAG(id, 3) OVER (ORDER BY id) AS lag_3,
   LEAD(id, 3) OVER (ORDER BY id) AS lead_3,
   FIRST_VALUE(id) OVER (ORDER BY id) AS `first_value`,
   LAST_VALUE(id) OVER (ORDER BY id
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `last_value`
FROM FruitsU