18. LAG and LEAD#

18.1. Synopsis#

LAG(
  <expression>
  [, <offset> [, <default_value>]
) OVER()

LEAD(
  <expression>
  [, <offset> [, <default_value>]
) OVER()

-- Optional
OVER (PARTITION BY <partition_expression>, ...)

18.2. Description#

LAG and LEAD are window functions that return values from rows relative to the current row.

LAG returns a value from a previous row
LEAD returns a value from a following row

These functions are typically used for time-series and analytical queries, such as computing deltas, changes over time, or comparing adjacent values.

In qdbsh, the OVER() clause is required when using LAG or LEAD. The supported OVER() syntax is intentionally limited and differs from standard SQL window functions.

18.3. Parameters#

expression: Column or expression whose value is returned from another row.
offset: Number of rows backward (for LAG) or forward (for LEAD). Defaults to 1.
default_value: Value returned when the requested row does not exist. Defaults to NULL.
partition_expression: One or more expressions that divide the result set into independent partitions. Window functions are evaluated separately within each partition.

18.4. Notes#

OVER() is required

LAG and LEAD must be used with an OVER() clause. Omitting OVER() results in a syntax error.

Ordering behavior

ORDER BY is not supported inside OVER(). Row ordering is defined by the outer query’s ORDER BY clause.

Partitioning behavior

When PARTITION BY is used, LAG and LEAD operate only within each partition. If omitted, the entire result set is treated as a single partition.

18.5. Examples#

18.5.1. Previous and next values#

Assume a time-series table:

table: bac_stock
column: Adj_Close (DOUBLE)

Retrieve the previous value using LAG:

SELECT
  $timestamp,
  Adj_Close,
  LAG(Adj_Close, 1) OVER() AS previous_price
FROM bac_stock
ORDER BY $timestamp;

Retrieve the next value using LEAD:

SELECT
  $timestamp,
  Adj_Close,
  LEAD(Adj_Close, 1) OVER() AS next_price
FROM bac_stock
ORDER BY $timestamp;

18.5.2. Day-over-day change#

Compute a price delta using LAG:

SELECT
  $timestamp,
  Adj_Close,
  LAG(Adj_Close, 1) OVER() AS previous_price,
  Adj_Close - LAG(Adj_Close, 1) OVER() AS price_change
FROM bac_stock
ORDER BY $timestamp;

18.5.3. Using default values#

Provide a fallback value when no previous row exists:

SELECT
  $timestamp,
  Adj_Close,
  LAG(Adj_Close, 1, 0) OVER() AS previous_price
FROM bac_stock
ORDER BY $timestamp;

18.5.4. Partitioned window#

Compute lagged values independently per symbol:

SELECT
  symbol,
  $timestamp,
  price,
  LAG(price, 1) OVER (PARTITION BY symbol) AS previous_price
FROM stock_prices
ORDER BY symbol, $timestamp;

In this query:

Rows are grouped by symbol
Each symbol’s series is processed independently
Row order is defined by the outer ORDER BY clause

18.6. Usage Guidelines#

Use categorical identifiers (such as symbol, metric, device_id) with PARTITION BY.
Avoid partitioning by the same numeric value being lagged or led, as this often results in single-row partitions.

18.7. Example Usage#

This section provides a complete, runnable example demonstrating how to use LAG and LEAD in qdbsh, from table creation to analytical queries.

18.7.1. Setup#

Create a simple time-series table and insert sample data:

CREATE TABLE bac_stock (
  Adj_Close DOUBLE
);

INSERT INTO bac_stock ($timestamp, Adj_Close) VALUES
  (2022-01-01, 10),
  (2022-01-02, 20),
  (2022-01-03, 30),
  (2022-01-04, 25),
  (2022-01-05, 28);

18.7.2. Simple LEAD example#

Retrieve the next row’s value using LEAD:

SELECT
  $timestamp,
  Adj_Close AS price,
  LEAD(Adj_Close, 1) OVER() AS next_price
FROM bac_stock
ORDER BY $timestamp;

This query returns the price of the following row for each timestamp. For the last row, next_price is NULL because no subsequent row exists.

18.7.3. Price change and percent change#

Compute both the absolute and percentage change using LAG:

SELECT
  $timestamp,
  Adj_Close AS price,
  LAG(Adj_Close, 1) OVER() AS prev_price,
  (Adj_Close - LAG(Adj_Close, 1) OVER()) AS change,
  ((Adj_Close / LAG(Adj_Close, 1) OVER()) - 1) AS change_pct
FROM bac_stock
ORDER BY $timestamp;

In this query:

prev_price contains the previous row’s value
change computes the absolute difference from the previous row
change_pct computes the relative change from the previous row

The first row has no previous value, so prev_price, change, and change_pct are NULL for that row.

18.8. Partitioning Example#

This example demonstrates how to use PARTITION BY to compute previous/next values independently for each series.

18.8.1. Setup#

Create a multi-series time-series table (partition key: symbol):

CREATE TABLE stock_prices (
  symbol STRING,
  price DOUBLE
);

INSERT INTO stock_prices ($timestamp, symbol, price) VALUES
  (2022-01-01, 'AAA', 10),
  (2022-01-02, 'AAA', 12),
  (2022-01-03, 'AAA', 11),
  (2022-01-01, 'BBB', 20),
  (2022-01-02, 'BBB', 25),
  (2022-01-03, 'BBB', 30);

18.8.2. Simple LEAD with PARTITION BY#

Retrieve the next value within each symbol series:

SELECT
  symbol,
  $timestamp,
  price,
  LEAD(price, 1) OVER (PARTITION BY symbol) AS next_price
FROM stock_prices
ORDER BY symbol, $timestamp;

18.8.3. Price change and percent change per partition#

Compute deltas independently per symbol:

SELECT
  symbol,
  $timestamp,
  price,
  LAG(price, 1) OVER (PARTITION BY symbol) AS prev_price,
  (price - LAG(price, 1) OVER (PARTITION BY symbol)) AS change,
  ((price / LAG(price, 1) OVER (PARTITION BY symbol)) - 1) AS change_pct
FROM stock_prices
ORDER BY symbol, $timestamp;

In this example:

PARTITION BY symbol ensures that AAA never uses values from BBB (and vice versa).
Row order is defined by the outer ORDER BY symbol, $timestamp clause.
The first row in each partition has no previous row, so lag-based fields are NULL for that row.

18.9. Summary#

LAG and LEAD are supported window functions in qdbsh
OVER() is required
PARTITION BY is supported
ORDER BY inside OVER() is not supported
Row ordering is defined by the outer query