LAG

 The LAG function in Oracle is an analytical function that allows you to access the value of a column in a previous row within the same result set. This function is especially useful when comparing values across rows or performing calculations involving data from different rows.

Here’s a detailed overview of the LAG function:

 

1. What is the LAG function in Oracle?

The LAG function allows you to retrieve the value of a column from a preceding row within the result set. It is often used to compare data from one row with data from a previous row, making it useful for running totals, differences, or comparisons over time.

 

2. Basic Syntax of LAG

The basic syntax of the LAG function is:

LAG(expression [, offset [, default]])

    OVER (PARTITION BY partition_expression ORDER BY order_expression)

• expression: The column or expression whose value you want to access from the previous row.
• offset (optional): The number of rows back from the current row. The default is 1 (previous row).
• default (optional): The value to return if the LAG function cannot retrieve a previous row (e.g., when the current row is the first row in the result set). The default is NULL if not specified.
• PARTITION BY partition_expression (optional): Divides the result set into partitions to apply LAG within each partition. This is useful when you want to perform comparisons within groups of data.
• ORDER BY order_expression: Determines the order of the rows to define which row is considered "previous."

 

3. How Does LAG Work?

The LAG function accesses a column value from a row that precedes the current row in the result set. By specifying an offset, you can control how many rows back you want to look. If there is no preceding row (like in the case of the first row), the function returns the default value (which is NULL unless otherwise specified).

 

4. Example 1: Basic Usage of LAG

Suppose you have the following table Sales with sales data:

Salesperson	SalesAmount	SaleDate
John	500	2025-01-01
Alice	600	2025-01-02
Bob	450	2025-01-03
Carol	700	2025-01-04

To compare the current sales amount with the previous day’s sales, you can use the LAG function:

SELECT Salesperson, SaleDate, SalesAmount,

       LAG(SalesAmount, 1) OVER (ORDER BY SaleDate) AS Previous_SalesAmount

FROM Sales;

Result:

Salesperson	SaleDate	SalesAmount	Previous_SalesAmount
John	2025-01-01	500	NULL
Alice	2025-01-02	600	500
Bob	2025-01-03	450	600
Carol	2025-01-04	700	450

• Previous_SalesAmount shows the sales amount from the previous row (ordered by SaleDate).
• For John, there is no previous row, so the result is NULL.

 

5. Example 2: Using LAG with Default Values

You can specify a default value if no previous row exists (e.g., the first row in the result set).

SELECT Salesperson, SaleDate, SalesAmount,

       LAG(SalesAmount, 1, 0) OVER (ORDER BY SaleDate) AS Previous_SalesAmount

FROM Sales;

Result:

Salesperson	SaleDate	SalesAmount	Previous_SalesAmount
John	2025-01-01	500	0
Alice	2025-01-02	600	500
Bob	2025-01-03	450	600
Carol	2025-01-04	700	450

• In this case, if there is no previous row (for John), the function will return 0 instead of NULL.

 

6. Example 3: Using LAG with a Partition

You can use the PARTITION BY clause to apply the LAG function to each partition of the data. For example, to compare the sales of each salesperson across different sales regions:

SELECT Salesperson, Region, SaleDate, SalesAmount,

       LAG(SalesAmount, 1) OVER (PARTITION BY Region ORDER BY SaleDate) AS Previous_SalesAmount

FROM Sales;

This would calculate the previous sales for each salesperson within the same region, ordered by sale date.

 

7. Handling NULLs in LAG

By default, LAG returns NULL if there is no preceding row. You can also manage NULL values by providing a default value (such as 0 or an empty string) or using other functions like COALESCE to replace NULL.

Example with COALESCE:

SELECT Salesperson, SaleDate, SalesAmount,

       COALESCE(LAG(SalesAmount, 1), 0) AS Previous_SalesAmount

FROM Sales;

 

8. Use Cases for LAG

• Comparing consecutive rows: Use LAG to compare data points from one row to another. This is useful for detecting changes in values over time.
• Calculating differences: You can use LAG to calculate the difference between the current row and the previous row, such as calculating the difference in sales or prices over time.
• Running totals or cumulative values: Although LAG itself doesn't calculate cumulative totals, it can be used in conjunction with other functions to perform running total calculations.

Example of calculating the difference in sales:

SELECT Salesperson, SaleDate, SalesAmount,

       SalesAmount - LAG(SalesAmount, 1, 0) OVER (ORDER BY SaleDate) AS SalesDifference

FROM Sales;

Result:

Salesperson	SaleDate	SalesAmount	SalesDifference
John	2025-01-01	500	500
Alice	2025-01-02	600	100
Bob	2025-01-03	450	-150
Carol	2025-01-04	700	250

 

9. Performance Considerations

• Sorting: The LAG function requires the rows to be ordered, which can affect performance on large datasets. Ensure that the column used in the ORDER BY clause is indexed if possible.
• Windowing: If you're using LAG with a large number of partitions or complex ORDER BY clauses, the function may require more processing time. Consider optimizing queries by using partitioning wisely.

 

10. LAG vs. LEAD

• LAG provides access to the previous row's data.
• LEAD provides access to the next row's data.

Both functions work similarly, but the key difference is in the direction of the comparison (previous vs. next row).

 

Conclusion

The LAG function is an essential tool for comparing rows in Oracle SQL, especially when working with sequential data. It allows you to retrieve values from previous rows, which is useful for tasks like calculating differences, tracking changes over time, or comparing consecutive data points. With its flexibility in handling offsets and default values, LAG can be used in a wide range of analytical queries.