FIRST_VALUE and LAST_VALUE in Oracle
Oracle中的FIRST_VALUE和LAST_VALUE
These are Oracle analytical functions used to return the first value or the last value from a set of ordered rows. These functions can get the first value or the last value within a column in a table. The only argument that can be passed to these functions is the column name. To use these functions we must use the ORDER BY clause. A partition by clause can be used but not required to use these functions. The functions are identical and the only difference is the name.
这些是Oracle分析函数，用于从一组有序行中返回第一个值或最后一个值。 这些函数可以获取表中列中的第一个值或最后一个值。 可以传递给这些函数的唯一参数是列名。 要使用这些功能，我们必须使用ORDER BY子句。 可以使用partition by子句，但不需要使用这些功能。 功能相同，唯一的区别是名称。
Syntax for FIRST_VALUE and LAST_VALUE:
FIRST_VALUE和LAST_VALUE的语法：
FIRST_VALUE (<<COLUMN NAME >>) OVER (ORDER BY <<COLUMN NAME >> )
LAST_VALUE (<<COLUMN NAME >>) OVER (ORDER BY <<COLUMN NAME >> )
The above syntax diagram is taken from the Oracle SQL Reference 19c document.
上面的语法图摘自Oracle SQL Reference 19c文档。
Now let see some example on how we can use them and determine the need based on actual business requirements. Now if we need to generate a report where we need to show the employee information along with another column to show which employee is getting the least paid or high paid.
现在，让我们看一些示例，说明如何使用它们并根据实际业务需求确定需求。 现在，如果需要生成报告，则需要在其中显示员工信息以及另一列以显示哪个员工的薪水最低或最高。
Below is a simple example of how to write a FIRST_VALUE analytical function:
以下是有关如何编写FIRST_VALUE分析函数的简单示例：
From the above query, we see that the least paid employee is "Smith" but if we need to display the report say based on departments with the order then we can add an order by clause at the end, in our case we are trying to retrieve the rows based on salary and least paid person. So in our scenario, we should order by salary as shown below. One thing to notice that the least paid employees column values will be repeated and the same follows on LAST_VALUE too.
从上面的查询中，我们看到薪水最低的员工是“史密斯”，但是如果我们需要根据订单的部门显示报告，那么我们可以在最后添加一个order by子句，在这种情况下，我们试图根据薪水和最低工资人员检索行。 因此，在我们的方案中，我们应按薪水排序，如下所示。 需要注意的一件事是，“最低薪雇员”列的值将重复，而LAST_VALUE也将重复同样的值。
Now, for instance, we need to get the highest paid person then simply we need to change the order but not at the end but at analytical clause:
例如，现在，我们需要获得最高薪水的人，然后只需更改顺序，而不是最后但要更改分析条款：
CODE:
码：
select ename, job,deptno, sal,
first_value(ename) over (order by sal desc) least_paid_employee
from scott.emp
order by sal;
LAST_VALUE just works as same as FIRS T_VALUE, so let us try to use the same SQL statement just by changing the function name from "FIRST_VALUE" to "LAST_VALUE".
LAST_VALUE的功能与FIRS T_VALUE相同，因此让我们尝试通过将函数名称从“ FIRST_VALUE”更改为“ LAST_VALUE”来使用相同SQL语句。
The SQL statement will return the lowest paid employee name against each salary range in the employee table.
SQL语句将返回employee表中每个薪水范围的最低薪雇员姓名。
The range is salary because we have provided the salary as the range, we can also identify by department number too.
范围是薪水，因为我们提供了薪水作为范围，我们也可以通过部门编号来识别。
select ename, job,deptno, sal,
last_value(ename) over (order by sal desc) least_paid_employee
from scott.emp
order by sal
From the above query, we see that for each range of salary the lowest paid employee is repeated for the same sections.
从上面的查询中，我们看到对于每个薪水范围，同一部分重复了最低薪水的雇员。
For example, "WARD" is repeated for 1250 salary
例如，“ WARD”重复为1250工资
The above statement shows for each salary range which employee gets the lowest salary now if we want to check for each department then we can change the statement as like below which will make more information on how/where we can utilize these analytical functions any business use case.
上面的声明显示了每个薪水范围，如果我们要检查每个部门，现在哪个员工的薪水最低，那么我们可以像下面这样更改该声明，这将提供有关如何/在何处使用这些分析功能的更多信息，用于任何业务用途案件。
select ename, job,deptno, sal,
last_value(ename) over (order by deptno desc) least_paid_employee
from scott.emp
order by deptno
Now from the above result, we can see that for department number 20 Smith is the employee who is getting a low salary. But the data is not ordered by the department. If we add the salary on the order by then we see a clear result which person is the lowest paid within the same department.
现在，从以上结果可以看出，对于第20部门，史密斯是薪水较低的员工。 但是数据不是由部门排序的。 如果我们在订单上加上薪水，则可以清楚地看到哪个人是同一部门中薪水最低的人。
select ename, job,deptno, sal,
last_value(ename) over (order by deptno desc) least_paid_employee
from scott.emp
order by deptno,sal
So now we understand how to use the "FIRST_VALUE" & "LAST_VALUE" function in Oracle.
因此，现在我们了解了如何在Oracle中使用“ FIRST_VALUE”和“ LAST_VALUE”函数。
Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics. I'll be looking forward to hearing from you – Swadhin Ray (Sloba)
感谢您阅读我的文章，请随时给我一些反馈或提出任何未来的话题。 我期待着您的回音-Swadhin Ray（Sloba）
For more information about me, please check out my Experts Exchange Profile page.
有关我的更多信息，请查看我的Experts Exchange个人资料页面。