Usage of lag (function and lead (function) in oracle

The LAG() and LEAD() functions in Oracle can obtain the value of the row before (LAG()) or after (LEAD()) the specified row offset from the current row. They are used to analyze time series data and calculate moving averages. The LAG() function returns the value of the previous row, and the LEAD() function returns the value of the subsequent row. The offset can be positive or negative, and returns a default value if it is outside the table range.

LAG() and LEAD() functions in Oracle

Introduction
The LAG() and LEAD() functions are used to obtain the value of the row before or after the specified row offset from the current row.

Syntax

<code>LAG(expression, offset, default)
LEAD(expression, offset, default)</code>

Parameters

• expression: Expression of the value to be returned Mode.
• offset: The number of rows to offset, which can be a positive or negative number.
• default: The default value returned if the offset is outside the scope of the table.

Usage

LAG() Function
LAG() function returns the value before the specified number of rows. For example:

<code class="sql">SELECT LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;</code>

This will return the employee's salary for the month prior to their joining date.

LEAD() function
LEAD() function returns the value after the specified number of rows. For example:

<code class="sql">SELECT LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;</code>

This will return the employee's salary one month after their joining date.

Notes

• If the offset exceeds the range of the table, the default value is returned.
• offset can be a negative number, indicating the previous value.
• The LAG() and LEAD() functions are useful when analyzing time series data and calculating moving averages.

The above is the detailed content of Usage of lag (function and lead (function) in oracle. For more information, please follow other related articles on the PHP Chinese website!