PostgreSQL中FIRST_VALUE、LAST_VALUE、LAG 和 LEAD是窗口函数,允许返回在数据集的特定窗口（或分区）内访问行的相对位置

在PostgreSQL中，FIRST_VALUE、LAST_VALUE、LAG 和 LEAD 是窗口函数（window functions），它们允许你在数据集的特定窗口（或分区）内访问行的相对位置。以下是对这些函数的详细解释和用法：

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_in_dept
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_in_dept
FROM 
    employees;

这将为每个部门返回按工资排序后的最后（最高，如果升序）个员工的工资。

3. LAG

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

语法：

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

• offset：要回溯的行数（默认为1）。
• default_value：如果回溯的行数超出窗口范围，则返回此默认值（可选）。

示例：

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

这将为每行返回前一个员工的工资（按工资排序）。如果这是第一行，则 previous_salary 将为NULL，除非你指定了一个默认值。

4. LEAD

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

语法：

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

• offset：要前瞻的行数（默认为1）。
• default_value：如果前瞻的行数超出窗口范围，则返回此默认值（可选）。

示例：

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

这将为每行返回下一个员工的工资（按工资排序）。如果这是最后一行，则 next_salary 将为NULL，除非你指定了一个默认值。

注意事项

• 这些函数通常与 OVER 子句一起使用，其中可以指定分区（PARTITION BY）和排序（ORDER BY）。
• 如果不指定 frame_clause（即 ROWS 或 RANGE），则默认使用整个分区作为窗口。
• LAG 和 LEAD 函数中的 offset 默认为1，表示前一行或后一行。
• 如果 LAG 或 LEAD 函数中的回溯或前瞻行数超出窗口范围，并且没有指定 default_value，则结果将为NULL。

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