Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

LEAD, LAG ​

Return values for a row at a given position before or after the current row in the partition (before the current row=LAG, after the current row=LEAD). The position is defined as an offset value.

{ LEAD | LAG } (expression [, offset [, default] ])
OVER { window_name | ( [ window_definition ] ) }

Parameters ​

offset

The offset is an optional integer value or numeric expression. The default offset is 1.

default

If offset is specified, a default value may also be specified, and this value must have the same data type as the first argument to the function.

This default provides a value when the function does not return a LEAD or LAG value for the specified offset (because no such row exists with respect to the current row). If default is not specified, the function returns null.

window_definition

For the full window_definition syntax, see Syntax for Window Functions. The ORDER BY clause is not required for this function. The frame clause, if specified, is ignored.

Examples ​

For example, use the LEAD function with an offset of 1 row:

premdb=# select name, city, capacity, 
lead(capacity,1) over(partition by city order by name) as lead_cap 
from team where city='London' order by 2,1;
       name         |  city  | capacity | lead_cap 
---------------------+--------+----------+----------
 Arsenal             | London |    60260 |    27111
 Charlton Athletic   | London |    27111 |    41663
 Chelsea             | London |    41663 |    26255
 Crystal Palace      | London |    26255 |    25700
 Fulham              | London |    25700 |    18439
 Queens Park Rangers | London |    18439 |    36284
 Tottenham Hotspur   | London |    36284 |    35016
 West Ham United     | London |    35016 |    26255
 Wimbledon           | London |    26255 |         
(9 rows)

Here is the same query with the LAG function instead of LEAD:

premdb=# select name, city, capacity, 
lag(capacity,1) over(partition by city order by name) as lag_cap 
from team where city='London' order by 2,1;
       name         |  city  | capacity | lag_cap 
---------------------+--------+----------+---------
 Arsenal             | London |    60260 |        
 Charlton Athletic   | London |    27111 |   60260
 Chelsea             | London |    41663 |   27111
 Crystal Palace      | London |    26255 |   41663
 Fulham              | London |    25700 |   26255
 Queens Park Rangers | London |    18439 |   25700
 Tottenham Hotspur   | London |    36284 |   18439
 West Ham United     | London |    35016 |   36284
 Wimbledon           | London |    26255 |   35016
(9 rows)

In the following example, there is a lag of 5 rows. A default lag value of 0 is supplied for rows that would otherwise return null:

premdb=# select name, city, capacity, 
lag(capacity,5,0) over(partition by city order by name) as lag_cap 
from team where city='London' order by 2,1; 
       name         |  city  | capacity | lag_cap 
---------------------+--------+----------+---------
 Arsenal             | London |    60260 |       0
 Charlton Athletic   | London |    27111 |       0
 Chelsea             | London |    41663 |       0
 Crystal Palace      | London |    26255 |       0
 Fulham              | London |    25700 |       0
 Queens Park Rangers | London |    18439 |   60260
 Tottenham Hotspur   | London |    36284 |   27111
 West Ham United     | London |    35016 |   41663
 Wimbledon           | London |    26255 |   26255
(9 rows)

Parent topic:Window Functions