Oracle 高级查询之分析函数部分--first_value,last_value

在Oracle数据库中，高级查询技巧往往涉及到复杂的SQL语句，其中分析函数是实现这些复杂查询的关键工具之一。本文将深入探讨分析函数中的`FIRST_VALUE()`和`LAST_VALUE()`函数，这两个函数在处理分组数据时尤其有用，能够帮助我们获取每个分组中的第一个或最后一个值，基于特定的排序规则。 ### Oracle分析函数：`FIRST_VALUE()`和`LAST_VALUE()` #### `FIRST_VALUE()`函数 `FIRST_VALUE()`函数返回在一个窗口函数中定义的顺序下，指定列的首个值。这通常用于返回一个分组内按照某种顺序（如按时间、数值等）的第一个元素的值。例如，在示例数据表`test`中，如果我们想找出每个`ID`下的最低薪资员工的名字，可以使用`FIRST_VALUE()`函数结合`ORDER BY salary ASC`来实现： ```sql SELECT ID, NAME, SALARY, FIRST_VALUE(NAME) OVER (PARTITION BY ID ORDER BY SALARY) AS lowest_sal_name FROM test ORDER BY ID, NAME; ``` #### `LAST_VALUE()`函数 与`FIRST_VALUE()`相对应，`LAST_VALUE()`函数返回的是在窗口函数定义的顺序下，指定列的最后一个值。它对于查找每个分组中按某种顺序排列的最后一个元素非常有用。在`test`表中，如果我们想要找出每个`ID`对应的最高薪资员工的名字，可以通过`LAST_VALUE()`函数结合`ORDER BY SALARY DESC`实现： ```sql SELECT ID, NAME, SALARY, LAST_VALUE(NAME) OVER (PARTITION BY ID ORDER BY SALARY DESC) AS highest_sal_name FROM test ORDER BY ID, NAME; ``` ### 分析函数的窗口限定子句 在使用`FIRST_VALUE()`和`LAST_VALUE()`时，经常需要配合窗口限定子句（如`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`）来指定函数的范围。这个子句定义了在计算`FIRST_VALUE()`或`LAST_VALUE()`时考虑的行范围。例如： - `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`表示考虑当前分组的所有行。 - `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`表示从当前行到分组的末尾所有行。 这些限定子句对于确定函数的搜索范围至关重要，不同的范围选择会直接影响结果的准确性。 ### 实例解析 让我们通过具体的示例来加深理解。在创建并填充`test`表后，我们可以使用以下SQL语句来找出每个`ID`下的最高薪资员工的名字： ```sql SELECT ID, NAME, SALARY, LAST_VALUE(NAME) OVER (PARTITION BY ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_sal_name FROM test ORDER BY ID, NAME; ``` 在这个查询中，`ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`确保了在每个`ID`分组内，根据薪资降序排列所有行，并返回该分组内的最高薪资员工的名字。 然而，如果我们将窗口限定子句改为`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`，则结果将不同，因为此时`LAST_VALUE()`仅考虑从当前行到分组末尾的行，可能会导致每个`ID`下的最高薪资员工名字发生变化。 `FIRST_VALUE()`和`LAST_VALUE()`函数是Oracle分析函数中非常强大的工具，能够帮助我们在复杂的数据集中提取有价值的信息。正确理解和应用这些函数及其窗口限定子句，可以极大地提高我们的数据分析能力和效率。