The LEAD function in SQL is used to access data from a subsequent row in the same result set without the need for a self-join. This function is particularly useful for comparing values in adjacent rows.
Basic Syntax
LEAD(column_name, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: The name of the column from which you want to retrieve data.
- offset: The number of rows forward from the current row from which to retrieve data. The default is 1 (the next row).
- default: A value to return if the LEAD call goes beyond the limits of the result set. This is optional.
- PARTITION BY: This clause divides the result set into partitions to which the LEAD function is applied.
- ORDER BY: This clause determines the order of rows within each partition.
Example
Assume you have a table called sales with the following columns: id, sale_date, and amount. You want to compare each sale with the next one.
SQL Query
SELECT
id,
sale_date,
amount,
LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM
sales;
Explanation
- In this query, for each row in the sales table:
- The current amount is retrieved.
- The LEAD(amount) function retrieves the amount of the next sale based on the order of sale_date.
- The result set will contain the current amount and the next_amount for comparison.
Example Output
Assuming the sales table contains:
| id | sale_date | amount |
|----|-----------|--------|
| 1 | 2023-01-01| 100.00 |
| 2 | 2023-01-02| 150.00 |
| 3 | 2023-01-03| 200.00 |
The output of the query would be:
| id | sale_date | amount | next_amount |
|----|-----------|--------|-------------|
| 1 | 2023-01-01| 100.00 | 150.00 |
| 2 | 2023-01-02| 150.00 | 200.00 |
| 3 | 2023-01-03| 200.00 | NULL |
In the last row, next_amount is NULL because there is no subsequent row.
Usage Considerations
- Ensure that you order the rows logically when using LEAD to get meaningful results.
- If you need to compare values in groups (like by category), use PARTITION BY to define those groups. For instance:
LEAD(amount) OVER (PARTITION BY category_id ORDER BY sale_date)
This function offers a powerful way to analyze sequential data without the complexity of self-joins.