Oracle开窗函数first_value与last_value解析总结

Oracle 开窗函数：first_value() 和 last_value() 深入解析

Oracle 的开窗函数（Window Functions）是 SQL 中强大的分析工具，其中 first_value() 和 last_value() 是两个常用的函数，用于获取窗口框架内的第一个和最后一个值。

基本语法

FIRST_VALUE(expr) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [frame_clause]
)

LAST_VALUE(expr) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [frame_clause]
)

first_value() 函数

first_value() 返回窗口框架中的第一个值。

示例1：基本用法

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

这个查询会返回每个部门中薪资最高的员工的薪资值，并将该值显示在该部门所有员工的记录中。

示例2：与窗口框架结合

SELECT 
    employee_id,
    hire_date,
    salary,
    FIRST_VALUE(salary) OVER (
        ORDER BY hire_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS first_in_window
FROM employees;

这个查询会显示每个员工记录以及按入职日期排序后，当前行前后各一行窗口中的第一个薪资值。

last_value() 函数

last_value() 返回窗口框架中的最后一个值。需要注意的是，默认窗口框架可能会导致意外的结果。

示例3：基本用法（可能有问题）

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

这个查询可能不会返回预期的结果，因为默认的窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

示例4：正确的last_value()用法

SELECT 
    employee_id,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary
FROM employees;

通过指定完整的窗口框架，我们可以正确获取每个部门中薪资最低的员工的薪资值。

窗口框架详解

窗口框架决定了函数计算时考虑的行范围。常见的框架子句包括：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（默认）
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

实际应用场景

1. 计算与最高/最低值的差异：

   SELECT 
       product_id,
       sale_date,
       amount,
       FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY amount DESC) AS max_amount,
       amount - FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY amount DESC) AS diff_from_max
   FROM sales;
   

2. 获取时间序列的第一个和最后一个值：

   SELECT 
       time_id,
       temperature,
       FIRST_VALUE(temperature) OVER (ORDER BY time_id) AS first_temp,
       LAST_VALUE(temperature) OVER (ORDER BY time_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_temp
   FROM temperature_readings;
   

3. 计算移动窗口的第一个和最后一个值：

   SELECT 
       date_id,
       stock_price,
       FIRST_VALUE(stock_price) OVER (ORDER BY date_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS first_in_5day_window,
       LAST_VALUE(stock_price) OVER (ORDER BY date_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS last_in_5day_window
   FROM stock_prices;
   

性能考虑

1. 开窗函数通常比自连接或子查询更高效
2. 合理的分区和排序可以显著提高性能
3. 避免过度使用无界窗口框架（UNBOUNDED FOLLOWING）

常见误区

1. 忽略窗口框架：特别是对 last_value() 使用时，不指定正确的窗口框架会导致意外结果
2. 混淆分区和排序：错误的排序可能导致获取的不是真正需要的第一个或最后一个值
3. 过度使用：有时简单的聚合函数可能更适合需求

通过合理使用 first_value() 和 last_value() 开窗函数，可以简化许多复杂的分析查询，提高SQL代码的可读性和性能。