SoFunction
Updated on 2025-03-04

Summary of the usage of window function lag in mysql

In MySQL, window functionsLAG()Can be used to access data from the previous row or multiple rows of the current row. This function is usually used to analyze time series data, such as calculating the differences between adjacent rows or obtaining the previous state.

The following isLAG()Basic syntax of functions:

LAG(expression [, offset] [, default_value]) OVER (partition_by_clause order_by_clause)
  • expression: The column or expression to retrieve.
  • offset: Specifies the number of rows that are offset forward from the current row. The default value is 1, which is the previous line of the current line.
  • default_value: The default value returned when there are not enough rows after the offset is specified. If not specified, the default isNULL。
  • partition_by_clause: clause used for partitioning, similar toGROUP BY。
  • order_by_clause: A clause used to specify the order of rows.

Here are some usagesLAG()Example of function:

Example 1: Get the data from the previous row

Suppose we have a name calledsalesThe table contains the ID and date of each sale:

SELECT
  sale_id,
  sale_date,
  LAG(sale_date, 1) OVER (ORDER BY sale_date) AS previous_sale_date
FROM
  sales;

This query will return the date of the current row and the date of the previous row for each row.

Example 2: LAG with offset

If you want to get the data of the second row before the current row, you can set the offset to 2:

SELECT
  sale_id,
  sale_date,
  LAG(sale_date, 2) OVER (ORDER BY sale_date) AS second_previous_sale_date
FROM
  sales;

Example 3: Use default values

If you want to return a specific default value when there are not enough rows, you can usedefault_valueparameter:

SELECT
  sale_id,
  sale_date,
  LAG(sale_date, 1, '1900-01-01') OVER (ORDER BY sale_date) AS previous_sale_date
FROM
  sales;

This query will return the date of the previous row, if it does not exist,'1900-01-01'。

Example 4: Use with partitions

Suppose you have sales data containing multiple departments and you want to get the data from the previous row within each department:

SELECT
  department_id,
  sale_id,
  sale_date,
  LAG(sale_date, 1) OVER (PARTITION BY department_id ORDER BY sale_date) AS previous_sale_date
FROM
  sales;

This query will return the date of the current row and the date of the previous row for each row within each department.

Please note that window functions are supported in MySQL 8.0 and above. If you are using an earlier version of MySQL, these functions will not be available.

This is the article about the usage of window function lag() in mysql. For more related contents of mysql window function lag(), please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!