Oracle数据库中FIRST_VALUE、LAST_VALUE、LAG和LEAD是用于在窗口函数中进行数据行之间相对位置访问的函数

在Oracle数据库中，FIRST_VALUE、LAST_VALUE、LAG 和 LEAD 是用于在窗口函数中进行数据行之间相对位置访问的函数。下面分别介绍这些函数的作用和用法。

1. FIRST_VALUE

FIRST_VALUE 函数返回在指定窗口帧内的第一行的值。常用于在分组或排序的上下文中获取某一组的起始值。

语法：

FIRST_VALUE(column_name) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ROWS or RANGE frame_clause])

示例：

SELECT 
    employee_id, 
    department_id, 
    salary, 
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_salary
FROM 
    employees;

这将为每个部门返回工资最低的员工的工资（假设工资升序排列）。

2. LAST_VALUE

LAST_VALUE 函数返回在指定窗口帧内的最后一行的值。常用于在分组或排序的上下文中获取某一组的结束值。

语法：

LAST_VALUE(column_name) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ROWS or RANGE frame_clause])

示例：

SELECT 
    employee_id, 
    department_id, 
    salary, 
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS last_salary
FROM 
    employees;

这将为每个部门返回工资最高的员工的工资（假设工资升序排列）。

3. LAG

LAG 函数返回窗口中当前行的前N行的值。常用于访问当前行的前几行的数据。

语法：

LAG(column_name, offset, default_value) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

• offset：表示要回溯的行数。
• default_value：如果回溯的行数超出窗口范围，则返回此默认值。

示例：

SELECT 
    employee_id, 
    salary, 
    LAG(salary, 1, 0) OVER (ORDER BY salary) AS previous_salary
FROM 
    employees;

这将为每行返回前一行员工的工资，如果这是第一行，则返回0（假设工资升序排列）。

4. LEAD

LEAD 函数返回窗口中当前行的后N行的值。常用于访问当前行的后几行的数据。

语法：

LEAD(column_name, offset, default_value) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

• offset：表示要前瞻的行数。
• default_value：如果前瞻的行数超出窗口范围，则返回此默认值。

示例：

SELECT 
    employee_id, 
    salary, 
    LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
FROM 
    employees;

这将为每行返回后一行员工的工资，如果这是最后一行，则返回0（假设工资升序排列）。

注意事项

• 这些函数通常与 OVER 子句一起使用，其中可以指定分区和排序。
• 窗口函数的结果依赖于查询中的排序和分区，因此在使用时需要仔细考虑这些因素。
• 如果不指定 frame_clause，则默认使用整个分区。

通过这些函数，可以灵活地访问和操作数据行之间的相对位置，从而进行更复杂的数据分析和处理。