PostgreSQL – LEAD Function

ByAdmin

The LEAD() function is used to access the value of a column in a subsequent row within the same result set. This function is often used to compare values between the current row and the next row, or to retrieve values from “leading” rows relative to the current row based on a specified order. It’s useful for scenarios where you need to perform calculations or comparisons with values from the next row.

The syntax of the LEAD() function is as follows:

LEAD(expression, offset, default_value) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
  • expression: The column or expression whose value you want to access from the subsequent row.
  • offset: The number of rows ahead of the current row from which you want to access the value. The default is 1 (i.e., the next row).
  • default_value: An optional value to be returned when the LEAD() function is invoked on the last row of a partition. If omitted, the default is NULL.
  • PARTITION BY: An optional clause that divides the result set into partitions or groups. The function operates separately within each partition. If omitted, the function operates on the entire result set.
  • ORDER BY: Specifies the column(s) by which you want to order the result set for accessing subsequent row values.

Here’s a simple example of how to use the LEAD() function in PostgreSQL:

Suppose you have a table named “sales” with columns “sale_date” and “revenue”, and you want to compare the revenue of the current sale with the revenue of the subsequent sale:

SELECT sale_date, revenue, LEAD(revenue, 1) OVER (ORDER BY sale_date) AS next_sale_revenue FROM sales;

In this example, the LEAD() function retrieves the revenue of the next sale (the sale that occurs immediately after the current sale) for each row based on the order of sale dates. The result might look like this:

sale_date | revenue | next_sale_revenue

------------+---------+-------------------

2023-01-05 | 100 | 150

2023-01-06 | 150 | 200

2023-01-07 | 200 | 180

2023-01-08 | 180 | Null

In this output, the “next_sale_revenue” column contains the revenue of the sale that follows each current sale.

The LEAD() function is particularly useful when you need to compare or perform calculations with values from the subsequent row, especially when analyzing trends, changes, or patterns within ordered data.

Similar Posts