previous

Knowledge Checkpoint

Readings that will help you understand this documentation better:

• Window Functions Overview

Definition

A window function that returns the value of the previous row at an offset relative to the current row.

Syntax

previous(expr, offset, order: order_expr, ...)
previous(expr, offset, order: order_expr, ...)
previous(expr, offset, order: order_expr, ..., reset: partition_expr, ...)
previous(expr, offset, order: order_expr, ..., partition: partition_expr, ...)

Examples

previous(count(users.id), order: count(users.id) | desc())
previous(count(users.id), 2, order: users.created_at | month())
previous(count(users.id), 4, order: users.created_at | month(), reset: users.gender)

Input

• expr (required): An expression of the value
• offset (optional): The offset of the previous row relative to the current row. If not specified, the default value is 1.
• order (required, repeatable): A field that is used for ordering. The order is default to ascending. The order can be set explicitly with asc() or desc().
  danger

  If the specified order does not uniquely identify rows, the result of the function can be non-deterministic. For example, if you use order: users.age, and there are multiple users with the same age in the same partition, the result can be unexpected.

• partition or reset (repeatable, optional): A field that is used for partitioning the table. If partitions are not specified:
  • If order is specified, the table will be partitioned by all other grouping columns.
  • If order is not specified, the table will be considered as a single partition.

Output

The value of the previous row in a offset relative to the current row.

Sample Usages

The most common use case for previous is to calculate the difference between the current row and the previous row. For example, to calculate the difference in sales between the current year and the previous year:

Difference in Sales between Current Year and Previous Year

explore {
  dimensions {
    orders.created_at | year()
  }
  measures {
    count(orders.id),
    _diff: count(orders.id) - previous(count(orders.id), order: orders.created_at | year()),
  }
}

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    (DATE_TRUNC ( 'year', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->year",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
)
SELECT
  TO_CHAR((CAST ( "aql__t1"."orders->created_at->year" AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->year",
  "aql__t1"."count_orders->id" AS "count_orders->id",
  ("aql__t1"."count_orders->id" - (LAG("aql__t1"."count_orders->id", 1) OVER ( ORDER BY "aql__t1"."orders->created_at->year" ASC))) AS "_diff"
FROM
  "aql__t1"
WHERE
  "aql__t1"."count_orders->id" IS NOT NULL

caution

previous works at the row level so it will return the previous row regardless if it actually is the previous year or not. You can see in the example above that _diff is equal 997 for 2022 because the previous row is 2020, not 2021.

If you work with time series data, please consider using relative_period instead.