In SQL, the LAG and LEAD window functions are used to access data from previous and next rows in a result set, respectively. They are particularly useful for calculating differences between rows or for comparing the current row with previous or next ones without the need for self-joins.
Syntax
- LAG:
LAG(column_name, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column)
- LEAD:
LEAD(column_name, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column)
Parameters:
- column_name: The column from which to retrieve the value.
- offset: The number of rows to look back (for LAG) or forward (for LEAD). The default is 1 if omitted.
- default: A default value to return when there is no row at the specified offset. If omitted, it defaults to NULL.
- PARTITION BY: (Optional) Divides the result set into partitions to which the function is applied.
- ORDER BY: Required; defines the order of rows within each partition.
Examples
Using LAG to Access Previous Row Value
Suppose you have a table named sales with columns sale_date and amount.
SELECT
sale_date,
amount,
LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_amount
FROM
sales;
In this query:
- We retrieve the sale_date and amount of each sale.
- The LAG function gets the amount from the previous row (based on the sale_date order).
- If there is no previous row (e.g., for the first row), it returns 0 as the default value.
Using LEAD to Access Next Row Value
You can also use LEAD to retrieve the next row's value.
SELECT
sale_date,
amount,
LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount
FROM
sales;
In this query:
- The LEAD function retrieves the amount from the next row ordered by sale_date.
- If there is no next row (e.g., for the last row), it returns 0 as the default value.
Combining LAG and LEAD
You can use both functions in the same query to compare values from the previous and next rows.
SELECT
sale_date,
amount,
LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount,
LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM
sales;
Use Cases
1. Calculating Differences: You can compute how much sales increased or decreased compared to the previous sale.
2. Detecting Trends: Compare current values to their next or previous values to identify trends.
3. Moving Averages: Calculate moving averages by combining values from multiple rows.
By utilizing LAG and LEAD, you can streamline your SQL queries and perform advanced analytics directly in your database queries!