LAG [Analytic]

Returns the value of the input expression at the given offset before the current row within a window. This function lets you access more than one row in a table at the same time. This is useful for comparing values when the relative positions of rows can be reliably known. It also lets you avoid the more costly self join, which enhances query processing speed.

For information on getting the rows that follow, see LEAD.

Behavior Type

Immutable

Syntax

LAG ( expression[, offset ] [, default ] ) OVER ( 
    [ window-partition-clause ] 
    window-order-clause )

Parameters

expression

The expression to evaluate—for example, a constant, column, non-analytic function, function expression, or expressions involving any of these.

offset

Indicates how great is the lag. The default value is 1 (the previous row). This parameter must evaluate to a constant positive integer.

default

The value returned if offset falls outside the bounds of the table or partition. This value must be a constant value or an expression that can be evaluated to a constant; its data type is coercible to that of the first argument.

Examples

This example sums the current balance by date in a table and also sums the previous balance from the last day. Given the inputs that follow, the data satisfies the following conditions:

  • For each some_id, there is exactly 1 row for each date represented by month_date.
  • For each some_id, the set of dates is consecutive; that is, if there is a row for February 24 and a row for February 26, there would also be a row for February 25.
  • Each some_id has the same set of dates.

    => CREATE TABLE balances (
           month_date DATE, 
           current_bal INT, 
           some_id INT);
    
    => INSERT INTO balances values ('2009-02-24', 10, 1); 
    => INSERT INTO balances values ('2009-02-25', 10, 1); 
    => INSERT INTO balances values ('2009-02-26', 10, 1); 
    => INSERT INTO balances values ('2009-02-24', 20, 2); 
    => INSERT INTO balances values ('2009-02-25', 20, 2); 
    => INSERT INTO balances values ('2009-02-26', 20, 2); 
    => INSERT INTO balances values ('2009-02-24', 30, 3); 
    => INSERT INTO balances values ('2009-02-25', 20, 3); 
    => INSERT INTO balances values ('2009-02-26', 30, 3);
    

Now run LAG to sum the current balance for each date and sum the previous balance from the last day:

=> SELECT month_date,
     SUM(current_bal) as current_bal_sum,
     SUM(previous_bal) as previous_bal_sum FROM 
       (SELECT month_date, current_bal, 
     LAG(current_bal, 1, 0) OVER 
       (PARTITION BY some_id ORDER BY month_date) 
     AS previous_bal FROM balances) AS subQ 
     GROUP BY month_date ORDER BY month_date;
month_date  | current_bal_sum | previous_bal_sum 
------------+-----------------+------------------
 2009-02-24 |              60 |                0
 2009-02-25 |              50 |               60
 2009-02-26 |              60 |               50
(3 rows)

Using the same example data, the following query would not be allowed because LAG is nested inside an aggregate function:

=> SELECT month_date,
    SUM(current_bal) as current_bal_sum, 
   SUM(LAG(current_bal, 1, 0) OVER 
      (PARTITION BY some_id ORDER BY month_date)) AS previous_bal_sum
   FROM some_table GROUP BY month_date ORDER BY month_date; 

The following example uses the VMart database. LAG first returns the annual income from the previous row, and then it calculates the difference between the income in the current row from the income in the previous row:

=> SELECT occupation, customer_key, customer_name, annual_income,
   LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
   ORDER BY annual_income) AS prev_income, annual_income - 
   LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
   ORDER BY annual_income) AS difference
   FROM customer_dimension ORDER BY occupation, customer_key LIMIT 20;
 occupation | customer_key |    customer_name     | annual_income | prev_income | difference 
------------+--------------+----------------------+---------------+-------------+------------
 Accountant |           15 | Midori V. Peterson   |        692610 |      692535 |         75
 Accountant |           43 | Midori S. Rodriguez  |        282359 |      280976 |       1383
 Accountant |           93 | Robert P. Campbell   |        471722 |      471355 |        367
 Accountant |          102 | Sam T. McNulty       |        901636 |      901561 |         75
 Accountant |          134 | Martha B. Overstreet |        705146 |      704335 |        811
 Accountant |          165 | James C. Kramer      |        376841 |      376474 |        367
 Accountant |          225 | Ben W. Farmer        |         70574 |       70449 |        125
 Accountant |          270 | Jessica S. Lang      |        684204 |      682274 |       1930
 Accountant |          273 | Mark X. Lampert      |        723294 |      722737 |        557
 Accountant |          295 | Sharon K. Gauthier   |         29033 |       28412 |        621
 Accountant |          338 | Anna S. Jackson      |        816858 |      815557 |       1301
 Accountant |          377 | William I. Jones     |        915149 |      914872 |        277
 Accountant |          438 | Joanna A. McCabe     |        147396 |      144482 |       2914
 Accountant |          452 | Kim P. Brown         |        126023 |      124797 |       1226
 Accountant |          467 | Meghan K. Carcetti   |        810528 |      810284 |        244
 Accountant |          478 | Tanya E. Greenwood   |        639649 |      639029 |        620
 Accountant |          511 | Midori P. Vogel      |        187246 |      185539 |       1707
 Accountant |          525 | Alexander K. Moore   |        677433 |      677050 |        383
 Accountant |          550 | Sam P. Reyes         |        735691 |      735355 |        336
 Accountant |          577 | Robert U. Vu         |        616101 |      615439 |        662
(20 rows)

The next example uses LEAD and LAG to return the third row after the salary in the current row and fifth salary before the salary in the current row:

=> SELECT hire_date, employee_key, employee_last_name,
   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "next_hired" ,
   LAG(hire_date, 1) OVER (ORDER BY hire_date) AS "last_hired"
   FROM employee_dimension ORDER BY hire_date, employee_key;
 hire_date  | employee_key | employee_last_name | next_hired | last_hired  
------------+--------------+--------------------+------------+------------
 1956-04-11 |         2694 | Farmer             | 1956-05-12 | 
 1956-05-12 |         5486 | Winkler            | 1956-09-18 | 1956-04-11
 1956-09-18 |         5525 | McCabe             | 1957-01-15 | 1956-05-12
 1957-01-15 |          560 | Greenwood          | 1957-02-06 | 1956-09-18
 1957-02-06 |         9781 | Bauer              | 1957-05-25 | 1957-01-15
 1957-05-25 |         9506 | Webber             | 1957-07-04 | 1957-02-06
 1957-07-04 |         6723 | Kramer             | 1957-07-07 | 1957-05-25
 1957-07-07 |         5827 | Garnett            | 1957-11-11 | 1957-07-04
 1957-11-11 |          373 | Reyes              | 1957-11-21 | 1957-07-07
 1957-11-21 |         3874 | Martin             | 1958-02-06 | 1957-11-11
(10 rows)