​
Overview

The LAG() window function returns the values from specific rows based on the offset argument (previous to the current row in the partition). It can be used with all data types supported by Oxla

​
Syntax

The syntax for this function is as follows:

LAG (expression, offset, default) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The output’s data type for this function is the same as the input’s one. If there is no row that meets the offset criteria, it returns a default value (that must be of a type compatible with the expression value)

​
Parameters

  • expression: column, which will be referenced
  • offset: numeric indicator of the previous row to access, that is relative to the current row (optional, if not specified 1 will be returned)
  • default: value that wil be returned if the offset is out of range (optional, if not specified NULL will be returned)

​
Examples

For the needs of this section, we will create the winsales table that stores details about some sales transactions:

CREATE TABLE winsales(
    salesid int,
    dateid date,
    sellerid int,
    buyerid text,
    qty int,
    qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);

​
LAG(expression, offset)

In this example, we will focus on executing the LAG() function with expression and offset parameters’ values specified:

SELECT buyerid, dateid, qty
    LAG(qty,1) OVER (ORDER BY buyerid, dateid) AS prev_qty
FROM winsales WHERE buyerid = 'c'
ORDER BY buyerid, dateid;

When executing the query above, it returns the buyer ID, date ID, quantity and previous quantity for all rows with buyer ID equal to c:

 buyerid |   dateid   | qty | prev_qty 
---------+------------+-----+----------
 c       | 2003-12-24 |  10 |         
 c       | 2004-01-18 |  10 |       10
 c       | 2004-02-16 |  20 |       10
 c       | 2004-09-07 |  30 |       20
(4 rows)

​
LAG(expression, offset, default)

In this example, we will focus on executing the LAG() function with expression, offset and default parameters’ values specified:

SELECT buyerid, dateid, qty
    LAG(buyerid,1,'unknown') OVER (ORDER BY dateid) AS prev_buyerid
FROM winsales 
ORDER BY dateid;

The query above returns the buyer ID, date ID, quantity and previous buyer ID for all rows:

 buyerid |   dateid   | qty | prev_buyerid 
---------+------------+-----+--------------
 b       | 2003-08-02 |  10 | unknown
 c       | 2003-12-24 |  10 | b
 a       | 2003-12-24 |  30 | c
 a       | 2004-01-09 |  40 | a
 c       | 2004-01-18 |  10 | a
 b       | 2004-02-12 |  20 | c
 a       | 2004-02-12 |  10 | b
 c       | 2004-02-16 |  20 | a
 b       | 2004-04-18 |  15 | c
 b       | 2004-04-18 |  20 | b
 c       | 2004-09-07 |  30 | b
(11 rows)

​
Time Series: LAG() to Compare Daily Sales Quantities

In this example, we will use LAG() to compare each day’s sales quantity (qty) with the previous day’s quantity, ordered by dateid:

SELECT dateid, qty,
    LAG(qty) OVER (ORDER BY dateid) AS prev_day_qty,
    qty - LAG(qty) OVER (ORDER BY dateid) AS qty_change
FROM winsales
ORDER BY dateid;

By executing the query above, we will get the following output:

   dateid   | qty | prev_day_qty | qty_change 
------------+-----+--------------+------------
 2003-08-02 |  10 |              |           
 2003-12-24 |  10 |           10 |          0
 2003-12-24 |  30 |           10 |         20
 2004-01-09 |  40 |           30 |         10
 2004-01-18 |  10 |           40 |        -30
 2004-02-12 |  20 |           10 |         10
 2004-02-12 |  10 |           20 |        -10
 2004-02-16 |  20 |           10 |         10
 2004-04-18 |  15 |           20 |         -5
 2004-04-18 |  20 |           15 |          5
 2004-09-07 |  30 |           20 |         10
(11 rows)