LAG

Returns a value from a previous row within the partition of a result set.

See also: LEAD.

Syntax

LAG ( expr [, offset ])
   OVER (
         [ PARTITION BY partition_expr [, ...] ]
         [ ORDER BY order [ ASC | DESC ] [, ...]]
      )

offset ::= integer literal

Arguments

Parameter

Description

expr

A TINYINT, SMALLINT, INT, BIGINT, REAL, DOUBLE, BOOL, DATE, DATETIME or TEXT expression. Arrays and DATETIME2 are not supported.

offset

Specifies the offset of rows to scan back. If not specified, defaults to 1.

Returns

Same type as expr.

Notes

  • offset is evaluated with respect to the current row. If not specified, offset defaults to 1.

  • If there is no previous row (calculated against the current row), LAG returns NULL.

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   "Name" text(40),
   "Team" text(40),
   "Number" tinyint,
   "Position" text(2),
   "Age" tinyint,
   "Height" text(4),
   "Weight" real,
   "College" text(40),
   "Salary" float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv

Name

Team

Number

Position

Age

Height

Weight

College

Salary

Avery Bradley

Boston Celtics

0.0

PG

25.0

6-2

180.0

Texas

7730337.0

Jae Crowder

Boston Celtics

99.0

SF

25.0

6-6

235.0

Marquette

6796117.0

John Holland

Boston Celtics

30.0

SG

27.0

6-5

205.0

Boston University

R.J. Hunter

Boston Celtics

28.0

SG

22.0

6-5

185.0

Georgia State

1148640.0

Jonas Jerebko

Boston Celtics

8.0

PF

29.0

6-10

231.0

5000000.0

Amir Johnson

Boston Celtics

90.0

PF

29.0

6-9

240.0

12000000.0

Jordan Mickey

Boston Celtics

55.0

PF

21.0

6-8

235.0

LSU

1170960.0

Kelly Olynyk

Boston Celtics

41.0

C

25.0

7-0

238.0

Gonzaga

2165160.0

Terry Rozier

Boston Celtics

12.0

PG

22.0

6-2

190.0

Louisville

1824360.0

Using LAG to access previous rows

Changed in version 2020.2: LAG and LEAD are supported from v2020.2.

This example calculates the salary between two players, starting from the highest salary.

t=> SELECT "Name",
.          "Salary",
.          LAG("Salary") OVER (ORDER BY "Salary" DESC) AS "Salary - previous",
.          LAG("Salary",1) OVER (ORDER BY "Salary" DESC) - "Salary" AS "Salary - diff"
.          -- LAG("Salary",1) is equivalent to LAG("Salary")
.   FROM   nba
.   LIMIT 11 ;
Name            | Salary   | Salary - previous | Salary - diff
----------------+----------+-------------------+--------------
Kobe Bryant     | 25000000 |                   |
LeBron James    | 22970500 |          25000000 |       2029500
Carmelo Anthony | 22875000 |          22970500 |         95500
Dwight Howard   | 22359364 |          22875000 |        515636
Chris Bosh      | 22192730 |          22359364 |        166634
Chris Paul      | 21468695 |          22192730 |        724035
Kevin Durant    | 20158622 |          21468695 |       1310073
Derrick Rose    | 20093064 |          20158622 |         65558
Dwyane Wade     | 20000000 |          20093064 |         93064
Brook Lopez     | 19689000 |          20000000 |        311000
DeAndre Jordan  | 19689000 |          19689000 |             0