MySQL LAG Function

MySQL LAG Function

The LAG() function in MySQL is a window function introduced in MySQL 8.0. It allows you to access data from a previous row in the same result set without requiring a self-join. This is useful for comparing values in consecutive rows or analyzing trends over time.

Syntax

LAG(expression, offset, default) OVER (
    [PARTITION BY column_name]
    [ORDER BY column_name]
)

• expression: The column or calculation whose value is returned from a previous row.
• offset: The number of rows behind the current row to look for the value (default is 1).
• default: The value returned if no previous row exists (default is NULL).
• PARTITION BY: Divides the result set into partitions; the function is applied to each partition independently.
• ORDER BY: Specifies the order of rows in which the function is applied.

Key Points

1. Default Values:

   • If no previous row exists, the function returns NULL unless a default value is specified.

2. Offset:

   • The offset determines how far back the function looks for the value.
   • By default, offset is 1.

3. Partitioning:

   • The result set can be partitioned into groups using the PARTITION BY clause, and the function will operate within each group.

4. Ordering:

   • The ORDER BY clause ensures rows are processed in a specific order.

Example 1: Simple Use of LAG()

Suppose you have a table sales:

sale_id	region	amount
1	North	100
2	North	200
3	North	150
4	South	300
5	South	250

To fetch the current amount and the previous amount:

SELECT sale_id, region, amount,
       LAG(amount) OVER (PARTITION BY region ORDER BY amount) AS previous_amount
FROM sales;

Output:

sale_id	region	amount	previous_amount
1	North	100	NULL
3	North	150	100
2	North	200	150
5	South	250	NULL
4	South	300	250

Example 2: Using LAG() with an Offset

To fetch the amount two rows behind:

SELECT sale_id, region, amount,
       LAG(amount, 2) OVER (PARTITION BY region ORDER BY amount) AS two_rows_behind
FROM sales;

Output:

sale_id	region	amount	two_rows_behind
1	North	100	NULL
3	North	150	NULL
2	North	200	100
5	South	250	NULL
4	South	300	NULL

Example 3: Using LAG() with a Default Value

To provide a default value when no previous row exists:

SELECT sale_id, region, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY region ORDER BY amount) AS previous_amount_with_default
FROM sales;

Output:

sale_id	region	amount	previous_amount_with_default
1	North	100	0
3	North	150	100
2	North	200	150
5	South	250	0
4	South	300	250

Example 4: Calculate Change Between Rows

Using the LAG() function to calculate the difference between the current amount and the previous amount:

SELECT sale_id, region, amount,
       LAG(amount) OVER (PARTITION BY region ORDER BY amount) AS previous_amount,
       amount - LAG(amount) OVER (PARTITION BY region ORDER BY amount) AS amount_difference
FROM sales;

Output:

sale_id	region	amount	previous_amount	amount_difference
1	North	100	NULL	NULL
3	North	150	100	50
2	North	200	150	50
5	South	250	NULL	NULL
4	South	300	250	50

Use Cases

1. Comparative Analysis:

   • Compare current values to past values within a dataset.

2. Trend Analysis:

   • Analyze changes in metrics like sales or prices over time.

3. Gaps in Data:

   • Identify and analyze gaps between consecutive rows.

4. Historical Context:

   • Access past data to provide context for the current row.

Conclusion

The LAG() function is a powerful tool for accessing previous rows in a result set. It simplifies tasks like comparisons, trend analysis, and identifying gaps without requiring complex subqueries or joins. Its flexibility with partitioning and ordering makes it essential for advanced analytics in MySQL.