SQL FIRST_VALUE

SQL Server的FIRST_VALUE是分析函数之一。此FIRST_VALUE函数将返回每个分区或整个表中的第一个值，其基本语法如下所示

SELECT FIRST VALUE([Scalar Expression]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

对于此FIRST_VALUE演示，我们将使用下面显示的数据。

SQL FIRST_VALUE（无Partition By子句）

如果我们在此函数中避免使用Partition By子句会怎样？由于我们没有指定任何分区，因此它将整个表视为一个分区。接下来，它将从整个员工表中打印第一个值。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
      ,FIRST_VALUE([Sales]) OVER (ORDER BY [HireDate] ASC) AS LeastSale  
      ,FIRST_VALUE([Occupation]) OVER (ORDER BY [HireDate] ASC) AS Profession   
  FROM [Employee]
SQL FIRST_VALUE without Partition By Clause 1

上面的FIRST_VALUE代码是常见的select语句，我们根据Hire Date列编写Sales和Profession的第一个值。

下面的SQL Server语句按升序对Employee表按HireDate排序。让我向您展示。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY [HireDate] ASC

上图显示，Employee表具有Occupation = Professional，Sales = 3578.27作为第一列值。现在，以下查询中的SQL FIRST_VALUE函数将这些值作为输出返回。

,FIRST_VALUE([Sales]) 
                  OVER (ORDER BY [HireDate] ASC) AS LeastSale  
,FIRST_VALUE([Occupation]) 
                  OVER (ORDER BY [HireDate] ASC) AS Profession

为了进一步说明，我将ORDER BY CLAUSE更改为降序。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
      ,FIRST_VALUE([Sales]) OVER (ORDER BY [HireDate] ASC) AS LeastSale  
      ,FIRST_VALUE([Occupation]) OVER (ORDER BY [HireDate] ASC) AS Profession   
  FROM [Employee]
FIRST_VALUE with OVER and Order By Clause 4

FIRST_VALUE（带Partition By示例）

FIRST_VALUE允许您选择每个分区中的第一个值。以下查询按Occupation对数据进行分区，并按其Sales降序排序。然后选择每个分区中的第一个[First Name]值或第一个值。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,FIRST_VALUE([FirstName]) OVER (
                                       PARTITION BY [Occupation] 
    				       ORDER BY [Sales] DESC
				      ) AS BestCustomer   
      ,[HireDate]
  FROM [Employee]
FIRST_VALUE With Partition By and Order By Clause 5